SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-3
Subscribing to Quotes
Suppose that the quotes table is defined as:
CREATE TABLE pubs.quotes
(symbol CHAR(5), price INT);
For this table, the SYSKEY is the clustering key; there is no user-defined clustering
key.
Subscribing to Quotes
An application can subscribe to stock quotes by issuing a SELECT statement that uses
the stream access mode:
SELECT * FROM STREAM(quotes);
The stream access mode causes all available rows to be retrieved by doing a regular
scan of the table. Then, if all available rows have been retrieved, the stream access
mode causes fetch operations to wait (block) instead of returning the end-of-data
condition. The fetch operation resumes when new rows become available.
For further detail, see Stream Access on page 2-2. For this scenario, the stream
access mode can be refined in these ways.
Subscribing to Quotes in the Order Inserted
Because the SYSKEY is the clustering key, an application can receive quotes in the
order inserted by issuing this SELECT statement:
SELECT * FROM STREAM(quotes)
ORDER BY SYSKEY;
Because the new rows are always inserted in the table (with a consistently increasing
SYSKEY) ahead of the scan that has taken place at any point in time, the order of
retrieval is always the order of insertion.
An application must specify an ORDER BY clause if ordered retrieval is required. If not
specified, SQL/MX does not guarantee retrieval of entries in their storage order.
Resuming After a Disconnect
An embedded SQL application can resume after a disconnect and reposition its cursor
immediately after the row last fetched by issuing these statements:
/* Declare cursor */
EXEC SQL DECLARE get_quotes CURSOR WITH HOLD FOR
SELECT SYSKEY, * FROM STREAM(quotes)
WHERE SYSKEY > :lastsyskey
ORDER BY SYSKEY;
...
EXEC SQL OPEN get_quotes;
...
/* Fetch quotes */