SQL/MX Queuing and Publish/Subscribe Services

Embedded SQL Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
3-6
Set Column Values on Rollback
EndOfProcessing:
EXEC SQL WHENEVER SQLERROR GOTO EndError;
EXEC SQL CLOSE arrivals;
EndError:
return SQLCODE;
}
------------------------------------------------------------
Set Column Values on Rollback
The set on rollback feature enables applications to update columns due to a rollback of
a DELETE or UPDATE operation. This program illustrates incrementing the abort count
on rollback. The application also uses a selection predicate to limit its attempts to
process the same row to no more than three attempts.
Suppose that the inbox table, described in Scenario 5. The Shipping Application,
contains a column for the abort count:
CREATE TABLE pubs.inbox
(destination INT NOT NULL,
origin INT NOT NULL,
seqnbr INT,
priority INT,
abortcount INT DEFAULT 0 NOT NULL);
The abortcount column must be declared as NOT NULL and cannot be part of a
secondary index:
---------------------------------------------------------------
#include <stdio.h>
#include <string.h>
long SetOnRollback()
{
EXEC SQL BEGIN DECLARE SECTION;
int destination;
int origin;
int seqnbr;
int abortcount;
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, abortcount
FROM (DELETE FROM STREAM(inbox) SET ON ROLLBACK
abortcount = abortcount+1 where abortcount < 3
FOR SKIP CONFLICT ACCESS) AS inbox;
EXEC SQL BEGIN WORK; /* Start transaction */
EXEC SQL OPEN arrivals;
/* Wait for newly arrived parcels and notify receivers */
while (1) {
/* Dequeue notifications */