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:
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.
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; }