`SQLEndTran()` - End transaction of a connection

SQLEndTran() requests a commit or rollback operation for all active transactions on all statements that are associated with a connection. SQLEndTran() can also request that a commit or rollback operation be performed for all connections that are associated with an environment.

ODBC specifications for `SQLEndTran()`

Table 1. `SQLEndTran()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
3.0	Yes	Yes

Syntax

SQLRETURN  SQLEndTran (SQLSMALLINT       HandleType,
                       SQLHANDLE         Handle,
                       SQLSMALLINT       CompletionType);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLEndTran()` arguments
Data type	Argument	Use	Description
SQLSMALLINT	HandleType	input	Identifies the handle type. Contains either SQL_HANDLE_ENV if Handle is an environment handle or SQL_HANDLE_DBC if Handle is a connection handle.
SQLHANDLE	Handle	input	Specifies the handle, of the type indicated by HandleType, that indicates the scope of the transaction. See Usage for more information.
SQLSMALLINT	CompletionType	input	Specifies whether to perform a commit or a rollback. Use one of the following values: SQL_COMMIT SQL_ROLLBACK

Usage

A new transaction is implicitly started when an SQL statement that can be contained within a transaction is executed against the current data source. The application might need to commit or roll back based on execution status.

If you set the HandleType argument to SQL_HANDLE_ENV and set the Handle argument to a valid environment handle, Db2 ODBC attempts to commit or roll back transactions one at a time on all connections that are in a connected state. Transactions are committed or rolled back depending on the value of the CompletionType argument.

If you set the CompletionType argument to SQL_COMMIT, SQLEndTran() issues a commit request for all statements on the connection. If CompletionType is SQL_ROLLBACK, SQLEndTran() issues a rollback request for all statements on the connection.

SQLEndTran() returns SQL_SUCCESS if it receives SQL_SUCCESS for each connection. If it receives SQL_ERROR on one or more connections, SQLEndTran() returns SQL_ERROR to the application, and the diagnostic information is placed in the diagnostic data structure of the environment. To determine which connections failed during the commit or rollback operation, call SQLGetDiagRec() for each connection.

Important: You must set the connection attribute SQL_ATTR_CONNECTTYPE to SQL_COORDINATED_TRANS (to indicate coordinated distributed transactions), for Db2 ODBC to provide coordinated global transactions with one-phase or two-phase commit protocols is made.

Completing a transaction has the following effects:

Prepared SQL statements (which SQLPrepare() creates) 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 conditions are true:
- The server is Db2 Server for VSE and VM.
- The SQL_ATTR_CURSOR_HOLD statement attribute for this handle is set to SQL_CURSOR_HOLD_OFF.
- The CURSORHOLD keyword in the Db2 ODBC initialization file is set so that cursor with hold is not in effect and this setting has not been overridden by resetting the SQL_ATTR_CURSOR_HOLD statement attribute.
- The CURSORHOLD keyword is present in the SQLDriverConnect() connection string specifying that cursor-with-hold behavior is not in effect. Also you must not override this setting 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 fetch to reposition the cursor (to the next row) before continuing to process the remaining result set.

To determine how transaction operations affect cursors, call SQLGetInfo() with the SQL_CURSOR_ROLLBACK_BEHAVIOR and SQL_CURSOR_COMMIT_BEHAVIOR attributes.
Cursors are closed after a rollback, and all pending results are discarded.
Statement handles are still valid after a call to SQLEndTran(), and they can be reused for subsequent SQL statements or deallocated by calling SQLFreeStmt() or SQLFreeHandle() with HandleType set to SQL_HANDLE_STMT.
Cursor names, bound parameters, and column bindings survive transactions.

Regardless of whether Db2 ODBC is in autocommit mode or manual-commit mode, SQLEndTran() always sends the request to the database for execution.

Return codes

After you call SQLGetDiagRec(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. `SQLEndTran()` SQLSTATEs
SQLSTATE	Description	Explanation
01000	Warning.	An informational message was generated. (`SQLEndTran()` returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
08003	Connection is closed.	The connection handle is not in a connected state.
08007	Connection failure during transaction.	The connection that is associated with the Handle argument failed during the execution of the function. No indication of whether the requested commit or rollback occurred before the failure is issued.
40001	Transaction rollback.	The transaction is rolled back due to a resource deadlock with another transaction.
HY000	General error.	An error occurred for which no specific SQLSTATE exists. The error message that is returned by `SQLGetDiagRec()` in the buffer that the MessageText argument specifies, describes the error and its cause.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the memory that is required to support the execution or completion of the function.
HY010	Function sequence error.	`SQLExecute()` or `SQLExecDirect()` is called for the statement handle and return SQL_NEED_DATA. This function is called before data was sent for all data-at-execution parameters or columns. Invoke `SQLCancel()` to cancel the data-at-execution condition.
HY012	Invalid transaction code.	The specified value for the CompletionType argument was neither SQL_COMMIT nor SQL_ROLLBACK.
HY092	Option type out of range.	The specified value for the HandleType argument was neither SQL_HANDLE_ENV nor SQL_HANDLE_DBC.

Restrictions

SQLEndTran() cannot be used if the ODBC application is executing as a stored procedure.

Example

Refer to the DSN8P3VP sample application or online in the DSN1210.SDSNSAMP data set

SQLEndTran() - End transaction of a connection

ODBC specifications for SQLEndTran()