SQL Reference

OPEN

The OPEN statement opens a cursor so that it can be used to fetch rows from its result table.

Invocation

Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

See DECLARE CURSOR for the authorization required to use a cursor.

Syntax

>>-OPEN--cursor-name----+------------------------------------+-><
                        |        .-,----------------.        |
                        |        V                  |        |
                        +-USING-----host-variable---+--------+
                        '-USING DESCRIPTOR--descriptor-name--'
 

Description

cursor-name
Names a cursor that is defined in a DECLARE CURSOR statement that was stated earlier in the program. When the OPEN statement is executed, the cursor must be in the closed state.

The DECLARE CURSOR statement must identify a SELECT statement, in one of the following ways:

The result table of the cursor is derived by evaluating that SELECT statement, using the current values of any host variables specified in it or in the USING clause of the OPEN statement. The rows of the result table may be derived during the execution of the OPEN statement and a temporary table may be created to hold them; or they may be derived during the execution of subsequent FETCH statements. In either case, the cursor is placed in the open state and positioned before the first row of its result table. If the table is empty the state of the cursor is effectively "after the last row".

USING
Introduces a list of host variables whose values are substituted for the parameter markers (question marks) of a prepared statement. (For an explanation of parameter markers, see PREPARE.) If the DECLARE CURSOR statement names a prepared statement that includes parameter markers, USING must be used. If the prepared statement does not include parameter markers, USING is ignored.

host-variable
Identifies a variable described in the program in accordance with the rules for declaring host variables. The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement. Where appropriate, locator variables and file reference variables can be provided as the source of values for parameter markers.

DESCRIPTOR descriptor-name
Identifies an SQLDA that must contain a valid description of host variables.

Before the OPEN statement is processed, the user must set the following fields in the SQLDA:

  • SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
  • SQLDABC to indicate the number of bytes of storage allocated for the SQLDA
  • SQLD to indicate the number of variables used in the SQLDA when processing the statement
  • SQLVAR occurrences to indicate the attributes of the variables.

The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.

If LOB result columns need to be accommodated, there must be two SQLVAR entries for every select-list item (or column of the result table). See Effect of DESCRIBE on the SQLDA, which discusses SQLDOUBLED and LOB columns.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. For more information, see Appendix C, SQL Descriptor Area (SQLDA).

Rules

Notes

Examples

Example 1:  Write the embedded statements in a COBOL program that will:

  1. Define a cursor C1 that is to be used to retrieve all rows from the DEPARTMENT table for departments that are administered by (ADMRDEPT) department 'A00'.
  2. Place the cursor C1 before the first row to be fetched.
      EXEC SQL  DECLARE C1 CURSOR FOR
                     SELECT DEPTNO, DEPTNAME, MGRNO 
                       FROM DEPARTMENT
                       WHERE ADMRDEPT = 'A00' 
      END-EXEC.
     
     
      EXEC SQL  OPEN C1
      END-EXEC.
    

Example 2:  Code an OPEN statement to associate a cursor DYN_CURSOR with a dynamically defined select-statement in a C program. Assuming two parameter markers are used in the predicate of the select-statement, two host variable references are supplied with the OPEN statement to pass integer and varchar(64) values between the application and the database. (The related host variable definitions, PREPARE statement, and DECLARE CURSOR statement are also shown in the example below.)

  EXEC SQL  BEGIN DECLARE SECTION;
    static short    hv_int;
    char            hv_vchar64[64];
    char            stmt1_str[200];
  EXEC SQL  END DECLARE SECTION;
 
  EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
  EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;
 
  EXEC SQL  OPEN DYN_CURSOR USING :hv_int, :hv_vchar64;

Example 3:  Code an OPEN statement as in example 2, but in this case the number and data types of the parameter markers in the WHERE clause are not known.

  EXEC SQL  BEGIN DECLARE SECTION;
    char    stmt1_str[200];
  EXEC SQL  END DECLARE SECTION;
  EXEC SQL  INCLUDE SQLDA;
 
  EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
  EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;
 
  EXEC SQL  OPEN DYN_CURSOR USING DESCRIPTOR :sqlda;


[ Top of Page | Previous Page | Next Page ]