FirebirdSQL logo
 Connecting to Firebird from ApplicationsFirebird Events 
Named Transactions and Transaction Workspaces

The construct DECLARE TRANSACTION …​ NAME transaction-name [USING namedUniqueWorkspace] allows explicit transactions to be configured and saved into the global environment in preparation for repeated use for any connection request or by any active connection.An instance of the saved transaction can be called into action by a specific form of the SET TRANSACTION command:

For a connection request:

SET TRANSACTION NAME MyReadTransaction

or

SET TRANSACTION NAME MyReadTransaction USING MyDsnDb1

for separate requests within a single active connection:

SET TRANSACTION LOCAL NAME MyReadTransaction

or

SET TRANSACTION LOCAL NAME MyReadTransaction USING MyDsnDb1

and, in this connection, for another request:

SET TRANSACTION LOCAL NAME MyWriteTransaction

or

SET TRANSACTION LOCAL NAME MyWriteTransaction USING MyDsnDb1

The form SET TRANSACTION …​ NAME transaction-name [USING namedUniqueWorkspace] differs from earlier implementations whereby the configuration set by the SET command would be repeated for the next transaction.The inclusion of the NAME and/or USING clauses makes the configuration repeatable on demand by use of the name.

Important

A return to the usual mode of operation requires a detach/connect cycle.

Ending Explicit Transactions

In SQL, a transaction is completed by a COMMIT or ROLLBACK request.ODBC has methods that do one or the other, such as SQLEndTran.Some programs are able to invoke SQLExecDirect but cannot call SQLEndTran.For those programs it is necessary to call an explicit

SQLExecDirect( hStmt, "COMMIT" )

to ensure that the interface will call

SQLEndTran( SQL_HANDLE_DBC, hConnection, SQL_COMMIT );
Note

If a transaction is initiated locally, the driver will execute SQLEndTran for the local hStmt.

Two Phase Commit Transactions

The ODBC/JDBC driver supports two-phase commit transactions, that is, a single transaction across different Firebird databases.Up to 16 databases can be accessed simultaneously in one such transaction — that is an absolute limit.

The call to start a two-phase commit transaction is:

SQLSetConnectAttr (connection, 4000, (void*) TRUE, 0);

To cancel the common connection:

SQLSetConnectAttr (connection, 4000, (void*) FALSE, 0);

More Transactions

Firebird ODBC by default uses one transaction per connection.Programmatically you can use a more flexible transaction structure.For example, you can use multiple transactions within one connection, whereby a single connection can be using a number of read/write transactions simultaneously.

An Example
HSTMT stmtRd;
HSTMT stmtWr;
SQLAllocHandle( SQL_HANDLE_STMT, connection, &stmtRd );
SQLAllocHandle( SQL_HANDLE_STMT, connection, &stmtWr );
SQLExecDirect( stmtRd, (UCHAR*)
  "SET TRANSACTION LOCAL\n"
  "READ ONLY\n"
  "ISOLATION LEVEL\n"
  "READ COMMITTED NO RECORD_VERSION WAIT\n",
  SQL_NTS );
SQLExecDirect( stmtWr, (UCHAR*)
  "SET TRANSACTION LOCAL\n"
  "READ WRITE\n"
  "ISOLATION LEVEL\n"
  "READ COMMITTED NO RECORD_VERSION WAIT\n",
  SQL_NTS );
SQLExecDirect( stmtRd,(UCHAR*)
  "SELECT CURRENCY FROM COUNTRY"
  "   WHERE country = 'Canada'"
  "   FOR UPDATE OF CURRENCY",
  SQL_NTS );
SQLFetch( stmtRd );
SQLPrepare( stmtWr, (UCHAR*)
  "update COUNTRY\n"
  "set    CURRENCY = 'CndDlr'\n"
  "where  COUNTRY = 'Canada'\n",
  SQL_NTS );
SQLExecute( stmtWr );
SQLExecDirect( stmtWr, (UCHAR*)"COMMIT", SQL_NTS );