 |
» |
|
|
|
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.
|
