SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-4
Embedded DELETE
An application subscribes and dequeues invoices by issuing this SELECT statement:
SET NAMETYPE ANSI;
SET SCHEMA psdb.pubs;
SELECT * FROM
(DELETE FROM STREAM(invoices)) AS invoices;
CONTRACTNBR AMOUNT PRIORITY
----------- ----------- -----------
100 10500 1
200 20390 2
300 30800 3
The available rows are retrieved and deleted. The application is now in a wait state
until more rows are inserted into the table. At any point in time, while this embedded
DELETE operation is selecting and deleting rows or is in a wait state, a query issued
by another application returns no rows and eventually times out. In this way, only one
application can retrieve a given row.
However, if the transaction performed by the dequeue operation is aborted for any
reason before the commit operation, the rows are not deleted and are unlocked as part
of the termination of the transaction (and therefore become available to other
applications).
In this example, the transaction performed by the dequeue operation is committed
when the stream times out, and the rows are deleted from the queue:
CONTROL QUERY DEFAULT stream_timeout '300';
SELECT * FROM
(DELETE FROM STREAM(invoices)) AS invoices;
CONTRACTNBR AMOUNT PRIORITY
----------- ----------- -----------
100 10500 1
200 20390 2
300 30800 3
*** ERROR[8006] The stream timed out,
but the cursor is still open.
SELECT * FROM invoices;
--- 0 row(s) selected.
When the stream times out, control is returned to the user application (or to MXCI).
Because of the user interface restrictions of MXCI, MXCI users cannot use the cursor.
However, an application can perform another fetch and return data (or time out again).
Note. Embedded DELETE statements are not allowed on tables with referential integrity
constraints.