SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Simple and Compound Statements
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
5-14
Compound Statements
For complete syntax, see the Compound (BEGIN ... END) statement in the SQL/MX
Reference Manual.
Although you cannot use cursors in compound statements, you can use rowsets to
retrieve multiple rows from database tables. You cannot embed C/C++ or COBOL
commands within a compound statement.
You cannot use a compound statement within trigger actions. The INSERT, UPDATE,
and DELETE statements cannot be trigger events when they are used in a compound
statement.
Example
Group three statements—two INSERT statements and an UPDATE statement—that
update the database within a single transaction. If an error occurs within the compound
statement, program control continues following the compound statement, and the
application issues a rollback to undo the effects of the other statements:
EXEC SQL WHENEVER SQLERROR GOTO end_compound;
EXEC SQL BEGIN WORK;
EXEC SQL BEGIN
INSERT INTO sales.orders VALUES (:ordernum, DATE '1998-03-23',
DATE '1998-03-30', 75, 7654);
INSERT INTO sales.odetail VALUES (:ordernum, :partnum,
:price, :qty);
UPDATE invent.partloc SET QTY_ON_HAND = QTY_ON_HAND - :qty
WHERE PARTNUM = :partnum AND LOC_CODE = 'G45';
END;
end_compound:
if (strcmp(SQLSTATE, SQLSTATE_OK) == 0)
EXEC SQL COMMIT WORK; /* Commit the changes */
else
EXEC SQL ROLLBACK WORK; /* Roll back the changes */
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SQLSTATE PIC X(5).
01 SQLSTATE-OK PIC X(5) VALUE "00000".
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
01-start-compound.
EXEC SQL WHENEVER SQLERROR GO TO 09-end-compound END-EXEC.
EXEC SQL BEGIN WORK END-EXEC.
EXEC SQL BEGIN
INSERT INTO sales.orders VALUES (:ordernum, DATE '1998-03-23',
DATE '1998-03-30', 75, 7654);
INSERT INTO sales.odetail VALUES (:ordernum, :partnum,
:price, :qty);
UPDATE invent.partloc SET QTY_ON_HAND = QTY_ON_HAND - :qty
WHERE PARTNUM = :partnum AND LOC_CODE = 'G45';
END END-EXEC.
C
COBOL