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

Transaction Management
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
14-7
Processing Database Changes
In a program, you might use a loop when updating or deleting rows in the result set of
a cursor. In a looping UPDATE or DELETE (either searched or positioned), NonStop
SQL/MX commits changes as they occur within the loop when autocommit is on. To
ensure database consistency when autocommit is on, issue BEGIN WORK before the
loop starts (or before declaring the cursor) and issue COMMIT WORK after all changes
have been made within the loop. For more information, see the Autocommit Setting on
page 14-4 and Committing Database Changes if No Errors Occur on page 14-8.
For the syntax of the BEGIN WORK statement, see the SQL/MX Reference Manual.
Processing Database Changes
A transaction typically consists of a sequence of SQL statements that change the
database. For example, a transaction might include INSERT, DELETE, or UPDATE
statements:
EXEC SQL UPDATE ... ;
Typically, the SQL statements within a single transaction are dependent on each other.
For example, suppose that you want to change a job code in your database. You might
insert the new job code in the JOB table, update the job code in the EMPLOYEE table,
and finally delete the old job code in the JOB table. These operations are logically
dependent on one another and, therefore, you should group them within one user-
defined transaction.
You should avoid certain DML operations on the same set of rows as a cursor
operation in the same transaction. The sensitivity of cursors is ASENSITIVE in
NonStop SQL/MX, which means that a concurrent DML operation in the same
transaction as a cursor might or might not affect the cursor results. For information on
the DML operations to avoid, see Cursor Sensitivity on page 6-16.
If you include a CALL statement within a transaction, be aware that the stored
procedure in Java (SPJ) invoked by the CALL statement inherits the transaction from
the caller, and the SPJ method cannot contain transaction control statements. For
more information, see the SQL/MX Guide to Stored Procedures in Java.
Testing for Errors
You can test SQLSTATE for a return value of 00000 (successful completion).
Examples
if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) ...
IF sqlstate = sqlstate-ok ...
Note. By default, autocommit is off in C/C++/COBOL programs.
C
COBOL