SQL/MX Programming Manual for Java

Sample Programs
HP NonStop SQL/MX Programming Manual for Java—523726-003
C-25
EmbeddedDelete.sqlj—Embedded DELETE
EmbeddedDelete.sqlj—Embedded DELETE
In this SQLJ program, an iterator with the holdability property dequeues rows from a
stream using an embedded DELETE, which reads and deletes rows in a single
operation. 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-7. EmbeddedDelete.sqlj—Embedded DELETE (page 1 of 2)
import java.sql.*;
#sql iterator EmbedDeleteIter with (holdability=true)
(int, int, int);
public class EmbeddedDelete
{
public static void main(String[] args)
{
int destination = 0;
int origin = 0;
int seqnbr = 0;
EmbedDeleteIter arrivals = null;
try
{
#sql { MODULE psdb.pubs.embeddeletemod };
#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))
AS inbox };
// Wait for newly arrived parcels and notify receivers
while (true)
{
try {
#sql { FETCH :arrivals
INTO :destination, :origin, :seqnbr };
}