SQL/MX Queuing and Publish/Subscribe Services

Embedded SQL Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
3-4
Embedded UPDATE
Embedded UPDATE
Embedded update statements enable applications to read and update rows with a
single operation. This program illustrates the embedded UPDATE operation within the
stream access mode.
Suppose that the invoices table, described in Scenario 5. The Shipping Application,
contains a column, archive, to mark whether a row has been processed:
CREATE TABLE pubs.inbox
(destination INT NOT NULL,
origin INT NOT NULL,
seqnbr INT,
priority INT,
archive CHAR(1));
---------------------------------------------------------------
#include <stdio.h>
#include <string.h>
long EmbeddedUpdate()
{
EXEC SQL BEGIN DECLARE SECTION;
int destination;
int origin;
int seqnbr;
long SQLCODE;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR GOTO EndOfProcessing;
EXEC SQL DECLARE arrivals CURSOR WITH HOLD FOR
SELECT destination, origin, seqnbr
FROM
(UPDATE STREAM(inbox) SET archive = 'Y'
WHERE archive = 'N') AS inbox;
EXEC SQL BEGIN WORK; /* Start transaction */
EXEC SQL OPEN arrivals;
/* Wait for newly arrived parcels and notify receivers */
while (1) {
/* Archive notifications */
EXEC SQL FETCH arrivals
INTO :destination,:origin,:seqnbr;
printf("\nDestination:%d Origin:%d "
"Sequence number:%d",
destination, origin, seqnbr);
/* Route package and notify receivers*/
routePackage(seqnbr, destination, origin);
EXEC SQL COMMIT WORK; /* Commit transaction */
EXEC SQL BEGIN WORK; /* Start new transaction */
}