EGL Reference Guide for iSeries

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, seeresultSetID.
hold
Maintains position in a result set when a commit occurs.
Note:
The hold option is available only for COBOL programs.

The hold option is appropriate in the following case:

You code might do as follows, for example:

  1. Declare and open a cursor by running an EGL open statement
  2. Fetch a row by running an EGL get next statement
  3. 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 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:

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

Error conditions

Various conditions are not valid, including these:

A problem also arises in the following case:

  1. You customize an EGL open statement for update, but fail to indicate that a particular SQL table column is available for update; and
  2. 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:


Related concepts
Record types and properties
SQL support
resultSetID
References to parts


Related tasks
Syntax diagram


Related reference
add
close
delete
EGL statements
Exception handling
execute
get
get next
get previous
I/O error values
prepare
replace
SQL item properties
sysVar.terminalID


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]