| Specification: | DB2 CLI 5.0 | ODBC 3.0 | ISO CLI |
SQLEndTran() requests a commit or rollback operation for all active operations on all statements associated with a connection. SQLEndTran() can also request that a commit or rollback operation be performed for all connections associated with an environment.
Syntax
SQLRETURN SQLEndTran (SQLSMALLINT HandleType,
SQLHANDLE Handle,
SQLSMALLINT CompletionType);
Function Arguments
Table 54. 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 indicated by HandleType, indicating the scope of the transaction. See the "Usage" section below for more information. |
| SQLSMALLINT | CompletionType | input | One of the following two values:
|
Usage
If HandleType is SQL_HANDLE_ENV and Handle is a valid environment handle, then DB2 CLI will attempt 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 will only be returned if it receives SQL_SUCCESS for each connection. If it receives SQL_ERROR on one or more connections, it will return SQL_ERROR to the application, and the diagnostic information will be placed in the diagnostic data structure of the environment. To determine which connection(s) failed during the commit or rollback operation, the application can call SQLGetDiagRec() for each connection.
SQLEndTran() should not be used when working in a Distributed Unit of Work environment. The transaction manager APIs should be used instead.
If CompletionType is SQL_COMMIT, SQLEndTran() issues a commit request for all active operations on any statement associated with an affected connection. If CompletionType is SQL_ROLLBACK, SQLEndTran() issues a rollback request for all active operations on any statement associated with an affected connection. If no transactions are active, SQLEndTran() returns SQL_SUCCESS with no effect on any data sources.
If DB2 CLI is in manual-commit mode (by calling SQLSetConnectAttr() with the SQL_ATTR_AUTOCOMMIT attribute set to SQL_AUTOCOMMIT_OFF), a new transaction is implicitly started when an SQL statement that can be contained within a transaction is executed against the current data source.
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 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 associated with the connection. SQLEndTran() leaves any statement present in a prepared state; the application can call SQLExecute() for a statement 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 associated with the connection. Cursors remain at the row they pointed to prior to the call to SQLEndTran().
When autocommit mode is on, calling SQLEndTran() with either SQL_COMMIT or SQL_ROLLBACK when no transaction is active will return SQL_SUCCESS (indicating that there is no work to be committed or rolled back) and have no effect on the data source.
When autocommit mode is off, calling SQLEndTran() with a CompletionType of either SQL_COMMIT or SQL_ROLLBACK always returns SQL_SUCCESS.
When a DB2 CLI application is running in autocommit mode, the DB2 CLI driver does not pass the SQLEndTran() statement to the server.
Return Codes
Diagnostics
Table 55. SQLEndTran SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 01000 | Warning. | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 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 and it cannot be determined whether the requested COMMIT or ROLLBACK occurred before the failure. |
| 40001 | Transaction rollback. | The transaction was rolled back due to a resource deadlock with another transaction. |
| HY000 | General error. | An error occurred for which there was 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 was unable to allocate memory required to support execution or completion of the function. |
| HY010 | Function sequence error. | An asynchronously executing function was called for a
StatementHandle associated with the ConnectionHandle and was
still executing when SQLEndTran() was called.
SQLExecute() or SQLExecDirect() was called for a StatementHandle 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. |
| HY012 | Invalid transaction code. | The value specified for the argument CompletionType was 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.
(The complete sample utilcli.c is also available here .)
/* From the CLI sample utilcli.c */
/* ... */
printf( "\nRolling back the transaction nb. %d ...\n", db_nb + 1) ;
sqlrc = SQLEndTran( SQL_HANDLE_DBC, a_hdbc[db_nb], SQL_ROLLBACK );
rc = HandleInfoPrint( SQL_HANDLE_DBC, a_hdbc[db_nb],
sqlrc, __LINE__, __FILE__);
if( rc == 0)
{ printf( "The transaction nb. %d rolled back.\n", db_nb + 1 ) ;
}
References