SQLTransact - Commit or roll back a transaction

SQLTransact() commits or rolls back the current transaction in the connection.

All changes to the database that have been made 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 be disconnected from the database.

Syntax

SQLRETURN SQLTransact (SQLHENV        henv,
                       SQLHDBC        hdbc,
                       SQLSMALLINT    fType);

Function arguments

Table 1. SQLTransact arguments
Data type	Argument	Use	Description
SQLHENV	henv	Input	Environment handle. If hdbc is a valid connection handle, henv is ignored.
SQLHDBC	hdbc	Input	Database connection handle. If hdbc is set to SQL_NULL_HDBC, then henv must contain the environment handle that the connection is associated with.
SQLSMALLINT	fType	Input	The wanted action for the transaction. The value for this argument must be one of: SQL_COMMIT SQL_ROLLBACK SQL_COMMIT_HOLD SQL_ROLLBACK_HOLD

Usage

Completing a transaction with SQL_COMMIT or SQL_ROLLBACK has the following effects:

Statement handles are still valid after a call to SQLTransact().
Cursor names, bound parameters, and column bindings survive transactions.
Open cursors are closed, and any result sets that are pending retrieval are discarded.

Completing the transaction with SQL_COMMIT_HOLD or SQL_ROLLBACK_HOLD still commits or rolls back the database changes, but does not cause cursors to be closed.

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

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

Return codes

SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLTransact SQLSTATEs
SQLSTATE	Description	Explanation
08003	Connection not open	The hdbc is not in a connected state.
08007	Connection failure during transaction	The connection associated with the hdbc fails during the processing of the function during the processing of the function and it cannot be determined whether the requested COMMIT or ROLLBACK occurs before the failure.
58004	System error	Unrecoverable system error.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY012	Transaction operation state that is not valid	The value specified for the argument fType is neither SQL_COMMIT nor SQL_ROLLBACK.
HY013 *	Memory management problem	The driver is unable to access memory required to support the processing or completion of the function.

Example

Refer to the example in SQLFetch - Fetch next row