OPEN

The OPEN data manipulation statement places a specified cursor in the open state. An open cursor represents a result table, an ordering of the rows in the result table, and a position relative to the ordering.

You can use this statement only in SQL that is embedded in a program.

Authorization

None required.

Syntax

►►─ OPEN cursor-name ─────────────────────────────────────────────────────────►

 ►──┬───────────────────────────────────────────────────────────────┬─────────►◄
    │            ┌──────── , ───────────┐                           │
    └─ USING ──┬─▼┬─ host-variable ────┬┴────────────────────────┬──┘
               │  │─ local-variable ───┤                         │
               │  └─ routine-parameter ┘                         │
               └─ :dyn-buff sql DESCRIPTOR descriptor-area-name ─┘

Parameters

cursor-name

Specifies the cursor to be opened. Cursor-name must identify a cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.

USING

Supplies values for the dynamic parameters embedded in the text of the dynamically prepared statement with which the cursor is associated.

host-variable

Identifies the host variables from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first host variable to the first dynamic parameter, the second host variable to the second dynamic parameter, and so on.

You must specify the same number of host variables in the USING parameter as the number of dynamic parameter markers in the dynamically prepared statement with which the cursor is associated. See Expansion of Host-variable, for more information.

Note: In COBOL, host-variable can be an elementary data item or a non-bulk structure. If a non-bulk structure is specified, each sub-element of the structure is counted as a host variable.

local-variable

routine-parameter

Identifies the local variable or routine parameter from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first local variable or routine parameter to the first dynamic parameter, the second local variable or routine parameter to the second dynamic parameter, and so on. You must specify the same number of local variables and routine parameters in the USING parameter as the number of dynamic parameter markers in the dynamically prepared statement with which the cursor is associated.

:dyn-buff

Identifies the variable from which CA IDMS is to retrieve values for the dynamic parameters.

Dyn-buff must identify a variable previously declared in the host-language application program or SQL routine.

The size of dyn-buff must be sufficient to hold a complete set of dynamic parameter values. The format of the data in dyn-buff must conform to the description in the SQL descriptor area specified by descriptor-area-name.

SQL DESCRIPTOR

Specifies the SQL descriptor area that describes the format of the dynamic parameter values contained in dyn-buff.

descriptor-area-name

Directs CA IDMS to use the named area as the descriptor area. Descriptor-name must identify an SQL descriptor area.

Note: The dyn-buff variable can be a host variable, or when the statement is used in an SQL routine, a local variable or a routine parameter. In this case, its name must not be preceded with a colon.

For the layout of an SQL descriptor area, see SQL Descriptor Area.

Usage

Cursor Positions

At any time, the position of an open cursor is one of the following:

Before a certain row
About a certain row
After the last row

A cursor can be before the first row or after the last row of a result table even if the table is empty.

When a cursor is first opened, its position is before the first row.

Effect on an Open Cursor

If the cursor named in an OPEN statement is already open, CA IDMS returns an error and continues processing.

Example

Opening a Cursor

The following OPEN statement places the cursor named ALL_EMP_CURSOR in the open state:

EXEC SQL
   OPEN ALL_EMP_CURSOR
END-EXEC

More Information

For more information about defining and manipulating cursors, see ALLOCATE CURSOR, CLOSE, DECLARE CURSOR, and FETCH.
For more information about using cursors in an application program, see the CA IDMS SQL Programming Guide.
For more information about the layout of the descriptor area, see SQL Descriptor Area.