SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Embedded-Only SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
3-12
C Examples of CLOSE
Reusing a Cursor
After CLOSE executes, the result table for the cursor (the output that results from the
execution of the SELECT that specifies the cursor) no longer exists. To use the same
cursor again, you must reopen it with an OPEN statement.
Effect on Locks
Closing a cursor does not affect locks. Locks on audited tables are released when the
containing transaction completes or aborts; locks on nonaudited tables must be
released with UNLOCK TABLE.
Using Extended Dynamic Cursors
The name of an extended dynamic cursor is not known until run time. When CLOSE
executes, the name must identify an open cursor within the same scope.
C Examples of CLOSE
•
Declare and open a cursor, fetch a row of retrieved data, and then close the cursor.
Note that in an actual program you would include processing the data in the host
variables hostvar1, hostvar2, and hostvar3, and looping back to fetch the
next row provided by the cursor.
...
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT COL1, COL2, COL3 FROM SALES.PARTS
WHERE COL1 >= :hostvar1
ORDER BY COL1
READ UNCOMMITTED ACCESS;
... /* Initialize value of hostvar1 */
EXEC SQL OPEN cursor1;
...
EXEC SQL FETCH cursor1 INTO :hostvar1, :hostvar2, :hostvar3;
...
EXEC SQL CLOSE cursor1;
•
This example uses extended cursor and statement names in the PREPARE,
ALLOCATE CURSOR, OPEN, and CLOSE statements.
...
scanf("%s", in_curspec);
...
EXEC SQL PREPARE :curspec FROM :in_curspec;
...
EXEC SQL ALLOCATE :extcur CURSOR FOR :curspec;
...
EXEC SQL OPEN :extcur;
/* Process using the extended dynamic cursor. */
...
EXEC SQL CLOSE :extcur;