SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-27
Considerations—CLOSE
cursor
is the name of an open cursor to close.
:cursor-variable
is a host variable of type CHAR or VARCHAR that stores the name of an open
cursor to close.
Considerations—CLOSE

There are no authorization requirements for closing a cursor.

Closing a cursor defined with REPEATABLE access does not affect locks. Locks
on audited tables are released when the TMF transaction finishes or aborts; locks
on nonaudited tables must be released with UNLOCK TABLE.
Closing a cursor defined with STABLE access for an audited table releases the row
lock acquired on the last FETCH only for a row that was not updated or deleted
using the cursor; locks on rows that were updated or deleted are not released until
the TMF transaction ends.
Closing a cursor defined with STABLE access for a nonaudited table releases the
row lock acquired on the last FETCH.

COMMIT WORK automatically closes cursors that reference audited tables. You
can use CLOSE only on a cursor for an audited table using STABLE or
REPEATABLE access within the same TMF transaction as the last FETCH on the
cursor. (If no FETCH statements execute after the cursor is opened, you can use
CLOSE on the cursor in any transaction or outside of a transaction.)

If your program is a server and the TMF transaction was started in a requester, the
program must close cursors to release space used by the cursors and to free locks
before returning control to the requester.

You can use the FREE RESOURCES statement instead of the CLOSE statement
to close all open cursors. After the cursor is closed, you must reopen the cursor
before referring to it in any statement.
Example—CLOSE
This program fragment declares and opens a cursor, uses FETCH to retrieve data,
closes the cursor:
EXEC SQL DECLARE CURSOR1 CURSOR FOR
SELECT COL1, COL2, COL3 FROM =PARTS
WHERE COL1 >= :HOSTVAR1 ORDER BY COL1 BROWSE ACCESS;
EXEC SQL OPEN CURSOR1;
EXEC SQL FETCH CURSOR1 INTO :HOSTVAR1, :HOSTVAR2, :HOSTVAR3;
EXEC SQL CLOSE CURSOR1;