open considerations for SQL

In the context of SQL, the EGL open statement creates a result set from a relational database and creates a cursor that you can use to move through the list.

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.

Syntax

Syntax diagram for the open statement
resultSetID
A character string of your choice to identify the result set that the open statement creates. You can use the ID with a positional get statement, or with the replace, delete, or close statements.
scroll
Option that enables a positional get statement to move through the result set. For more about positional get statements, see get considerations for SQL. This option is available only if you are generating output in Java™.
hold
Option that causes the program to retain position in a result set when a commit occurs. The hold option is appropriate when all of the following conditions are true:
  • You use the EGL open statement to open a cursor rather than to call a stored procedure.
  • You want to commit changes periodically without losing your position in the result set.
  • Your database management system supports use of the WITH HOLD option in the SQL cursor declaration.

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.

forUpdate
Option that enables later EGL statements to replace or delete the data that was retrieved from the database. Do not use this option when you call a stored procedure.
usingKeys ... field
You can specify field names from your SQL record here to override the key fields you specified in that record definition. The field or fields you specify with the usingKeys clause build the key-value component of the WHERE clause in an implicit SQL statement. The implicit SQL statement is used at run time if you do not specify an explicit SQL statement. If you include a usingKeys clause, you must also specify an SQL record variable.

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.

with #sql{ sqlStatement }
Specifies an embedded SQL SELECT statement, which is optional if you also specify an SQL record variable. Embedded code takes precedence over any implicit code EGL might otherwise generate. Do not leave space between the #sql directive and the left brace.
into target
Corresponds to an SQL INTO clause, which identifies the EGL host variables (see Host variables) that receive values from the result set. You can alternately specify a record name, in which case EGL uses all fields from the record. In a clause like this one (which is outside of a #sql directive), do not include a colon before the name of a host variable.
preparedStatementID
Identifies code created with an EGL prepare statement, allowing dynamic processing. For details, see prepare considerations for SQL.
using field
Corresponds to an SQL USING clause, which identifies the EGL host variables that are made available to the prepared statement at run time. In a using field clause (which is outside of a #sql directive), do not include a colon before the name of a host variable.
SQLRecordVariable
The name of an SQL record variable. If you do not specify an SQL record variable, you must provide embedded SQL code (with the #sql directive). If you do provide an SQL record variable, and do not specify embedded SQL code, EGL uses information in the record properties to create an implicit SQL SELECT statement. You can query the variable after the open operation to detect error conditions; for more information, see "Error conditions" in this topic.

Default processing

By default, when you specify an SQL record variable, an open statement has the following effect:
  • The open statement makes a set of rows available. EGL uses the mapping in the SQL Record part to associate each column in the selected rows with a record variable field. Except for the columns that are associated with a read-only record field, all the columns are available for subsequent update by an EGL replace statement.
  • If you declare only one key field for the SQL record, the open statement selects all rows that meet the following conditions:
    • The rows fulfill the record-specific defaultSelectCondition property of the SQL Record part.
    • The value in the SQL table key column is greater than or equal to the value in the key field of the SQL record variable.
  • If you do not declare any key fields or multiple key fields for the SQL record, the record-specific defaultSelectCondition provides the only search criteria, and the open statement retrieves all rows that meet those criteria.
  • If you do not specify a record key or a default selection condition, the open statement selects all rows in the table.
  • The selected rows are not sorted.
The EGL open statement is represented in the generated code by a cursor declaration that includes an SQL SELECT or an SQL SELECT FOR UPDATE statement. The following conditions are true by default:
  • The FOR UPDATE OF clause does not include record fields that are read only.
  • The SQL SELECT statement for a particular record is similar to the following 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).

Examples

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)
    }
Here is an example of processing flow that requires the hold keyword:
  1. Declare and open a cursor with an EGL open statement.
  2. Fetch a row with an EGL get next statement.
  3. Perform the following actions in a loop:
    1. Process the data in some way.
    2. Update the row with an EGL replace statement.
    3. Commit changes with the sysLib.commit() system function.
    4. Fetch another row with an EGL get next statement.

If you do not specify hold, the first run of the fourth part of step three fails because the cursor is no longer open.

Error conditions

The following situations are not valid:
  • You include an embedded SQL statement that lacks a clause required for SELECT; the required clauses are SELECT, FROM, and (if you specify the forUpdate option) FOR UPDATE OF.
  • Your SQL record variable is associated with a column that either does not exist at run time or is incompatible with the related record field.
  • You specify the forUpdate option, and your code tries to run an open statement against either of the following kinds of SQL records:
    • An SQL record whose only record fields are read only.
    • An SQL record that is related to more than one SQL table.
There is also a problem when both of the following conditions are true:
  1. You customize an EGL open statement for update, but do not indicate that a particular SQL table column is available for update.
  2. The replace statement that is related to the open statement tries to revise the column.
You can solve this problem in any of the following ways:
  • When you customize the EGL open statement, include the column name in the FOR UPDATE OF clause of the SQL SELECT statement.
  • When you customize the EGL replace statement, eliminate reference to the column in the SET clause of the SQL UPDATE statement.
  • Accept the implicit SQL code that EGL generates for both the open and replace statements.

Compatibility

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.

Table 1. Compatibility considerations for open and SQL
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.

Feedback