SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-19
SET TRANSACTION Statement
Even if a transaction is initiated implicitly, you must end a transaction explicitly with the
COMMIT WORK statement or the ROLLBACK WORK statement so that either the
entire SQL statement executes or none of it is does.
Auto-Abort
NonStop SQL/MX Release 1.8 automatically aborted transactions if an error occurred
while performing a SQL DELETE, INSERT, UPDATE, or DDL statement. By default,
NonStop SQL/MX Release 2.0 does not automatically abort transactions following an
error. The option to abort the transaction following an error must be determined by the
application program.
You can set a CONTROL QUERY DEFAULT, UPD_ABORT_ON_ERROR, to make
SQL/MX Release 2.0 behave like SQL/MX Release 1.8 and automatically abort
transactions following an error. See the description of this control query default in the
SQL/MX Reference Manual for details.
SQL/MP Behavior
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 sometimes called
an “UPSERT.”
SQL/MX Release 2.0 enables you to use row-not-found and row-already exists errors
the same way, by leaving the CONTROL QUERY DEFAULT
UPD_ABORT_ON_ERROR set to OFF, the default. See the description of this control
query default in the SQL/MX Reference Manual for details.
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 SQL transaction. SQL 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.
SET TRANSACTION Statement
In NonStop SQL/MX, to automatically end a transaction after a statement successfully
executes, use the AUTOCOMMIT option within a SET TRANSACTION statement in
both MXCI and embedded SQL. This option specifies whether or not NonStop SQL/MX
commits at the end of statement execution and applies to any statement for which SQL
initiates a transaction (implicit transaction). This option does not apply to transactions
you start with the BEGIN WORK statement or transactions started by services, such as