CLOSE (SQL)

Closes a cursor.

Synopsis

CLOSE cursor-name

Description

A CLOSE statement shuts down an open cursor. It releases the current result set and frees any cursor locks held on the rows on which the cursor is positioned. However, CLOSE does not delete the cursor; it leaves the data structures accessible for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. This behavior is demonstrated by the following command sequences:

DECLARE c1, OPEN c1, FETCH c1, CLOSE c1 is the standard sequence.
DECLARE c1, OPEN c1, CLOSE c1, OPEN c1 reopens the declared cursor c1.
DECLARE c1, OPEN c1, CLOSE c1, DECLARE c1, OPEN c1 reopens the cursor specified in the first DECLARE, the second DECLARE is ignored.
DECLARE c1, OPEN c1, FETCH c1, CLOSE c1, OPEN c1, FETCH c1 cause both fetch operations to retrieve the same record.

CLOSE must be issued on an open cursor. Issuing a CLOSE on a cursor that has only been declared (but not opened), or on a cursor that has already been closed results in an SQLCODE -102 error. Issuing a CLOSE on a non-existent cursor — for example, a cursor that differs from the defined cursor in letter case — results in an SQLCODE -52 error.

The cursor-name is not namespace-specific. Changing the current namespace has no effect on use of a declared cursor. The only namespace consideration is that FETCH must occur in the namespace that contains the table(s) being queried.

Note that, as an SQL statement, CLOSE is only supported from Embedded SQL. Equivalent operations are supported through ODBC using the ODBC API.

Arguments

cursor-name

The name of the cursor to be closed. The cursor name was specified in the DECLARE statement. Cursor names are case-sensitive.

Examples

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.Employee
        WHERE Home_State %STARTSWITH 'A')
   WRITE !,"BEFORE: Name=",name," State=",state 
   &sql(OPEN EmpCursor)
     IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   NEW %ROWCOUNT,%ROWID
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"DURING: Name=",name," State=",state }
   WRITE !,"After FETCH SQLCODE: ",SQLCODE
   WRITE !,"After FETCH row count: ",%ROWCOUNT
   &sql(CLOSE EmpCursor)
      IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT} 
   WRITE !,"After CLOSE SQLCODE: ",SQLCODE
   WRITE !,"After CLOSE row count: ",%ROWCOUNT
   WRITE !,"AFTER: Name=",name," State=",state

Note that after closing the cursor, the host variables remain set to the last fetched data values, and %ROWCOUNT remains set to the number of rows retrieved. However, the SQLCODE value at the end of the fetch (SQLCODE=100) is overwritten by the SQLCODE value for the CLOSE (SQLCODE=0).

The following Embedded SQL example shows that a cursor persists across namespaces. This cursor is declared in %SYS, opened and fetched in USER, and closed in SAMPLES. Note that the OPEN must be executed in the namespace that contains the table(s) being queried, and the FETCH must able to access the output host variables, which are namespace-specific:

    &sql(USE DATABASE %SYS)
    WRITE $ZNSPACE,!
  &sql(DECLARE NSCursor CURSOR FOR SELECT Name INTO :name FROM Sample.Employee)
    &sql(USE DATABASE "USER")
    WRITE $ZNSPACE,!
  &sql(OPEN NSCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
       NEW SQLCODE,%ROWCOUNT,%ROWID
  FOR { &sql(FETCH NSCursor)
       QUIT:SQLCODE  
       WRITE "Name=",name,! }
    &sql(USE DATABASE SAMPLES)
    WRITE $ZNSPACE,!
  &sql(CLOSE NSCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}