OPEN Statement

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.
idmscu19
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.
This article describes the following information:
2
2
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.
    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.
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.
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