SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-20
SET TRANSACTION Statement
TMF or MXCS, or inherited from other processes. NonStop SQL/MP provides a similar
SQLCI command, called AUTOWORK.
See Cursor Operations (Embedded SQL) on page 3-21 for information about using the
AUTOCOMMIT option with cursors.
The next statement sets the AUTOCOMMIT option to ON:
SET TRANSACTION AUTOCOMMIT ON;
If this option is set to ON, at the end of each statement execution, NonStop SQL/MX
automatically commits changes made to the database. AUTOCOMMIT is set to ON by
default at the start of an MXCI session.
If this option is set to OFF, the current transaction remains active until the end of the
MXCI session or, for embedded SQL, the end of program execution, at which time the
transaction is aborted—unless you explicitly commit or roll back the transaction. The
default is OFF for embedded SQL.
You can use the MXCI command ENV to check the state of the transaction.
AUTOCOMMIT Example
In the next example, the SET TRANSACTION statement sets AUTOCOMMIT to ON.
The first INSERT statement starts an implicit transaction. When the INSERT finishes
executing, the transaction, consisting of one inserted row, is committed. The next
INSERT statement occurs, and after it finishes, its transaction is committed. You start a
user-defined (explicit) transaction with the BEGIN WORK statement. A series of
UPDATE statements are contained in this user-defined transaction, and after the
COMMIT WORK statement is issued, the updates are committed. Remember that the
AUTOCOMMIT option is still set to ON, so that when the next DML statement is
initiated, an implicit transaction is started, and when the statement successfully
finishes, the transaction is committed.
SET TRANSACTION AUTOCOMMIT ON;
INSERT ...;
INSERT ...;
--Implicit transaction started
--Row committed
--Implicit transaction started
--Row committed
--AUTOCOMMIT STILL ON