|
|
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.
Application Programs and Procedures Only
OPEN CursorName [KEEP CURSOR [WITH LOCKS
WITH NOLOCKS]]
[USING { [SQL]DESCRIPTOR {SQLDA
AreaName}
HostVariableName[[INDICATOR]:IndicatorVariable][,...]} ]
- 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
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.
|