Example: Using an Application Program to Update Two SQL Server Databases

Article
10/19/2016

Applies To: Windows 10, Windows 7, Windows 8, Windows 8.1, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows Server Technical Preview, Windows Vista

The following example demonstrates the transaction calls for initiating and committing DTC transactions, and the ODBC calls for propagating a DTC transaction from an application program to a relational database. This example uses a Microsoft SQL Server database, identical copies of which are maintained on two different systems. It updates the address of an author on both database systems under the control of the DTC distributed transaction.

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

Note

Application programs that use the DTC do not need to call either CoInitializeEx or OleInitialize before calling DtcGetTransactionManager. The DtcGetTransactionManager function does not depend on these calls.

ITransactionDispenser * pTransactionDispenser;  
ITransaction * pTransaction;  
HRESULT hr = S_OK ;  

// Obtain a transaction dispenser interface pointer from the DTC.  
hr = DtcGetTransactionManager(  
 NULL,              // [in] char * pszHost,  
 NULL,              // [in] char * pszTmName,  
 IID_ITransactionDispenser,    // [in] REFIID riid,  
 0,                // [in] DWORD dwReserved1,  
 0,                // [in] WORD wcbVarLenReserved2,  
 (void *)NULL,          // [in] void * pvVarDataReserved2,  
 (void **)&pTransactionDispenser // [out] void ** ppv  
);  
if (FAILED (hr)) {  
  printf("The DtcGetTransactionManager failed: Error # %#x\n", hr);  
  exit(1); // Replace with specific error handling.  
}

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

HENV   hEnv = SQL_NULL_HENV;  
HDBC   hDbc1, hDbc2;  
RETCODE rc = 0;  

// Initialize ODBC.  
rc = SQLAllocEnv(&hEnv);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLAllocEnv failed: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

// Allocate connections to the database servers.  
rc = SQLAllocConnect(hEnv, &hDbc1);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLAllocConnect failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

rc = SQLAllocConnect(hEnv, &hDbc2);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLAllocConnect failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

// Establish connection to database on server #1.  
rc = SQLConnect(  
 hDbc1,  
 (unsigned char*)SQL_SERVER_1, SQL_NTS,  
 (unsigned char *)SQL_USER_ID, SQL_NTS,  
 (unsigned char *)SQL_PASSWORD, SQL_NTS  
);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLConnect failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

// Establish connection to database on server #2.  
rc = SQLConnect(  
 hDbc2,  
 (unsigned char*)SQL_SERVER_2, SQL_NTS,  
 (unsigned char *)SQL_USER_ID, SQL_NTS,  
 (unsigned char *)SQL_PASSWORD, SQL_NTS  
);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLConnect failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}

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

// Initiate a DTC transaction.  
hr = pTransactionDispenser->BeginTransaction(  
 NULL,           // [in] IUnknown * punkOuter,  
 ISOLATIONLEVEL_ISOLATED, // [in] ISOLEVEL isoLevel,  
 ISOFLAG_RETAIN_DONTCARE, // [in] ULONG isoFlags,  
 NULL,           // [in] ITransactionOptions * pOptions,  
 &pTransaction       // [out] ITransaction * ppTransaction  
);  
if (FAILED (hr)) {  
  printf("BeginTransaction failed: Error # %#x\n", hr);  
  exit(1); // Replace with specific error handling.  
}

The application program associates the transaction object with the ODBC database connections. This ensures that further work on the ODBC connections is performed under the auspices of the DTC transaction.

// Enlist each of the data sources in the transaction.  
rc = SQLSetConnectAttr (  
 hDbc1,  
 SQL_COPT_SS_ENLIST_IN_the DTC,  
 (UDWORD)pTransaction  
);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLSetConnectOption failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

rc = SQLSetConnectOption (  
 hDbc2,  
 SQL_COPT_SS_ENLIST_IN_the DTC,  
 (UDWORD)pTransaction  
);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLSetConnectOption failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}

The application program associates a statement handle with each ODBC database connection. These standard ODBC calls are not affected by using the DTC.

// Allocate resources for sending the SQL statement.  
HSTMT hStmt1, hStmt2;  

rc = SQLAllocStmt(hDbc1, &hStmt1);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLAllocStmt failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

rc = SQLAllocStmt(hDbc2, &hStmt2);  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLAllocStmt failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}

The application program uses the ODBC database connections to do work on behalf of the transaction.

// Generate the SQL statement to execute on the databases.  
char SqlStatement[300];  

if ((NULL == gNewAddress) || (NULL == gAuthorID)) {  
  printf("gNewAddress and gAuthorID must be globally defined.\n");  
  exit(0); // Replace with specific error handling.  
}  

// Use a more secure alternative to sprintf.  
// #include <strsafe.h>   
hr = StringCbPrintf(  
 SqlStatement, sizeof(SqlStatement),  
 "update authors set address = '%s' where au_id = '%s'",  
 gNewAddress, gAuthorID  
);   
if (FAILED (hr)) {  
  printf("StringCbPrintf failed: Error # %#x\n", hr);  
  exit(0); // Replace with specific error handling.  
}  

// Perform updates on both databases.  
rc = SQLExecDirect(hStmt1, (unsigned char *)SqlStatement, SQL_NTS);  
if !(SQL_SUCCESS == rc) {  
  printf("SQLExecDirect failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

rc = SQLExecDirect(hStmt2, (unsigned char *)SqlStatement, SQL_NTS);  
if !(SQL_SUCCESS == rc) {  
  printf("SQLExecDirect failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

// Free the statement handles.  
rc = SQLFreeStmt(hStmt1, SQL_DROP);hStmt1 = SQL_NULL_HSTMT;  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLFreeStmt failed for DB1: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}  

rc = SQLFreeStmt(hStmt2, SQL_DROP);hStmt2 = SQL_NULL_HSTMT;  
if !((SQL_SUCCESS == rc) || (SQL_SUCCESS_WITH_INFO == rc)) {  
  printf("SQLFreeStmt failed for DB2: Error # %#x\n", rc);  
  exit(-1); // Replace with specific error handling.  
}

The application program calls the ITransaction::Commit method on the transaction object. The 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 its 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 either in a new DTC transaction or in the null transaction. When the application completes the transaction, it releases the transaction object.
```
// Commit the transaction.  
hr = pTransaction->Commit(  
 FALSE,           // [in] BOOL fRetaining,  
 XACTTC_SYNC_PHASEONE,   // [in] DWORD grfTC,  
 0             // [in] DWORD grfRM  
);  
if (FAILED(hr)) {  
  printf("pTransaction->Commit() failed: Error # %#x\n", hr);  
  exit(1); // Replace with specific error handling.  
}  

// Release the transaction object.  
pTransaction->Release();  
```

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

// Release the transaction dispenser.  
pTransactionDispenser->Release();  

// Free the ODBC connections to the database.  
SQLDisconnect(hDbc1);SQLFreeConnect(hDbc1);hDbc1 = SQL_NULL_HDBC;SQLDisconnect(hDbc2);SQLFreeConnect(hDbc2);hDbc2 = SQL_NULL_HDBC;  

// Free the global ODBC environment handle.  
SQLFreeEnv(hEnv);

Example: Using an Application Program to Update Two SQL Server Databases

Additional resources