SQL/MX Programming Manual for Java

Sample Programs
HP NonStop SQL/MX Programming Manual for Java—523726-003
C-28
EmbeddedUpdate.sqlj—Embedded UPDATE
EmbeddedUpdate.sqlj—Embedded UPDATE
In this SQLJ program, an iterator with the holdability property dequeues rows from a
stream using an embedded UPDATE, which reads and updates rows in a single
operation. Note that rows with the archive column set to ‘N’ are dequeued by setting
the archive column to ‘Y’. The program also uses a stream timeout setting of five
seconds.
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-8. EmbeddedUpdate.sqlj—Embedded UPDATE (page1of2)
import java.sql.*;
#sql iterator EmbedUpdateIter with (holdability=true)
(int, int, int);
public class EmbeddedUpdate
{
public static void main(String[] args)
{
int destination = 0;
int origin = 0;
int seqnbr = 0;
EmbedUpdateIter arrivals = null;
try
{
#sql { MODULE psdb.pubs.embedupdatemod };
#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 (UPDATE STREAM(psdb.pubs.inbox)
SET archive = 'Y' WHERE archive= 'N')
AS inbox };
// Wait for newly arrived parcels and notify receivers
while (true)
{
try {
#sql { FETCH :arrivals
INTO :destination, :origin, :seqnbr };
}