OPEN

OPEN is the second step of cursor processing. It readies the cursor for row retrieval by executing the SELECT that was assigned in the DECLARE.

What OPEN does

• Readies the cursor for row retrieval.
• Reads the current values of the host variables used in the SELECT's WHERE clause.
• Executes the SQL statement and builds the result table.
• Positions the cursor before the first row of the result set - no row is read yet.
• OPEN is coded in the Procedure Division, usually in its own paragraph like OPEN-PARA.

Syntax and example

• Syntax: EXEC SQL OPEN <cursor-name> END-EXEC.
• Example:
  
  MOVE 20 TO WS-DEPTNO. EXEC SQL OPEN EMPCUR END-EXEC. IF SQLCODE NOT = 0 DISPLAY "OPEN FAILED, SQLCODE = " SQLCODE STOP RUN END-IF.
• Always check SQLCODE after OPEN. 0 means the cursor is open and ready; a negative value means the OPEN failed.
• Set the host variables (like WS-DEPTNO) BEFORE the OPEN, because OPEN reads them at execution time.

OPEN rules and re-opening

• A cursor must be DECLARED before it can be OPENed.
• Opening a cursor that is already open gives SQLCODE -502. CLOSE it first, then OPEN again.
• To run the same cursor with different host variable values, CLOSE it, change the values, and OPEN it again - the SELECT runs fresh each time.
• If the SELECT finds no rows, OPEN still succeeds with SQLCODE 0. The first FETCH will then return SQLCODE 100.
• OPEN does not return any data into host variables - only FETCH does that.