EGL Reference Guide for iSeries
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.


- 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 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--
- Process the data in some way
- Update the row by running an EGL replace statement
- Commit changes by running the system function sysLib.commit
- 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)
}
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.
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 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 ]
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.