More Transactions

Connecting to Firebird from ApplicationsFirebird Events

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 );

MS DTC Transactions

The Microsoft Distributed Transaction Coordinator (MS DTC) service is a Windows component that is responsible for coordinating transactions that span multiple resource managers, such as database systems, message queues, and file systems.It can perform global, single-phase or two-phase commit transactions involving Microsoft SQL Server, Sybase and other servers that are able to work with it.Our ODBC/JDBC driver provides that capability for Firebird servers.

An Example Using MS DTC

// Include MS DTC specific header files.
//------------------------------------------------------------------------------
#define INITGUID
#include "txdtc.h"
#include "xolehlp.h"

ITransactionDispenser *pTransactionDispenser;
ITransaction *pTransaction;
// Obtain the ITransactionDispenser Interface pointer
// by calling DtcGetTransactionManager()
DtcGetTransactionManager( NULL,// [in] LPTSTR pszHost,
     NULL,// [in] LPTSTR pszTmName,
     IID_ITransactionDispenser,// [in] REFIID rid,
     0,// [in] DWORDdwReserved1,
     0, // [in] WORDwcbReserved2,
     NULL,// [in] void FAR * pvReserved2,
     (void **)&pTransactionDispenser // [out] void** ppvObject
     );
// Establish connection to database on server#1
LogonToDB( &gSrv1 );
// Establish connection to database on server#2
LogonToDB( &gSrv2 );
// Initiate an MS DTC transaction
pTransactionDispenser->BeginTransaction(
     NULL,// [in] IUnknown __RPC_FAR *punkOuter,
     ISOLATIONLEVEL_ISOLATED,// [in] ISOLEVEL isoLevel,
     ISOFLAG_RETAIN_DONTCARE,// [in] ULONG isoFlags,
     NULL,// [in] ITransactionOptions *pOptions
     &pTransaction// [out] ITransaction **ppTransaction
     );
// Enlist each of the data sources in the transaction
SQLSetConnectOption( gSrv1->hdbc, SQL_COPT_SS_ENLIST_IN_DTC, (UDWORD)pTransaction );
SQLSetConnectOption( gSrv2->hdbc, SQL_COPT_SS_ENLIST_IN_DTC, (UDWORD)pTransaction );
// Generate the SQL statement to execute on each of the databases
sprintf( SqlStatement,
  "update authors set address = '%s_%d' where au_id = '%s'",
   gNewAddress, i, gAuthorID );
// Perform updates on both of the DBs participating in the transaction
ExecuteStatement( &gSrv1, SqlStatement );
ExecuteStatement( &gSrv2, SqlStatement );
// Commit the transaction
hr = pTransaction->Commit( 0, 0, 0 );
// or roll back the transaction
//hr = pTransaction->Abort( 0, 0, 0 );