SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-11
Holdable Cursors
EXEC SQL PREPARE :szHoldableStatementName FROM
:szHoldableStatement;
/* Extended Dynamic Cursor */
strcpy(ext_hold_stmthold, "C1");
EXEC SQL DECLARE :ext_hold_stmthold CURSOR WITH HOLD FOR
:szHoldableStatementName;
/* Allocated Cursor */
strcpy(ext_hold_stmthold, "C2");
EXEC SQL ALLOCATE :ext_hold_stmthold CURSOR WITH HOLD FOR
:szHoldableStatementName;
From Scenario 4. Fetching Contract Data (page B-8), this DECLARE CURSOR
statement specifies the cursor is to remain open and maintain its position in the result
set even though a user-specified transaction has terminated:
EXEC SQL DECLARE get_invoices CURSOR WITH HOLD FOR
SELECT amount,contractnbr
FROM
(DELETE FROM STREAM(invoices)
FOR SKIP CONFLICT ACCESS) AS invoices;
EXEC SQL BEGIN WORK; /* Start transaction */
EXEC SQL OPEN get_invoices; /* Open cursor */
/* Wait for newly arrived invoices */
while (1) {
/* Dequeue invoices */
EXEC SQL FETCH get_invoices
INTO :inv_amount, :inv_contractnbr;
...
/* Commit does not close open cursor */
EXEC SQL COMMIT WORK; /* Commit transaction */
EXEC SQL BEGIN WORK; /* Start new transaction */
}
EXEC SQL COMMIT WORK; /* Commit final transaction */
...
Because the cursor was declared WITH HOLD, it is not closed by the execution of the
COMMIT WORK statement. Only an error condition or a concurrent DDL or utility
operation can cause a cursor to close. For example, an error such as a system or disk
process failure that would cause any cursor to close, causes a holdable cursor to
close. Before using a cursor closed by an error condition, the application must reopen
the cursor.
Concurrent DDL or utility operations can have a similar effect. While the subscriber’s
transaction is active, locking can cause the DDL or utility operation to wait. When the
subscriber’s transaction ends and its locks are released, the DDL or utility operation
proceeds and the subscriber’s cursor can be closed. In this case, the next time the
subscriber uses the cursor to fetch, an error is generated. To reuse the cursor, the
application must reopen it.
For more information about concurrency, see the SQL/MX Reference Manual.