SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-20
Transaction Control Statements
Implicit Transactions
NonStop SQL/MP supports implicit transactions in SQLCI but not in embedded
statements. NonStop SQL/MX also supports implicit transactions in MXCI and in
embedded statements. If you do not specify a BEGIN WORK statement, NonStop
SQL/MX initiates a transaction when it encounters an SQL statement that refers to an
audited table. Most DML statements initiate transactions implicitly at the start of
execution. However, NonStop SQL/MX does not initiate a transaction for specific SQL
statements, such as SELECT statements with READ UNCOMMITTED access. For
other exceptions, see the
SQL/MX Reference Manual.
Even if a transaction is initiated implicitly, you must end a transaction explicitly with the
COMMIT WORK statement or the ROLLBACK WORK statement to ensure that either
the entire SQL statement executes or none of it does.
Inherited Transactions
In NonStop SQL/MP and NonStop SQL/MX, if a transaction is started at some point
during the execution of your program (for example, in TMF or MXCS), you do not need
to explicitly start a new transaction. NonStop SQL/MX detects that a transaction exists
and inherits that transaction. If an error occurs in an inherited transaction, NonStop
SQL/MX changes the transaction state to doomed (SQLSTATE “25000”). A doomed
transaction is not aborted. However, to proceed, you must manually abort the
transaction. NonStop SQL/MX provides error messages until then.
Auto-Abort and Upserts
By default, SQL/MX Release 2.x does not automatically abort transactions following an
error in a DELETE, INSERT, UPDATE, or DDL statement. To abort a transaction
following an error, you must provide code in your application program to explicitly abort
the transaction.
You can set a CONTROL QUERY DEFAULT, UPD_ABORT_ON_ERROR, to make
SQL/MX Release 2.x behave like SQL/MX Release 1.8 and automatically abort
transactions following an error. For more information, see the
SQL/MX Reference
Manual.
In NonStop SQL/MP, you can use row-not-found warnings or row-already-exists errors
to determine whether to insert or update single rows. You can do an INSERT to find out
if the row already exists, ignore the error, and follow it with an UPDATE statement to
update the row. Or, do an UPDATE on a row to see if it exists, and when no row is
found, use an INSERT statement to insert the row. This process is called an
upsert.
In SQL/MX Release 2.x, because UPD_ABORT_ON_ERROR is OFF by default, you
can use row-not-found and row-already exists errors to perform upserts. For more
information, see the
SQL/MX Reference Manual.