SQL/MX Programming Manual for Java

Sample Programs
HP NonStop SQL/MX Programming Manual for Java—523726-003
C-31
SetOnRollback.sqlj—Setting Column Values on
Rollback
SetOnRollback.sqlj—Setting Column Values
on Rollback
In this SQLJ program, an iterator with the holdability property dequeues rows from a
stream using an embedded DELETE. The program also uses a stream timeout setting
of five seconds.
The SET ON ROLLBACK clause causes the program to update a column on the
rollback of the delete operation (that is, it marks rows that have been unsuccessfully
dequeued). The same column is used to prevent the iterator from selecting rows that
have already experienced three or more rollbacks.
For information about publish/subscribe functionality, see the SQL/MX Queuing and
Publish/Subscribe Services manual.
This SQLJ program uses tables in the shipping database. To install this database, see
the SQL/MX Quick Start.
SQLJ Source File
Example C-9. SetOnRollback.sqlj—Setting Column Values on
Rollback (page1of2)
import java.sql.*;
#sql iterator SetOnRollbackIter with (holdability=true)
(int, int, int);
public class SetOnRollback
{
public static void main(String[] args)
{
int destination = 0;
int origin = 0;
int seqnbr = 0;
SetOnRollbackIter arrivals = null;
try
{
#sql { MODULE psdb.pubs.setonrollbackmod };
#sql { BEGIN WORK }; /* Start a transaction */
// Set stream timeout to 5 seconds (5 hundredths
// of a second)
#sql { CONTROL QUERY DEFAULT STREAM_TIMEOUT '500' };
// Use an iterator to dequeue parcels from a stream
#sql arrivals = { SELECT destination, origin, seqnbr
FROM (DELETE FROM STREAM(psdb.pubs.inbox)
SET ON ROLLBACK ABORTCOUNT = ABORTCOUNT + 1
WHERE ABORTCOUNT < 3)
AS inbox };