OPEN

The OPEN statement opens a cursor.

Invocation

This statement can only be embedded in 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

Identifies the cursor to be opened. The cursor-name must identify a declared cursor as explained in the Notes for the DECLARE CURSOR statement. When the OPEN statement is executed, the cursor must be in the closed state.

The SELECT statement associated with the cursor is either:

• The select-statement specified in the DECLARE CURSOR statement, or
• The prepared select-statement identified by the statement-name specified in the DECLARE CURSOR statement. If the statement has not been successfully prepared, or is not a select-statement, the cursor cannot be successfully opened.

The result table of the cursor is derived by evaluating the SELECT statement. The evaluation uses the current values of any special registers specified in the SELECT statement and the current values of any host variables specified in the SELECT statement or the USING clause of the OPEN statement. The rows of the result table can be derived during the execution of the OPEN statement and a temporary table can be created to hold them; or they can 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 position 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, you must use USING. If the prepared statement does not include parameter markers, USING is ignored.

host-variable,...

Identifies host structures or host variables that must be declared in the program in accordance with the rules for declaring host structures and host variables. A reference to a host structure is replaced by a reference to each of its 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.

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. (The rules for REXX are different. For more information see the SQL Programming with Host Languages book.)

• 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*(80), where 80 is the length of an SQLVAR occurrence. If LOBs or distinct types are specified, there must be two SQLVAR entries for each parameter marker and SQLN must be set to two times the number of parameter markers.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. It must be the same as the number of parameter markers in the prepared statement. The nth variable described by the SQLDA corresponds to the nth parameter marker in the prepared statement. For a description of an SQLDA, see Appendix C, SQL Descriptor Area (SQLDA).

Note that because RPG/400 does not provide the facility for setting pointers and the SQLDA uses pointers to locate the appropriate host variables, you will have to set these pointers outside your RPG/400 application.

Parameter Marker Replacement

When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by its corresponding host variable. With the exception of the LIKE predicate, the replacement of a parameter marker is an assignment operation in which the source is the value of the host variable, and the target is a variable within the database manager. The attributes of the target variable are determined as follows:

• If the parameter marker was specified as the operand of an arithmetic operator, the data type, scale, and precision of the target are the same as the other operand of that operator.
• If the parameter marker was specified as a comparison operand in any predicate other than the BETWEEN, IN, or LIKE predicates, the attributes of the target are the same as the other operand of the predicate, unless the data type of that operand is DATE, TIME, or TIMESTAMP, in which case, the target is effectively character with a length attribute of 32766.
• If the parameter marker was specified as a comparison operand in a BETWEEN or IN predicate, the attributes of the target are assigned as follows:
  • If only one operand is not a parameter marker, the attributes of the target are the attributes of the operand.
  • If more than one operand is not a parameter marker, the attributes of the target are those that would result if those operands were specified in a VALUE scalar function, except the CCSID attribute is the CCSID of its corresponding host variable.
• If the parameter marker was specified as the pattern or ESCAPE character of a LIKE predicate, the host variable used to replace the parameter marker must be compatible with the first operand.

  If the parameter marker was specified as the pattern, the data type of the target is VARCHAR (if the first operand is CHAR or VARCHAR) or VARGRAPHIC (if the first operand is GRAPHIC or VARGRAPHIC). The length attribute of the target is the same as that of the first operand.

  If the parameter marker was specified as the ESCAPE character, the length attribute of the target is 1.

  Note:

  When the pattern specified in a LIKE predicate is a parameter marker, and a fixed-length character host variable is used to replace the parameter marker; specify a value for the host variable that is the correct length. If you do not specify the correct length, the select will not return the intended results. For example, if the host variable is defined as CHAR(10), and the value WYSE% is assigned to that host variable, the host variable is padded with blanks on assignment. The pattern used is 'WYSE% ' This pattern requests the database manager to search for all values that start with WYSE and end with five blank spaces. If you intended to search for only the values that start with 'WYSE' you should assign the value 'WSYE%%%%%%' to the host variable.

• If the parameter marker was specified as an operand in a VALUE, COALESCE, IFNULL, MIN, MAX, LAND, LOR, or XOR scalar function, the attributes of the target are assigned as follows:
  • If only one operand is not a parameter marker, the attributes of the target are the attributes of the operand.
  • If more than one operand is not a parameter marker, the attributes of the target are those that would result if the parameter markers were removed from the scalar function.

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 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.
• If the target cannot contain nulls, the value of V must not be null.

However, unlike the rules for assigning a value to a column:

• If V is a string, the value will be truncated (without an error), if its length is greater than the length attribute 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 called:
  • If CLOSQLCSR(*ENDPGM) is specified, all cursors are in the closed state each time the program is called.
  • If CLOSQLCSR(*ENDSQL) is specified, all cursors are in the closed state only the first time the program is called as long as one SQL program remains on the call stack.
  • If CLOSQLCSR(*ENDJOB) is specified, all cursors are in the closed state only the first time the program is called as long as the job remains active.
  • If CLOSQLCSR(*ENDMOD) is specified, all cursors are in the closed state each time the module is initiated.
  • If CLOSQLCSR(*ENDACTGRP) is specified, all cursors are in the closed state only the first time the module in the program is initiated in the activation group.
• A program starts a new unit of work by executing a COMMIT or ROLLBACK statement without a HOLD option. Cursors declared with the HOLD option are not closed by a COMMIT statement.
• A CONNECT (Type 1) statement was executed.

A cursor can also be in the closed state because:

• A CLOSE statement was executed.
• A DISCONNECT statement disconnected the connection with which the cursor was associated.
• The connection with which the cursor was associated was in the release-pending state and a successful COMMIT occurred.

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

Effect of temporary tables

If the result table of a cursor is not read-only, its rows are derived during the execution of subsequent FETCH statements. The same method may be used for a read-only result table. However, if a result table is read-only, DB2 UDB for AS/400 may choose to use the temporary table method instead. With this method the entire result table is inserted into 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.
• The INSERT, UPDATE, and DELETE statements that are executed 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. 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 you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A subsequent FETCH C might or might not retrieve the new row of T.

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 PL/I program. Assume each prepared select-statement always defines two items in its select list with the first item having a data type of integer and the second item having a data type of varchar(64). (The related host variable definitions, PREPARE statement, and DECLARE CURSOR statement are also shown in the example below.)

   EXEC SQL  BEGIN DECLARE SECTION;
     DCL  HV_INT      BINARY    FIXED(31);
     DCL  HV_VCHAR64  CHAR(64)  VARYING;
     DCL  STMT1_STR   CHAR(200)  VARYING;
   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 3, but in this case the number and data types of the items in the select statement are not known.

   EXEC SQL  BEGIN DECLARE SECTION;
     DCL  STMT1_STR   CHAR(200)  VARYING;
   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;