SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-4
Subscribing to Quotes
EXEC SQL FETCH get_quotes
INTO :lastsyskey, :symbol, :price;
In this example, the host variable named lastkey is maintained and saved by the
application after each fetch. It is used as a starting point whenever the cursor is
reopened.
This technique allows a subscriber to see rows that were inserted while the subscribing
application was disconnected. It takes advantage of the fact that SYSKEY values are
always assigned in increasing order. However, the technique does not allow
reconnected subscribers to see rows that were updated while the application was
disconnected, because updating a row does not change its SYSKEY value.
If an application does not need to see rows that were either inserted or updated while it
was disconnected, but simply needs to avoid rereading rows that were read before the
disconnect, you can use an AFTER LAST ROW clause.
Note that using the WITH HOLD clause does not help in a situation where you use
statements with a selection predicate when a row is UPDATED while the cursor is
closed, and the update causes the row to satisfy the predicate.
Suppose that the previous statement has an additional predicate:
WHERE SYSKEY > :lastsyskey AND PRICE > 44
A given row is skipped because it does not satisfy the predicate. Later the row is
updated to set the price to 45. A subscriber should now see this row.
If a subscriber were interrupted (say, the application terminates), and then the row
were updated, and then the subscription resumed using the predicates “where
SYSKEY > :lastsyskey AND PRICE > 44", SQL would miss the updated row.
Subscribing to a Subset of Quotes
An application can select a subset of stock quotes—for example, ABC Enterprise
quotes—by issuing this SELECT statement with a WHERE clause:
SELECT * FROM STREAM(quotes)
WHERE symbol = 'ABC'
ORDER BY SYSKEY;
Because the new rows for the ABC quotes are always inserted in the table ahead of
the scan that has taken place at any point in time, the order of retrieval of the ABC
quotes is always the order of insertion.