An ODBC-based Application

This example shows the steps that a C or C++ application takes to use ODBC to update two SQL Server databases within an MS DTC transaction. It shows the OLE transaction calls for initiating and committing MS DTC transactions as well as the ODBC call to propagate an MS DTC transaction from the application to SQL Server. Note that the complete ODBC example is not shown here but it can be found in the Microsoft SQL Server Programmer's Toolkit.

These are the steps that the application performs:

The application first declares variables and completes initialization. Note that it is not necessary for the application to call either CoInitialize or OleInitialize. MS DTC does not depend on these calls.
The application connects to MS DTC and obtains an interface pointer to the transaction dispenser object by calling DtcGetTransactionManager.
The application uses standard ODBC calls to connect to two databases under the control of different SQL Servers.
The application begins an MS DTC transaction by calling the ITransactionDispenser::BeginTransaction method on the transaction dispenser object. BeginTransaction initiates a new transaction and returns a transaction object that represents the transaction.
The application calls the ODBC SQLSetConnectOption function to associate the transaction with each of the ODBC database connections. All further work performed on the ODBC connections is performed within the MS DTC transaction.
The application uses standard ODBC calls to associate a statement handle with each ODBC database connection. The application then updates each of the databases.
The application calls ITransaction::Commit 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. When the transaction completes, the application releases the transaction dispenser object.
You cannot use the ODBC database connections until ITransaction::Commit completes and you call SQLSetConnectOption to enlist in a new MS DTC transaction. If you reuse the ODBC connection before doing this, an error will result.
Note If you have used an ODBC connection with an MS DTC transaction and you want to use the same ODBC connection with a local SQL Server transaction, you must first call SQLSetConnectOption and pass a NULL transaction. By passing a NULL transaction to the ODBC SQLSetConnectOption function, you instruct SQL Server to use local SQL Server transactions.

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

void main(int argc, char **argv)
{
  
ITransactionDispenser    *pTransactionDispenser;
ITransaction                *pTransaction;
HRESULT                    hr = S_OK ;
BOOL                        tf = 0 ;
TCHAR                    SqlStatement[STR_LEN*2];
  
  
// Initialize globals & validate command line arguments
InitGlobals(argc,argv);
  
// Obtain the ITransactionDispenser Interface pointer
// by calling DtcGetTransactionManager()
hr = DtcGetTransactionManager(
        NULL,                 // LPTSTR pszHost,
        NULL,                // LPTSTR pszTmName,
        IID_ITransactionDispenser,    // REFIID rid,
        0,                    // DWORD dwReserved1,
        0,                    // WORD wcbReserved2,
        0,                    // void FAR * pvReserved2,
        (void **)&pTransactionDispenser     // void**    ppvObject) ;
if (FAILED (hr))
{
printf("DtcGetTransactionManager failed: %x\n", hr);
       exit (1);
}
  
// Initialize the ODBC environment handle.
if (SQL_NULL_HENV == gHenv)
{
ProcessRC("SQLAllocEnv",0,SQLAllocEnv(&gHenv));
}
  
// Establish connection to database on server#1
LogonToDB(&gSrv1);
 
// Establish connection to database on server#2
LogonToDB(&gSrv2);
  
  
// Loop performing distributed transactions
for (INT i = 0; i < 5; i++)
{
  
// Initiate an MS DTC transaction
hr = pTransactionDispenser->BeginTransaction(
            NULL,                // IUnknown __RPC_FAR *punkOuter,
            ISOLATIONLEVEL_ISOLATED,    // ISOLEVEL isoLevel,
            ISOFLAG_RETAIN_DONTCARE,    // ULONG isoFlags,
            NULL,                // ITransactionOptions *pOptions 
            &pTransaction        //  ITransaction **ppTransaction) ;

if (FAILED (hr))
{    
printf("BeginTransaction failed: %x\n",hr);
exit(1);
}
  
// Enlist each of the data sources in the transaction
Enlist(&gSrv1,pTransaction);
Enlist(&gSrv2,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);
if (FAILED(hr))
{
printf("pTransaction->Commit() failed: %x\n",hr);
exit(1);
}
  
// At end of each transaction, pTransaction-Release()
// must be called.
hr = pTransaction->Release();
if (FAILED(hr))
{
printf("pTransaction->Release() failed: %x\n",hr);
exit(1);
}
  
printf("Successfully committed Transaction #%d\n",i);
  
} // for 
  

// release the transaction dispenser    
pTransactionDispenser->Release();
  
// Free ODBC handles
FreeODBCHandles(&gSrv1);
FreeODBCHandles(&gSrv2);
  
// Free the global ODBC environment handle.
SQLFreeEnv(gHenv);
}
  
  
void LogonToDB(DBCONN *ptr)
{
RETCODE rc = 0;
  
rc = SQLAllocConnect(gHenv, &(ptr->hdbc) );
  
if (ProcessRC("SQLAllocConnect",ptr,rc))
{
rc = SQLConnect(ptr->hdbc, 
(UCHAR *)(ptr->pszSrv),
SQL_NTS,
(UCHAR *)(ptr->pszUser),
SQL_NTS,
(UCHAR *)(ptr->pszPasswd),
SQL_NTS
);
  
ProcessRC("SQLConnect",ptr,rc);
}
}
  
  
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);
}
  
  
void ExecuteStatement(DBCONN *ptr, LPTSTR pszBuf)
{
RETCODE rc = 0;
  
// Allocate a statement handle for use with SQLExecDirect
rc = SQLAllocStmt(ptr->hdbc,&(ptr->hstmt));

ProcessRC("SQLAllocStmt",ptr,rc);
  
// Execute the passed string as a SQL statement
    rc = SQLExecDirect(ptr->hstmt,(UCHAR *)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);

}
  
  
void FreeODBCHandles(DBCONN *ptr)
{
SQLDisconnect(ptr->hdbc);

SQLFreeConnect(ptr->hdbc);

ptr->hdbc   = SQL_NULL_HDBC;
ptr->hstmt  = SQL_NULL_HSTMT;
}