SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-6
Embedded UPDATE
From Scenario 2. The Invoices Queue (page B-5), suppose that the invoices table
contains a column for archiving:
CREATE TABLE pubs.invoices
(contractnbr INT,
amount INT,
priority INT,
archive CHAR(1) DEFAULT 'N');
This SELECT statement is similar to the embedded DELETE example but instead uses
stream access with an embedded UPDATE operation:
SET NAMETYPE ANSI;
SET SCHEMA psdb.pubs;
SELECT * FROM
(UPDATE STREAM(invoices) SET archive = 'Y'
WHERE archive = 'N') AS invoices;
CONTRACTNBR AMOUNT PRIORITY ARCHIVE
----------- ----------- ----------- -------
100 10500 1 Y
200 20390 2 Y
300 30800 3 Y
The available rows are retrieved and marked for archiving. The application is now in a
wait state until more rows are inserted into the table.
Another application enqueues an invoice by issuing this INSERT statement:
INSERT INTO invoices (contractnbr, amount, priority)
VALUES (500, 55000, 5);
The first application dequeues the new invoice and then continues its wait state:
CONTRACTNBR AMOUNT PRIORITY ARCHIVE
----------- ----------- ----------- -------
100 10500 1 Y
200 20390 2 Y
300 30800 3 Y
500 55000 5 Y
This dequeue operation is implemented by using the stream access mode with an
embedded UPDATE and marking an entry as dequeued (or archived). For an example
of using an embedded UPDATE operation for the cursor specification in an application,
see Embedded UPDATE on page 3-4.
Note. Embedded UPDATE statements are not allowed on tables with referential integrity
constraints.