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, 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
The scroll option is available only
if you are generating output in Java™.
- hold
- Maintains position in a result set when a commit occurs. The hold option
is available for Java programs only if the JDBC driver supports JDBC
3.0 or higher. The option is available for COBOL programs; however, if a program
targeted for CICS® is
segmented, the option hold has little value because
a converse in a segmented program ends the CICS transaction and prevents the program
from retaining any file or database position.
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)
}