A DB-Library-based Application

This example shows the steps that a C or C++ application takes to use DB-Library to update two SQL Servers within an MS DTC transaction. It shows the OLE transaction calls for initiating and committing MS DTC transactions as well as the DB-Library call to propagate an MS DTC transaction from the application to SQL Server. Note that the complete DB-Library 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:

  1. 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.
  2. The application connects to MS DTC and obtains an interface pointer to the transaction dispenser object by calling DtcGetTransactionManager.
  3. The application uses standard DB-Library calls to connect to two databases under the control of different SQL Servers.
  4. 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.
  5. The application calls the DB-Library dbenlisttrans function to associate the transaction with each of the DB-Library database connections. All further work performed on the DB-Library connections is performed within the MS DTC transaction.

    The application passes two parameters to dbenlisttrans. The first parameter is the database process structure (DBPROC), which identifies the SQL Server connection. The second parameter is a pointer to the MS DTC transaction object. To learn more about dbenlisttrans, see What's New in SQL Server 6.5.

  6. The application uses standard DB-Library calls to associate a statement handle with each DB-Library database connection. The application then updates each of the databases.
  7. 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 DB-Library database connections until ITransaction::Commit completes and you call dbenlisttrans to enlist in a new MS DTC transaction. If you reuse the DB-Library connection before doing this, an error will result.

    Note If you have used a DB-Library connection with an MS DTC transaction and you want to use the same DB-Library connection with a local SQL Server transaction, you must first call dbenlisttrans and pass a NULL transaction. By passing a NULL transaction to the DB-Library dbenlisttrans function, you instruct SQL Server to use local SQL Server transactions.

  8. When no more transactions are needed, the application releases the transaction dispenser object and closes all DB-Library connections. 
    void main(int argc, char **argv)
{
  
ITransactionDispenser    *pTransactionDispenser;
ITransaction                *pTransaction;
HRESULT                    hr = S_OK ;
TCHAR                    SqlStatement[STR_LEN*2];
  
  
// Initialize globals & validate command line arguments
InitGlobals(argc,argv);
  
// set error/msg handlers for this program
dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
dberrhandle((DBERRHANDLE_PROC)err_handler);
  
// initialize LOGINREC structure
login = dblogin();
  
// 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);
}
  
// Establish connection to database on server#1
LogonToDB(&dbproc_server1,&gSrv1);
 
// Establish connection to database on server#2
LogonToDB(&dbproc_server2,&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 on the transaction
Enlist(&gSrv1,dbproc_server1,pTransaction);
Enlist(&gSrv2,dbproc_server2,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,dbproc_server1,SqlStatement);
ExecuteStatement(&gSrv2,dbproc_server2,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();
  
// release DBLib resources and exit
dbexit();
}
  
  
void LogonToDB(DBPROCESS **dbp, DBCONN *ptr)
{
  
DBSETLUSER(login, ptr->pszUser);
DBSETLPWD(login, ptr->pszPasswd);
DBSETLAPP(login, "example");
  
*dbp = dbopen (login, ptr->pszSrv);
if (*dbp == NULL)
{
printf ("\nLogin to server: %s failed, exiting!\n",ptr->pszSrv);
exit (ERREXIT);
}
  
/* Use the "pubs" database. */
dbuse(*dbp, "pubs");
}
  
  
void Enlist(DBCONN *ptr, DBPROCESS *dbp, ITransaction *pTransaction)
{
RETCODE rc = 0;

rc = dbenlisttrans (dbp, pTransaction);

if (FAIL == rc) 
{
printf("\ndbenlisttrans() failed: %x\n",rc);
exit(1);
}
  
}
  
  
void ExecuteStatement(DBCONN *ptr, DBPROCESS *dbp, char *pszBuf)
{
RETCODE rc = 0;
  
dbcmd(dbp,pszBuf);

rc = dbsqlexec(dbp);
if (FAIL == rc)
{
printf("dbsqlexec() failed\n");
exit(1);
}
  
rc = dbresults(dbp);
if (rc != SUCCEED)
{
printf("dbresults() failed: %x\n",rc);
exit(1);
}
}