SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-46
Starting a Transaction
Starting a Transaction
Use a BEGIN WORK statement to start a transaction explicitly:
#sql {BEGIN WORK};
If you do not use the BEGIN WORK statement, SQL/MX automatically starts a
transaction for a statement, provided that an active transaction does not already exist
and that the statement supports implicit transactions. For information about implicit (or
system-defined) transactions, see the SQL/MX Reference Manual.
In a program, you might use a loop when updating or deleting rows in the result set of
an iterator. In a looping UPDATE or DELETE (either searched or positioned), 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 initializing the iterator), and issue COMMIT WORK after all changes
have been made within the loop. For more information, see the Autocommit Setting on
page 4-43 and Committing Database Changes if No Errors Occur on page 4-47.
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:
#sql {UPDATE...};
Typically, 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, should be grouped within one user-defined
transaction.
You should avoid DML operations on the same set of rows as an iterator operation in
the same transaction. The sensitivity of iterators is ASENSITIVE in SQL/MX, which
means that a concurrent DML operation in the same transaction as an iterator might or
might not affect the iterator results. See Iterators and Result Sets on page 3-48.
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 for SQL exceptions by coding a try and catch blocks. If no SQL
exceptions occur, database changes are committed. If an SQL exception occurs,