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:

• Including the SELECT statement in the DECLARE CURSOR statement

• Including a statement-name that names a prepared SELECT statement.

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

• When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by its corresponding host variable. For a typed parameter marker, the attributes of the target variable are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable are determined according to the context of the parameter marker. See Rules for the rules affecting parameter markers.

• Let V denote a host variable that corresponds to parameter marker P. The value of V is assigned to the target variable for P in accordance with the rules for assigning a value to a column. Thus:

  • V must be compatible with the target.

  • If V is a string, its length must not be greater than the length attribute of the target.

  • If V is a number, the absolute value of its integral part must not be greater than the maximum absolute value of the integral part of the target.

  • If the attributes of V are not identical to the attributes of the target, the value is converted to conform to the attributes of the target.

  When the SELECT statement of the cursor is evaluated, the value used in place of P is the value of the target variable for P. For example, if V is CHAR(6), and the target is CHAR(8), the value used in place of P is the value of V padded with two blanks.

• The USING clause is intended for a prepared SELECT statement that contains parameter markers. However, it can also be used when the SELECT statement of the cursor is part of the DECLARE CURSOR statement. In this case the OPEN statement is executed as if each host variable in the SELECT statement were a parameter marker, except that the attributes of the target variables are the same as the attributes of the host variables in the SELECT statement. The effect is to override the values of the host variables in the SELECT statement of the cursor with the values of the host variables specified in the USING clause.

Notes

• Closed state of cursors:  All cursors in a program are in the closed state when the program is initiated and when it initiates a ROLLBACK statement.

  All cursors, except open cursors declared WITH HOLD, are in a closed state when a program issues a COMMIT statement.

  A cursor can also be in the closed state because a CLOSE statement was executed or an error was detected that made the position of the cursor unpredictable.

• To retrieve rows from the result table of a cursor, execute a FETCH statement when the cursor is open. The only way to change the state of a cursor from closed to open is to execute an OPEN statement.

• Effect of temporary tables:  In some cases, the result table of a cursor is derived during the execution of FETCH statements. In other cases, the temporary table method is used instead. With this method the entire result table is transferred to a temporary table during the execution of the OPEN statement. When a temporary table is used, the results of a program can differ in these two ways:

  • An error can occur during OPEN that would otherwise not occur until some later FETCH statement.

  • INSERT, UPDATE, and DELETE statements executed in the same transaction while the cursor is open cannot affect the result table.

  Conversely, if a temporary table is not used, INSERT, UPDATE, and DELETE statements executed while the cursor is open can affect the result table if issued from the same unit of work. The Application Development Guide describes how locking can be used to control the effect of INSERT, UPDATE, and DELETE operations executed by concurrent units of work. Your result table can also be affected by operations executed by your own unit of work, and the effect of such operations is not always predictable. For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and a new row is inserted into T, the effect of that insert on the result table is not predictable because its rows are not ordered. Thus a subsequent FETCH C may or may not retrieve the new row of T.

• Statement caching affects cursors declared open by the OPEN statement. See the Notes for information.

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;

[ DB2 List of Books | Search the DB2 Books ]