OPEN

Executes a prepared SQL query associated with a cursor and creates a result set composed of the rows that satisfy the query. This set of rows is called the active set.

Syntax

OPEN cursor_name[ USING {[ SQL ] DESCRIPTOR structure_name
 | :host_variable[[ INDICATOR ] :ind_variable] ,  ...}] ;

Parameters

cursor_name

An identifier named in an earlier DECLARE CURSOR statement.

USING [ SQL ] DESCRIPTOR structure_name

Directs the SQL engine to create the result set in storage addressed by the identified SQLDA structure.

USING :host_variable[[ INDICATOR ] :ind_variable]

Directs the SQL engine to create the result set in storage addressed by host variables.

Notes

• Executing an OPEN cursor statement sets the cursor to the open state.
• After the OPEN cursor statement is executed, the cursor is positioned just before the first row of the active set.
• For a single execution of an OPEN cursor statement, the active set does not change and the host variables are not re-examined.
• If you elect to retrieve a new active set and a host variable value has changed, you must CLOSE the cursor and OPEN it again.
• Execution of a COMMIT statement or ROLLBACK statement implicitly closes the cursors that have been opened in the current transaction.
• It is good practice to CLOSE cursors explicitly.
• When a cursor is in the open state, executing an OPEN statement on that cursor results in an error.
• If a DECLARE cursor statement is associated with a static SQL statement containing parameter markers, the following requirements apply:
  • You must execute the DECLARE statement before executing the OPEN statement for that cursor.
  • The DECLARE cursor statement and the OPEN statement for the same cursor must occur in the same transaction.
  • If the statement contains parameter markers for stack variables, the DECLARE cursor statement and the following OPEN statement for the same cursor must occur in the same C Language function.

Example

/*
**    5.  Name WHENEVER routine to handle SQLERROR.
**
**    6.  DECLARE cursor for the SELECT statement.
**        NOTE: You must set input parameter values before OPEN CURSOR.
**        The static query in this program does not have input parameters.
**
**    7.  OPEN the cursor.
**        NOTE: For static statements, if a DECLARE CURSOR
**              statement contains references to automatic variables,
**              the OPEN CURSOR statement must be in the same C function.
**
**    8.  Name WHENEVER routine to handle NOT FOUND condition.
*/
     
      EXEC SQL WHENEVER SQLERROR GOTO selerr ;
      EXEC SQL DECLARE stcur CURSOR FOR  
            SELECT InvTransNum, Qty,
            OrderNum FROM PUB.InventoryTrans ;
      EXEC SQL OPEN stcur ;      
      EXEC SQL WHENEVER NOT FOUND GOTO seldone ;