HP 3000 Manuals HP 3000 Manuals
OPEN
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]]
[ {[SQL] DESCRIPTOR{SQLDA } }]
[USING { {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.