ALLOCATE CURSOR

The ALLOCATE CURSOR statement defines a cursor and associates it with a result set locator variable.

Invocation

This statement can be embedded in an application program. It is an executable statement that can be dynamically prepared. It cannot be issued interactively.

Authorization

None required.

Syntax


>>-ALLOCATE--cursor-name---------------------------------------->

>--CURSOR FOR RESULT SET--rs-locator-variable------------------><

Description

cursor-name: Names the cursor. The name must not identify a cursor that has already been declared in the source program.
CURSOR FOR RESULT SET rs-locator-variable: Specifies a result set locator variable that has been declared in the application program according to the rules for declaring result set locator variables.
The result set locator variable must contain a valid result set locator value, as returned by the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE SQL statement. The value of the result set locator variable is used at the time the cursor is allocated. Subsequent changes to the value of the result set locator have no affect on the allocated cursor. The result set locator value must not be the same as a value used for another cursor allocated in the source program.

Notes

Dynamically prepared ALLOCATE CURSOR statements: The EXECUTE statement with the USING clause must be used to execute a dynamically prepared ALLOCATE CURSOR statement. In a dynamically prepared statement, references to host variables are represented by parameter markers (question marks). In the ALLOCATE CURSOR statement, rs-locator-variable is always a host variable. Thus, for a dynamically prepared ALLOCATE CURSOR statement, the USING clause of the EXECUTE statement must identify the host variable whose value is to be substituted for the parameter marker that represents rs-locator-variable.

You cannot prepare an ALLOCATE CURSOR statement with a statement identifier that has already been used in a DECLARE CURSOR statement. For example, the following SQL statements are invalid because the PREPARE statement uses STMT1 as an identifier for the ALLOCATE CURSOR statement and STMT1 has already been used for a DECLARE CURSOR statement.

   DECLARE CURSOR C1 FOR STMT1;
   PREPARE STMT1 FROM          INVALID
     'ALLOCATE C2 CURSOR FOR RESULT SET ?';

Rules for using an allocated cursor: The following rules apply when you use an allocated cursor:

You cannot open an allocated cursor with the OPEN statement.
You can close an allocated cursor with the CLOSE statement. Closing an allocated cursor closes the associated cursor defined in the stored procedure.
You can allocate only one cursor to each result set.

The life of an allocated cursor: A rollback operation, an implicit close, or an explicit close destroy allocated cursors. A commit operation destroys allocated cursors that are not defined WITH HOLD by the stored procedure. Destroying an allocated cursor closes the associated cursor defined in the stored procedure.

Considerations for scrollable cursors: Following an ALLOCATE CURSOR statement, a GET DIAGNOSTICS statement can be used to get the attributes of the cursor such as the following information (for more information, see GET DIAGNOSTICS):

DB2_SQL_ATTR_CURSOR_HOLD. Whether the cursor was defined with the WITH HOLD attribute.
DB2_SQL_ATTR_CURSOR_SCROLLABLE. Scrollability of the cursor.
DB2_SQL_ATTR_CURSOR_SENSITIVITY. Effective sensitivity of the cursor.
The sensitivity information can be used by applications (such as an ODBC driver) to determine what type of FETCH (INSENSITIVE or SENSITIVE) to issue for a cursor defined as ASENSITIVE.
DB2_SQL_ATTR_CURSOR_ROWSET. Whether the cursor can be used to access rowsets.
DB2_SQL_ATTR_CURSOR_TYPE. Whether a cursor type is forward-only, static, or dynamic.

In addition, if subsystem parameter DISABSCL is set to NO, a subset of the above information is returned in the SQLCA:

The scrollability of the cursor is in SQLWARN1.
The sensitivity of the cursor is in SQLWARN4.
The effective capability of the cursor is in SQLWARN5.

Example

The statement in the following example is assumed to be in a PL/I program.

Define and associate cursor C1 with the result set locator variable LOC1 and the related result set returned by the stored procedure:

   EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :LOC1;