SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-31
C Examples of COMMIT WORK
The transaction must add the order for terminals to PARTSUPP, add the supplier to
the SUPPLIER table, and update QTY_ON_HAND in PARTLOC. After the INSERT
and UPDATE statements execute successfully, you commit the transaction, as
shown, within MXCI:
-- This statement initiates a transaction.
BEGIN WORK;
--- SQL operation complete.
-- This statement inserts a new entry into PARTSUPP.
INSERT INTO invent.partsupp
VALUES (5100, 17, 800.00, 24);
--- 1 row(s) inserted.
-- This statement inserts a new entry into SUPPLIER.
INSERT INTO invent.supplier
VALUES (17, 'Super Peripherals','751 Sanborn Way',
'Santa Rosa', 'California', '95405');
--- 1 row(s) inserted.
-- This statement updates the quantity in PARTLOC.
UPDATE invent.partloc
SET qty_on_hand = qty_on_hand + 24
WHERE partnum = 5100 AND loc_code = 'G43';
--- 1 row(s) updated.
-- This statement ends a transaction.
COMMIT WORK;
--- SQL operation complete.
C Examples of COMMIT WORK
•
Begin a transaction, execute an UPDATE statement, and test SQLSTATE. If the
UPDATE is successful, the database changes are committed. Otherwise, the
database changes are rolled back.
...
char SQLSTATE_OK[6] = "00000";
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL BEGIN WORK; /* Start a transaction. */
...
EXEC SQL UPDATE ... ; /* Change the database. */
...
if (strcmp(SQLSTATE, SQLSTATE_OK) == 0)
EXEC SQL COMMIT WORK; /* Commit the changes. */
else
EXEC SQL ROLLBACK WORK; /* Roll back the changes. */