Call Level Interface Guide and Reference

SQLTransact - Transaction Management

Status of this Function since DB2 CLI Version 5
Note:
In ODBC version 3, SQLTransact() has been deprecated and replaced with SQLEndTran(); see SQLEndTran - End Transactions of a Connection for more information.
Although this version of DB2 CLI continues to support SQLTransact(), we recommend that you begin using SQLEndTran() in your DB2 CLI programs so that they conform to the latest standards.
See DB2 CLI Functions Deprecated for Version 5 for more information on this and other deprecated functions.

Migrating to the New Function

The statement:

   SQLTransact(henv, hdbc, SQL_COMMIT);

for example, would be rewritten using the new function as:

   SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);

Purpose

Specification: DB2 CLI 1.1 ODBC 1.0 ISO CLI

SQLTransact() commits or rolls back the current transaction in the specified connection. SQLTransact() can also be used to request that a commit or rollback be issued for each of the connections associated with the environment.

All changes to the database performed on the connection since connect time or the previous call to SQLTransact() (whichever is the most recent) are committed or rolled back.

If a transaction is active on a connection, the application must call SQLTransact() before it can disconnect from the database.

Syntax

SQLRETURN   SQLTransact      (SQLHENV           EnvironmentHandle, /* henv */
                              SQLHDBC           ConnectionHandle,  /* hdbc */
                              SQLUSMALLINT      Type);             /* fType */

Function Arguments

Table 181. SQLTransact Arguments

Data Type Argument Use Description
SQLHENV EnvironmentHandle input Environment handle.
If ConnectionHandle is a valid connection handle, EnvironmentHandle is ignored.
SQLHDBC ConnectionHandle input Database connection handle.
If ConnectionHandle is set to SQL_NULL_HDBC, then EnvironmentHandle must contain the environment handle that the connection is associated with.
SQLUSMALLINT Type input The desired action for the transaction. The value for this argument must be one of:

SQL_COMMIT
SQL_ROLLBACK

Data Type	Argument	Use	Description
SQLHENV	`EnvironmentHandle`	input	Environment handle. If `ConnectionHandle` is a valid connection handle, `EnvironmentHandle` is ignored.
SQLHDBC	`ConnectionHandle`	input	Database connection handle. If `ConnectionHandle` is set to SQL_NULL_HDBC, then `EnvironmentHandle` must contain the environment handle that the connection is associated with.
SQLUSMALLINT	`Type`	input	The desired action for the transaction. The value for this argument must be one of: SQL_COMMIT SQL_ROLLBACK

Usage

In DB2 CLI, a transaction begins implicitly when an application that does not already have an active transaction, issues SQLPrepare(), SQLExecDirect(), SQLExecDirect(), SQLGetTypeInfo(), or one of the catalog functions. The transaction ends when the application calls SQLTransact().

If the input connection handle is SQL_NULL_HDBC and the environment handle is valid, then a commit or rollback will be issued on each of the open connections in the environment. SQL_SUCCESS is returned only if success is reported on all the connections. If the commit or rollback fails for one or more of the connections, SQLTransact() will return SQL_ERROR. To determine which connection(s) failed the commit or rollback operation, the application needs to call SQLError() on each connection handle in the environment.

It is important to note that unless the connection option SQL_ATTR_CONNECTTYPE has been set to SQL_COORDINATED_TRANS (to indicate coordinated distributed transactions), there is no attempt to provide coordinated global transaction with one-phase or two-phase commit protocols.

Completing a transaction has the following effects:

Prepared SQL statements (via SQLPrepare()) survive transactions; they can be executed again without first calling SQLPrepare().
Cursor positions are maintained after a commit unless one or more of the following is true:
- the server is SQL/DS
- the SQL_ATTR_CURSOR_HOLD statement attribute for this handle is set to SQL_CURSOR_HOLD_OFF.
- The CURSORHOLD keyword in the DB2 CLI initialization file is set so that cursor with hold is not in effect and this has not been overridden by resetting the SQL_ATTR_CURSOR_HOLD statement attribute.
- The CURSORHOLD keyword is present in a the connection string on the SQLDriverConnect() call that set up this connection, and it indicates cursor with hold is not in effect, and this has not been overridden by resetting the SQL_ATTR_CURSOR_HOLD statement attribute.
If the cursor position is not maintained due to any one of the above circumstances, the cursor is closed and all pending results are discarded.
If the cursor position is maintained after a commit, the application must issue a fetch to re-position the cursor (to the next row) before continuing with processing of the remaining result set.
To determine whether cursor position will be maintained after a commit, call SQLGetInfo() with the SQL_CURSOR_COMMIT_BEHAVIOR information type.
Cursors are closed after a rollback and all pending results are discarded.
Statement handles are still valid after a call to SQLTransact(), and can be reused for subsequent SQL statements or de-allocated by calling SQLFreeStmt().
Cursor names, bound parameters, and column bindings survive transactions.

If no transaction is currently active on the connection, calling SQLTransact() has no effect on the database server and returns SQL_SUCCESS.

SQLTransact() may fail while executing the COMMIT or ROLLBACK due to a loss of connection. In this case the application may be unable to determine whether the COMMIT or ROLLBACK has been processed, and a database administrator's help may be required. Refer to the DBMS product information for more information on transaction logs and other transaction management tasks.

Return Codes

SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 182. SQLTransact SQLSTATEs

SQLSTATE Description Explanation
08003 Connection is closed. The ConnectionHandle was not in a connected state.
08007 Connection failure during transaction. The connection associated with the ConnectionHandle failed during the execution of the function during the execution of the function and it cannot be determined whether the requested COMMIT or ROLLBACK occurred before the failure.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY012 Invalid transaction code. The value specified for the argument Type was neither SQL_COMMIT not SQL_ROLLBACK.
HY013 Unexpected memory handling error. DB2 CLI was unable to access memory required to support execution or completion of the function.

SQLSTATE	Description	Explanation
08003	Connection is closed.	The `ConnectionHandle` was not in a connected state.
08007	Connection failure during transaction.	The connection associated with the `ConnectionHandle` failed during the execution of the function during the execution of the function and it cannot be determined whether the requested COMMIT or ROLLBACK occurred before the failure.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY012	Invalid transaction code.	The value specified for the argument `Type` was neither SQL_COMMIT not SQL_ROLLBACK.
HY013	Unexpected memory handling error.	DB2 CLI was unable to access memory required to support execution or completion of the function.

Restrictions

None.

Example

Refer to SQLEndTran - End Transactions of a Connection.

References

[ Top of Page | Previous Page | Next Page ]