SQL/MX Queuing and Publish/Subscribe Services

Embedded SQL Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
3-8
Join With a Stream
while (1) {
/* Dequeue rowset of notifications */
EXEC SQL FETCH arrivals
INTO :arrayDestination,:arrayOrigin,
:arraySeqnbr;
/* Get number of rows fetched */
EXEC SQL GET DIAGNOSTICS :numFetched = ROW_COUNT;
printf("\nNumber Fetched: %d",numFetched);
/* Process each row fetched in some way */
for (i = 0; i < numFetched; i++) {
printf("\nDestination:%d Origin:%d "
"Sequence Number:%d",
arrayDestination[i], arrayOrigin[i],
arraySeqnbr[i]);
/* Route package and notify receiver */
routePackage(arraySeqnbr[i], arrayDestination[i],
arrayOrigin[i]);
}
EXEC SQL COMMIT WORK; /* Commit transaction */
EXEC SQL BEGIN WORK; /* Start new transaction */
}
EndOfProcessing:
EXEC SQL WHENEVER SQLERROR GOTO EndError;
EXEC SQL CLOSE arrivals;
EndError:
return SQLCODE;
}
-----------------------------------------------------------
Join With a Stream
A stream can be joined to another table, provided that the other table is not accessed
as a stream. The SQL compiler will always implement these types of joins with the
stream as the outer table and the nonstream as the inner table.
In the example, the application opens the cursor and fetches all the available rows in
the joined table. While there is a row in the inbox table that can be joined to a row in
the routes table, the fetch returns a row without blocking. When no more joined rows
are available, the fetch blocks, waiting for more rows. If a new row is inserted into the
routes table, the fetch continues to block (the routes table is not a stream).
However, if a new row is inserted into the inbox table, it will be deleted and the fetch
will return the row (assuming the new row meets the join predicate condition).
If, while fetching, the cursor encounters a row in the inbox table that does not have a
matching row in the routes table, that row will be deleted from the inbox table
without being returned to the cursor.