OPEN

Syntax:

>>--EXEC SQL---OPEN---cursor_name------------------------>

 >----.--------------------------------.----END-EXEC-----><
      +-USING DESCRIPTOR :sqlda_struct-+
      |       +- , -+                  |
      |       V     |                  |
      +-USING :hvar--------------------+

Parameters:

`cursor_name`	A previously declared cursor.
`:sqlda_struct`	An SQLDA data structure previously constructed by the application. The SQLDA data structure contains the address, data type, and length of each input parameter. This option is used only with a cursor that references a prepared SQL statement in the DECLARE statement. The colon is optional to provide compatibility with other embedded SQL implementations.
`:hvar`	One or more input host variables corresponding to parameter markers in the SELECT statement. This option is used only with a cursor that references a prepared SQL statement in the DECLARE statement.

Comments:

If the cursor is declared with a static SELECT statement (that is, one that was not prepared), the SELECT statement can contain host variables but not parameter markers . The current values of the host variables are substituted when the OPEN statement runs. For a statically declared cursor, the OPEN statement cannot contain the USING :hvar or USING DESCRIPTOR :sqlda_struct option.

If the cursor is declared with a dynamic SELECT statement (that is, one that was prepared), the SELECT statement can contain parameter markers but not host variables. Parameter markers can appear wherever column values are allowed in the SELECT statement. If the SELECT statement has parameter markers, the OPEN statement must include either the USING :hvar option with the same number of host variables or the USING DESCRIPTOR :sqlda_struct option identifying an SQLDA data structure already populated by the application.

With the USING DESCRIPTOR :sqlda_struct option, values of the program variables are substituted for parameter markers in the SELECT statement. These program variables are addressed by corresponding SQLDATA entries in the SQLDA data structure.

The number of parameter markers in the SELECT statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda_struct) or host variables (USING :hvar) in the OPEN statement.

Example:

*Declare the cursor...
     EXEC SQL
     DECLARE C1 CURSOR FOR
        SELECT staff_id, last_name
        FROM staff
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not declare cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

     EXEC SQL
        OPEN C1
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not open cursor.'
        DISPLAY SQLERRMC    
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

     PERFORM UNTIL sqlcode NOT = ZERO
*SQLCODE will be zero as long as it has successfully fetched data
        EXEC SQL
           FETCH C1 INTO :staff-staff-id, :staff-last-name
        END-EXEC
        IF SQLCODE = ZERO
          DISPLAY "Staff ID: " staff-staff-id
           DISPLAY "Staff member's last name: " staff-last-name
        END-IF
     END-PERFORM

     EXEC SQL
        CLOSE C1
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not close cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
     END-IF

Restriction: The following scenario can cause the COBOL compiler to fail to update output host variables when the cursor for a stored procedure call is closed. This can happen when stored procedures meet all three of the following criteria:

Are called using the OPEN statement
Return a return value, output parameters, or both
Return a result set

The failure occurs because the COBOL compiler, when possible, compiles 01-level fields and their subordinate 01-level FILLER fields as JVM types.

To ensure successful compilation, use traditional COBOL data types, such as PIC X(n), COMP-3, etc., for host variables rather than object types, such as string, binary-long, BigDecimal, etc., and do one of the following:

Compile using the NOILNATIVE COBOL compiler directive
Declare host variables that receive return values or output parameters at level 02 or higher, with a record name other than FILLER

This scenario does not apply to stored procedures called using the CALL statement.