Micro Focus Visual COBOL 2.3 for Visual Studio 2015 > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > OPEN

OPEN

Runs the SELECT statement specified in the corresponding DECLARE CURSOR statement to produce the results set that is accessed one row at a time by the FETCH statement.

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