Using an Application Program to Update Two SQL Server Databases

The following example introduces the OLE Transactions calls for initiating and committing MS DTC transactions, and the ODBC call for propagating an MS DTC transaction from an application program to a relational database:

1. The client application connects to MS DTC by calling DtcGetTransactionManager, which returns an interface pointer to a transaction dispenser object. The transaction dispenser object is used to initiate subsequent transactions.

   ITransactionDispenser *pTransactionDispenser;
   ITransaction *pTransaction;
   HRESULT hr = S_OK ;
   
   // Obtain an interface pointer from MS DTC proxy.
   hr = DtcGetTransactionManager(0, 0, IID_ITransactionDispenser, 0, 0, 0,(void **)&pTransactionDispenser);
   if (FAILED (hr))
   {
       printf("DtcGetTransactionManager failed: %x\n", hr);
       exit (1);
   }

Application programs do not need to call either CoInitialize or OleInitialize. The DtcGetTransactionManager doesn’t depend on these calls. The transaction dispenser object can be used to create multiple transactions.

1. The application program connects to the two databases using ODBC. These standard ODBC calls are not affected by using MS DTC.

   // Establish connection to database on server#1
   LogonToDB(&gSrv1);
   
   // Establish connection to database on server#2
   LogonToDB(&gSrv2);
   
   void LogonToDB(DBCONN *ptr)
   {
       RETCODE rc = 0;
   
       rc = SQLAllocConnect(gHenv, &(ptr->hdbc) );
   
       if (ProcessRC("SQLAllocConnect",ptr,rc))
       {
           rc = SQLConnect(ptr->hdbc,
                           (unsigned char *)(ptr->pszDSN),
                           SQL_NTS,
                           (unsigned char *)(ptr->pszUser),
                           SQL_NTS,
                           (unsigned char *)(ptr->pszPasswd),
                           SQL_NTS
                           );
   
           ProcessRC("SQLConnect",ptr,rc);
       }
   }

The application program begins an MS DTC transaction by invoking the ITransactionDispenser::BeginTransaction method on the transaction dispenser object obtained in step one. The BeginTransaction method returns a translation object that represents the transaction.

// Initiate an MS DTC transaction

hr = 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__RPC_FAR
                                          //        *__RPC_FAR *ppTransaction
        ) ;
        
if (FAILED (hr))
{    
    printf("BeginTransaction failed: %x\n",hr);
    exit(1);
}

1. The application program associates the transaction object with the ODBC database connections, using the new ODBC enlistment interface that directs further work on the ODBC connections to be performed under the auspices of the MS DTC transaction.

   // Enlist each of the data sources in the transaction
   Enlist(&gSrv1,pTransaction);
   Enlist(&gSrv2,pTransaction);
    
   //-------------------------------------------------------------------    
   void Enlist(DBCONN *ptr, ITransaction *pTransaction)
   {
       RETCODE rc = 0;
    
       // Enlist database in the transaction
       rc = SQLSetConnectOption (ptr->hdbc, SQL_COPT_SS_ENLIST_IN_DTC,
                                       (UDWORD)pTransaction);
    
       ProcessRC("SQLSetConnectOption",ptr,rc);
    
   }

2. The application program associates a statement handle with each ODBC database connection. These standard ODBC calls are not affected by using MS DTC. The application program uses the ODBC database connections to do work on behalf of the transaction.

   // Generate the SQL statement to execute on each of the
   // databases.
   sprintf(SqlStatement,
               "update authors set address = '%s' where au_id = '%s'",
                gNewAddress,gAuthorID
           );
       
   // Perform updates on both of the DBs participating in
   // the transaction
   ExecuteStatement(&gSrv1,SqlStatement);
   ExecuteStatement(&gSrv2,SqlStatement);
    
   // --------------------------------------------------
   void ExecuteStatement(DBCONN *ptr, char *pszBuf)
   {
       RETCODE rc = 0;
    
       // Allocate an ODBC statement handle
    
       rc = SQLAllocStmt(ptr->hdbc,&(ptr->hstmt));
       ProcessRC("SQLAllocStmt",ptr,rc);
       
       // Execute the passed string as a SQL statement
       rc = SQLExecDirect(ptr->hstmt,
               (unsigned char *)pszBuf,SQL_NTS);
       
       ProcessRC("SQLExecDirect",ptr,rc);
    
       // Free the statement handle
       rc = SQLFreeStmt(ptr->hstmt, SQL_DROP);
       ptr->hstmt = SQL_NULL_HSTMT;
    
       ProcessRC("SQLFreeStmt",ptr,rc);
       
   }

3. The application program calls the Commit method on the transaction object. MS DTC performs the two-phase commit protocol to commit the transaction. If any SQL Server database is unable to commit the transaction, the transaction is aborted and the transaction's effects are undone from all databases that were modified.

The ODBC database connections can’t be used until the commit completes and another SQLSetConnectOption call is made to enlist the ODBC connection in either a new MS DTC transaction or in the null transaction.

Note Do not reuse the ODBC connection before the commit completes or another SQLSetConnectOption call is made.

When the application completes the transaction, it releases the transaction object.

// Commit the transaction
hr = pTransaction->Commit(0,0,0);
if (FAILED(hr))
{
    printf("pTransaction->Commit() failed: %x\n",hr);
    exit(1);
}
 
// Release Transaction
pTransaction->Release();
if (FAILED(hr))
{
    printf("pTransaction->Commit() failed: %x\n",hr);
    exit(1);
}

1. When no more transactions are needed, the program releases the transaction dispenser object and closes the ODBC connections.

   // release transaction dispenser
   pTransactionDispenser->Release();
    
    
   // Free ODBC handles
   FreeODBCHandles(&gSrv1);
   FreeODBCHandles(&gSrv2);
    
   // Free the global ODBC environment handle.
   SQLFreeEnv(gHenv);
    
   // -------------------------------------------------------
   void FreeODBCHandles(DBCONN *ptr)
   {
       SQLDisconnect(ptr->hdbc);
       SQLFreeConnect(ptr->hdbc);
       
       ptr->hdbc   = SQL_NULL_HDBC;
       ptr->hstmt  = SQL_NULL_HSTMT;
   }