EGL does not give you direct access to this cursor, but instead provides positional options with the get statement (see get considerations for SQL). After the open statement runs, the cursor points to the first row (record) in the result set.
Cursors for which you specify the hold option are not closed on a commit; however, a rollback or database connect closes all cursors. If you do not need to retain cursor position across a commit, do not specify hold.
For more information, see "Compatibility" in this topic.
If you do not specify a usingKeys clause, the key-value component of the implicit statement is based on the SQL Record part that is referenced in the open statement.
If you specify the forUpdate keyword, the columns associated with the key items are excluded from the columns listed on the SQL FOR UPDATE OF clause. If you do not specify the forUpdate keyword, only the columns associated with the key items are included in the list of columns on the SQL ORDER BY clause.
Any implicit code that EGL might otherwise generate is ignored if you specify an embedded SQL statement.
SELECT column01, column02, ... columnNN INTO :recordField01, :recordField02, ... :recordFieldNN FROM tableName WHERE keyColumn01 = :keyField01 FOR UPDATE OF column01, column02, ... columnNN
You can override the default by specifying an embedded SQL statement in the EGL open statement (using the #sql directive).
The following examples assume an SQL record variable named myCustomer:
open myCustomerResults forUpdate for myCustomer; open x1 with #sql{ SELECT customer_number, customer_name, customer_balance FROM Customer WHERE customer_number >= :myCustomer.customerNumber FOR UPDATE OF :myCustomer.customerNumber, :myCustomer.customerName, :myCustomer.customerBalance } open x2 with #sql{ SELECT customer_name, customer_balance FROM Customer WHERE customer_number = :myCustomer.customerNumber } for myCustomer; open x3 with #sql{ call aResultSetStoredProc(:parameter) }
If you do not specify hold, the first run of the fourth part of step three fails because the cursor is no longer open.
Each relational database management system (RDBMS) has its own version of SQL. Not all SQL commands are available in every implementation. Before coding any embedded SQL, check the documentation for your RDBMS.
Platform | Issue |
---|---|
Java generation | The hold option is available for Java programs only if the JDBC driver supports JDBC 3.0 or higher. |
CICS® | If the program is running in segmented mode, a converse statement ends the CICS transaction and prevents the program from retaining a file or database position, even if you use the hold keyword in the open statement. |
IMS/VS | Every converse statement runs in segmented mode; this ends the IMS™ transaction and prevents the program from retaining a file or database position, even if you use the hold keyword in the open statement. |
Cloudscape® or Derby databases | EGL does not support an open statement that uses both the scroll and forUpdate flags; you can use one or the other. |