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]]
  [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.

OPEN

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description