OPEN

Invocation

Description

cursor-name

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

The SELECT statement of the cursor is either one of the following types of SELECT statements:

• The prepared SELECT statement that is identified by the statement-name that is 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 host variables that are specified in the USING clause of the OPEN statement. The rows of the result table can be derived during the execution of the OPEN statement. The cursor is placed in the open state and positioned before the first row of its result table.

USING

Introduces a list of host variables whose values are substituted for the parameter markers (question marks):

• If the DECLARE CURSOR statement included statement-name, the statement was prepared with a PREPARE statement. The host variables specified in the USING clause of the OPEN statement replace any parameter markers in the prepared statement. This reflects the typical use of the USING clause of the OPEN statement For an explanation of parameter marker replacement, see PREPARE.

  If the prepared statement 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 variables that must be described in the application program in accordance with the rules for declaring host structures and variables. When the statement is executed, a reference to a 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. Where appropriate, locator variables can be provided as the source of values for parameter markers.

Notes

Closed state of cursors: All cursors in an application process are in the closed state when:

• The application process is started.
• A new unit of work is started for the application process.

A cursor can also be in the closed state because:

• A CLOSE statement was executed.
• An error was detected that made the position of the cursor unpredictable.

To retrieve rows from the result table of a cursor, you must 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.

Parameter marker replacement: Before the OPEN statement is executed, each parameter marker in the query is effectively replaced by its corresponding host variable. The replacement is an assignment operation in which the source is the value of the host variable and the target is a variable within IMS. The assignment rules are those described for assignment to a column in Assignment and comparison.

When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by the value of its corresponding host variable. For more on the process of replacement, see Parameter marker replacement.

Examples

Example 1: Execute an OPEN statement, which places the cursor at the beginning of the rows to be fetched.

EXEC SQLIMS                         
  DECLARE C1 CURSOR FOR DYSQL 
END-EXEC.                        

EXEC SQLIMS                               
   PREPARE DYSQL FROM :SELECT-STATEMENT   
END-EXEC           

EXEC SQLIMS OPEN C1 END-EXEC.
 
EXEC SQLIMS FETCH C1 INTO :HOSPCODE, :HOSPNAME, :WARDNAME, :PATNAME END-EXEC.
 
   IF SQLIMSCODE = 100
      PERFORM DATA-NOT-FOUND
   ELSE
      PERFORM GET-REST-OF-HOSP
      UNTIL SQLIMSCODE IS NOT EQUAL TO ZERO.
 
   EXEC SQLIMS CLOSE C1 END-EXEC.