 |
» |
|
|
|
The OPEN statement is used in an application program or a procedure to open
a cursor, that is, make the cursor and its associated active
set available to manipulate a query result.
Scope | |
Application Programs and Procedures Only
SQL Syntax | |
OPEN CursorName [KEEP CURSOR [ WITH LOCKS WITH NOLOCKS ]] [USING {[SQL] DESCRIPTOR { SQLDA AreaName } HostVariableName [ [INDICATOR] :IndicatorVariable ] [,...] } ] Parameters | |
- CursorName
specifies the cursor to be opened. The cursor name must first be
defined with a DECLARE CURSOR statement.
- KEEP CURSOR
maintains the cursor position across transactions until a CLOSE
statement is issued on the cursor.
This clause is not available for procedure cursors (those declared for
an EXECUTE PROCEDURE statement).
- WITH LOCKS
keeps only those locks associated with the position of the kept
cursor after a COMMIT WORK statement, and releases all other locks. This is the
default.
- WITH NOLOCKS
releases all
locks associated with the kept cursor after a COMMIT WORK statement.
- USING
allows dynamic parameter substitution in a prepared statement.
This clause can only be specified within an application when opening a cursor
on a dynamically
prepared SELECT or EXECUTE PROCEDURE statement.
- SQL DESCRIPTOR
specifies a location that at run time
contains the data value assigned to an input dynamic parameter
specified in a prepared SELECT or EXECUTE PROCEDURE statement.
Specify the same location (SQLDA or AreaName) as you specified in the DESCRIBE INPUT statement.
- SQLDA
specifies that a data structure of sqlda_type named sqlda
is used to pass dynamic parameter data between the application
and ALLBASE/SQL.
- AreaName
specifies the user defined name of a data structure of
type sqlda_type that is used to pass dynamic parameter data
between the application and ALLBASE/SQL.
- HostVariableName
specifies a host variable name that at run time
contains the data value that is assigned to an input dynamic parameter
specified in the parameter list of a prepared SELECT or
EXECUTE PROCEDURE statement.
Host variables must be specified in the same order as the dynamic
parameters in the prepared statement they represent.
There must be a one to one correspondence between host variable names and the dynamic parameters. A maximum of 1023 host variables names can be specified.
- IndicatorVariable
names an indicator variable, whose value
determines whether the associated host variable contains a NULL value:
- > = 0
the value is not NULL
- < 0
the value is NULL
Description | |
For a select cursor, ALLBASE/SQL examines any input host variables and
input dynamic parameters used in
the cursor definition, determines the cursor's active
set, positions the cursor before the first row of the
active set, and leaves the cursor in the open state.
No rows are actually available to your
application program until a FETCH statement is executed.
For a procedure cursor, ALLBASE/SQL examines any input host variables and
input dynamic parameters used in the cursor definition.
No rows are actually available to your
application program, nor does procedure execution begin, until ADVANCE and/or
FETCH statements are executed.
For a select cursor, the KEEP CURSOR
option lets you maintain the cursor position in an
active set beyond transaction boundaries.
When you use this option, the COMMIT WORK
and ROLLBACK WORK statements do not automatically close the
cursor. Instead, you must explicitly close the cursor and then issue a
COMMIT WORK.
Cursors not using the KEEP CURSOR option are automatically closed when a
transaction terminates or a ROLLBACK WORK TO SAVEPOINT is executed.
|
