OPEN

Synopsis

OPEN cursor-name

Description

An OPEN statement opens a cursor according to the parameters specified in the cursor’s DECLARE statement. Once opened, a cursor can be fetched. An open cursor must be closed.

Attempting to open a cursor that is already open results in an SQLCODE -101 error. Attempting to fetch or close a cursor that is not open results in an SQLCODE -102 error. A successful OPEN sets SQLCODE = 0, even if the result set is empty.

OPEN does not support the #sqlcompile mode=Deferred preprocessor directive. Attempting to use Deferred mode with a DECLARE, OPEN, FETCH, or CLOSE cursor statement generates a #5663 compilation error.

As an SQL statement, this is only supported from embedded SQL. Equivalent operations are supported through ODBC using the ODBC API.

Example

The following embedded SQL example shows a cursor (named EmpCursor) being opened and closed:

   SET name="LastName,FirstName",state="##"
   &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name, Home_State
        INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'A')
   WRITE !,"BEFORE: Name=",name," State=",state 
   &sql(OPEN EmpCursor)
   IF SQLCODE '= 0 { WRITE "Open error: ",SQLCODE
                     QUIT }
   NEW %ROWCOUNT,%ROWID
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"DURING: Name=",name," State=",state }
   WRITE !,"FETCH status SQLCODE=",SQLCODE
   WRITE !,"Number of rows fetched=",%ROWCOUNT
   &sql(CLOSE EmpCursor)
   WRITE !,"AFTER: Name=",name," State=",state

See Also

• CLOSE, DECLARE, FETCH

• SQL Cursors in the “Using Embedded SQL” chapter of Using Caché SQL

• SQLCODE error messages listed in the Caché Error Reference