ALLOCATE CURSOR

Allocates an extended cursor.

 

Usage

Embedded, Module.

Description

The value of the extended-cursor-name is associated with the prepared statement specified by the extended-statement-name. Extended cursors and statements differ from ‘normal’ cursors and statements in that they are identified by a host variable or a string-literal, instead of by an identifier. The host variable must be declared in the DECLARE SECTION of the compilation unit as a character string variable.

The association between the cursor and the statement is preserved until the prepared statement is destroyed, see DEALLOCATE PREPARE, at which time the cursor is also destroyed.

A cursor allocated WITH HOLD will be a holdable cursor. An open holdable cursor is not closed when a transaction is committed.

WITHOUT HOLD and NO SCROLL are the default cursor attributes, therefore you do not need to specify them.

A cursor allocated as REOPENABLE may be opened several times in succession and previous cursor states are saved on a stack, see OPEN. Saved cursor states are restored when the current state is closed, see CLOSE.

A cursor allocated as SCROLL will be a scrollable cursor. For a scrollable cursor, records can be fetched using an orientation specification. See the description of FETCH for a description of how the orientation is specified.

Restrictions

A cursor for a result set procedure call must not be allocated WITH HOLD.

Notes

The extended statement must identify a statement previously prepared in the scope of the extended-statement-name. That prepared statement must be a query expression.

There must be no other extended cursor with the same name allocated in the same compilation unit.

Cursors should normally be allocated WITHOUT HOLD (default), because WITH HOLD cursors require more internal resources than ordinary cursors.

A re-openable cursor can be used to solve the ‘Parts explosion’ problem. Refer to the Mimer SQL Programmer's Manual, The 'Parts explosion' Problem for a description of this.

Example

exec sql PRERARE 'stmA' FROM :sqlstr;

exec sql DESCRIBE OUTPUT 'stmA' USING SQL DESCRIPTOR 'descrOut';

exec sql GET DESCRIPTOR 'descrOut' :cnt = COUNT;

if (cnt > 0) {

   /* The statement is returning a result set.

      Allocate a cursor to be used when reading it. */

    exec sql ALLOCATE 'curA' SCROLL CURSOR FOR 'stmA';

…

Standard Compliance