SQLEndTran function (CLI) - End transactions of a connection or an environment

Requests a commit or rollback operation for all active operations on all statements associated with a connection, or for all connections associated with an environment.

Specification:

  • CLI 5.0
  • ODBC 3.0
  • ISO CLI

SQLEndTran() requests a commit or rollback operation for all active operations on all statements that are associated with a connection, or for all connections that are associated with an environment.

Syntax

SQLRETURN   SQLEndTran (
               SQLSMALLINT       HandleType,       /* fHandleType */
               SQLHANDLE         Handle,           /* hHandle */
               SQLSMALLINT       CompletionType);  /* fType */

Function arguments

Table 1. SQLEndTran arguments
Data type Argument Use Description
SQLSMALLINT HandleType Input Handle type identifier. Contains either SQL_HANDLE_ENV if Handle is an environment handle, or SQL_HANDLE_DBC if Handle is a connection handle.
SQLHANDLE Handle Input The handle, of the type that is indicated by HandleType, that indicates the scope of the transaction.
SQLSMALLINT CompletionType Input One of the following two values:
  • SQL_COMMIT
  • SQL_ROLLBACK

Usage

If HandleType is SQL_HANDLE_ENV and Handle is a valid environment handle,CLI attempts to commit or roll back transactions one at a time, depending on the value of CompletionType, on all connections that are in a connected state on that environment. SQL_SUCCESS is returned only if it receives SQL_SUCCESS for each connection. If it receives SQL_ERROR on one or more connections, it 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, the application can call SQLGetDiagRec() for each connection.

You must not useSQLEndTran() when working in a Distributed Unit of Work environment. Use the transaction manager APIs instead.

If CompletionType is SQL_COMMIT, SQLEndTran() issues a commit request for all active operations on any statement that is associated with an affected connection. If CompletionType is SQL_ROLLBACK, SQLEndTran() issues a rollback request for all active operations on any statement that is associated with an affected connection. If no transactions are active, SQLEndTran() returns SQL_SUCCESS with no effect on any data sources.

To determine how transaction operations affect cursors, an application calls SQLGetInfo() with the SQL_CURSOR_ROLLBACK_BEHAVIOR and SQL_CURSOR_COMMIT_BEHAVIOR options.

If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_DELETE, SQLEndTran() closes and deletes all open cursors on all statements that are associated with the connection, and discards all pending results. SQLEndTran() leaves any statement present in an allocated (unprepared) state; the application can reuse them for subsequent SQL requests or can call SQLFreeStmt() or SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT to deallocate them.

If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_CLOSE, SQLEndTran() closes all open cursors on all statements that are associated with the connection. SQLEndTran() leaves any statement present in a prepared state; the application can call SQLExecute() for a statement that is associated with the connection without first calling SQLPrepare().

If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_PRESERVE, SQLEndTran() does not affect open cursors that are associated with the connection. Cursors remain at the row that they pointed to before the call to SQLEndTran().

When autocommit mode is off, calling SQLEndTran() with either SQL_COMMIT or SQL_ROLLBACK when no transaction is active returns SQL_SUCCESS, which indicates that there is no work to be committed or rolled back. Calling SQLEndTran() has no effect on the data source, unless errors that are not related to the transactions occur.

When autocommit mode is on, calling SQLEndTran() with a CompletionType of either SQL_COMMIT or SQL_ROLLBACK always returns SQL_SUCCESS, unless errors that are not related to the transactions occur.

When a CLI application is running in autocommit mode, the CLI driver does not pass the statement to the server.

For applications that use the ODBC driver version 3.8 or later, the SQLEnTran function can set the connection to suspended state and returns SQL_ERROR (with SQLSTATE set to HY117). You must set the SQL_ATTR_ODBC_VERSION environment attribute to SQL_OV_ODBC3_80. For more details about necessary conditions to set the connection in a suspended state, see the Microsoft MSDN documentation for the SQLEndTran() at http://msdn.microsoft.com/en-us/library/ms716544(v=vs.85).aspx.

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLEndTran SQLSTATEs
SQLSTATE Description Explanation
01000 Warning. An informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
08003 Connection is closed. The ConnectionHandle is not in a connected state.
08007 Connection failure during transaction. The connection that is associated with the ConnectionHandle failed during the execution of the function, and it cannot be determined whether the requested COMMIT or ROLLBACK occurred before the failure.
40001 Transaction rollback. The transaction is rolled back due to a resource deadlock with another transaction.
HY000 General error. An error occurred for which there is no specific SQLSTATE. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause.
HY001 Memory allocation failure. Db2® CLI is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY010 Function sequence error. An asynchronously executing function was called for a StatementHandle that is associated with the ConnectionHandle and was still executing when SQLEndTran() was called.

SQLExecute() or SQLExecDirect() was called for a StatementHandle that is associated with the ConnectionHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.

An exception to this behavior exists for CLI applications that run against a Db2 for z/OS® database server. When the connection attribute SQL_ATTR_FORCE_ROLLBACK is turned on, CLI applications can successfully perform SQLEndTran() or SQLTransact() when CompletionType is SQL_ROLLBACK. The StreamPutData configuration keyword must be set to 1 (on).
HY012 Invalid transaction code. The value that is specified for the argument CompletionType is neither SQL_COMMIT nor SQL_ROLLBACK.
HY092 Option type out of range. The value specified for the argument HandleType was neither SQL_HANDLE_ENV nor SQL_HANDLE_DBC.

Restrictions

None.

Example

  /* commit all active transactions on the connection */
  cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT)
  
  /* ... */
  
  /* rollback all active transactions on the connection */
  cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);

  /* ... */

  /* rollback all active transactions on all connections 
     in this environment */
  cliRC = SQLEndTran(SQL_HANDLE_ENV, henv, SQL_ROLLBACK);