open

The EGL open statement selects a set of rows from a relational database for later retrieval with get next statements. The open statement may operate on a cursor or on a called procedure.

Syntax diagram for the open statement, without SQL record

Syntax diagram for the open statement, with SQL record

resultSetID

ID that ties the open statement to later get next, replace, delete, and close statements. For details, see resultSetID.

scroll

Option that lets you move through a result set in various ways. The statement get next is always available to you, but use of scroll allows you to use the following statements too:

get absolute
get current
get first
get last
get previous
get relative

hold

Maintains position in a result set when a commit occurs.

Note: The hold option is available only if the JDBC driver supports JDBC 3.0 or higher.

The hold option is appropriate in the following case:

You are using the EGL open statement to open a cursor rather than a stored procedure; and
You want to commit changes periodically without losing your position in the result set; and
Your database management system supports use of the WITH HOLD option in the SQL cursor declaration.

You code might do as follows, for example:

Declare and open a cursor by running an EGL open statement
Fetch a row by running an EGL get next statement
Do the following in a loop--
1. Process the data in some way
2. Update the row by running an EGL replace statement
3. Commit changes by running the system function sysLib.commit
4. Fetch another row by running an EGL get next statement

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

Cursors for which you specify hold are not closed on a commit, but a rollback or database connect closes all cursors.

If you have no need to retain cursor position across a commit, do not specify hold.

forUpdate

Option that lets you use a later EGL statement to replace or delete the data that was retrieved from the database.

You cannot specify forUpdate if you are calling a stored procedure to retrieve a result set.

usingKeys ... item

Identifies a list of key items that are used to 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 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.

The usingKeys information is ignored if you specify an explicit SQL statement.

with #sql{ sqlStatement }

An explicit SQL SELECT statement, which is optional if you also specify an SQL record. Leave no space between the #sql and the left brace.

into ... item

An INTO clause, which identifies the EGL host variables that receive values from the cursor or stored procedure. In a clause like this one (which is outside of a #sql{ } block), do not include a semicolon before the name of a host variable.

with preparedStatementID

The identifier of an EGL prepare statement that prepares an SQL SELECT or CALL statement at run time. The open statement runs the SQL SELECT or CALL statement dynamically. For details, see prepare.

using ... item

A USING clause, which identifies the EGL host variables that are made available to the prepared SQL SELECT or CALL statement at run time. In a clause like this one (which is outside of a #sql{ } block), do not include a semicolon before the name of a host variable.

SQL record name

Name of a record of type SQLRecord. Either the record name or a value for sqlStatement is required; if sqlStatement is omitted, the SQL SELECT statement is derived from the SQL record.

Examples are as follows (assuming an SQL record called emp):

  open empSetId forUpdate for emp;

  open x1 with
    #sql{
      select empnum, empname, empphone 
      from employee 
      where empnum >= :empnum
               for update of empname, empphone
    }

  open x2 with 
    #sql{
      select empname, empphone
      from employee
      where empnum = :empnum
    }
  for emp;

  open x3 with
    #sql{
      call aResultSetStoredProc(:argumentItem)
    }

Default processing

The effect of an open statement is as follows by default, when you specify an SQL record:

The open statement makes a set of rows available. Each column in the selected rows is associated with a structure item, and except for the columns that are associated with a read-only structure item, all the columns are available for subsequent update by an EGL replace statement.
If you declare only one key item for the SQL record, the open statement selects all rows that fulfill the record-specific default select condition, so long as the value in the SQL table key column is greater than or equal to the value in the key item of the SQL record.
If multiple keys are declared for the SQL record, the record-specific default select condition is the only search criterion, and the open statement retrieves all rows that meet that criterion.
If you specify neither a record key nor 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 is true by default:

The FOR UPDATE clause (if any) does not include structure items that are read only

The SQL SELECT statement for a particular record is similar to the following statement:

  SELECT column01, 
         column02, ... 
         columnNN
  INTO   :recordItem01,
         :recordItem02, ...
         :recordItemNN
  FROM   tableName 
  WHERE  keyColumn01 = :keyItem01
  FOR UPDATE OF
         column01, 
         column02, ... 
         columnNN

You may override the default by specifying an SQL statement in the EGL open statement.

Error conditions

Various conditions are not valid, including these:

You include an SQL statement that lacks a clause required for SELECT; the required clauses are SELECT, FROM, and (if you specify forUpdate) FOR UPDATE OF
Your SQL record is associated with a column that either does not exist at run time or is incompatible with the related structure item
You specify the option forUpdate, and your code tries to run an open statement against either of the following SQL records:
- An SQL record whose only structure items are read only; or
- An SQL record that is related to more than one SQL table.

A problem also arises in the following case:

You customize an EGL open statement for update, but fail to indicate that a particular SQL table column is available for update; and
The replace statement that is related to the open statement tries to revise the column.

You can solve this problem in any of these ways:

When you customize the EGL open statement, include the column name in the SQL SELECT statement, FOR UPDATE OF clause; or
When you customize the EGL replace statement, eliminate reference to the column in the SQL UPDATE statement, SET clause; or
Accept the defaults for both the open and replace statements.

Related tasks
Syntax diagram