You can also use the statement to create implicit delete, insert, or update queries, or to write embedded SQL from a limited list of available commands.
onException (sqlEx SQLException) if (myCustomer is unique) myErrorHandler(8); end end
WHERE CURRENT OF resultSetID
Do not leave space between #sql and the left brace.
You can generate implicit SQL through the execute statement to perform certain database operations without first performing an EGL open or get. EGL usually hides the details of SQL database access, such as the manipulation of result set row position. Through the execute statement, you can directly specify the rows of the database you want to change by setting the defaultSelectCondition property of the SQL Record part.
execute delete myCustomer;
Because of the lack of safeguards, exercise caution when you use these implicit SQL commands.
The implicit SQL DELETE statement uses the defaultSelectCondition SQL record property to determine the table rows to be deleted when the value in each SQL table key column is equal to the value in the corresponding key field of the SQL record variable. If you do not specify a record key or default selection condition, all table rows are deleted.
DELETE FROM tableName WHERE [ defaultSelectCondition AND ] keyColumn01 = :keyField01 [ AND ... keyColumnnn = :keyFieldnn ]
You cannot use a single EGL statement to delete rows from more than one database table.
INSERT INTO tableName (column01, ... columnNN) values (:recordField01, ... :recordFieldNN)
UPDATE tableName SET column01 = :recordField01, column02 = :recordField01, ... columnNN = :recordFieldNN WHERE keyColumn01 = :keyField01 [ AND ... keyColumnnn = :keyFieldnn ]
execute #sql{ DELETE FROM Customer WHERE last_order_date < :targetDate };
The EGL variable targetDate becomes a host variable (preceded by a colon) in the SQL code; for more information, see Host variables.
You can also use similar syntax to embed SQL INSERT or UPDATE statements.
You cannot use an EGL execute statement to embed a SQL statement that returns a result set. This is because you do not have direct control of the cursor; EGL handles all cursor definition and management internally.
execute #sql{ create table Customer ( customer_number int not null, customer_name char(40) not null, customer_balance decimal(9,2) not null) }; execute update for myCustomer; execute #sql{ call aStoredProcedure( :parameterVar) }; execute myPreparedStatement;