SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-10
Holdable Cursors
Another SELECT statement is issued to dequeue the invoices:
SELECT * FROM
(DELETE FROM STREAM(invoices)
SET ON ROLLBACK abortcount = abortcount + 1
FOR SKIP CONFLICT ACCESS) AS invoices;
CONTRACTNBR AMOUNT PRIORITY ABORTCOUNT
----------- ----------- ----------- ----------
100 10500 1 1
200 20390 2 1
300 30800 3 0
The first two rows are still in the queue and show the abort count has been
incremented. Note that at some point, an application could subscribe to the invoices
table and test the abort count. An entry whose abort count exceeds some specified
level could then be entered as an exception event.
For an example of using set on rollback for the cursor specification in an application,
see Set Column Values on Rollback on page 3-6.
Holdable Cursors
A holdable cursor enables an application to retain an open cursor across transactions.
The cursor maintains its position in the result set of the cursor specification. This
feature is supported only for cursors using the stream access mode or for cursors
defined with a table reference that uses an embedded UPDATE or DELETE.
The WITH HOLD clause can also be used with dynamic cursors, extended dynamic
cursors, and allocated cursors as shown:
EXEC SQL BEGIN DECLARE SECTION;
int nID;
char szHoldableStatement[255];
char szHoldableStatementName[40];
char ext_hold_stmthold[40];
EXEC SQL END DECLARE SECTION;
strcpy(
szHoldableStatement,
"SELECT amount,contractnbr FROM "
"(DELETE FROM STREAM(invoices) "
" FOR SKIP CONFLICT ACCESS) AS invoices;");
/* Prepare a statement to use with the dynamic cursor: */
EXEC SQL PREPARE not_ext_holdable FROM :szHoldableStatement;
/* A dynamic cursor: */
EXEC SQL DECLARE notext_hold_stmthold CURSOR WITH HOLD FOR
not_ext_holdable;
/* Prepare a statement to use with the extended dynamic
cursor and allocated cursor: */