ODBC.CPP


/*MS DTC Sample
**
**This example uses MS DTC (distributed transaction
**coordinator) to perform simultaneous updates on two
**SQL servers. The transaction in this example is
**client initiated. The client also initiates the
**commit operation.
**
** As this sample uses the ODBC interface, you'll need
** to configure ODBC data source names for 2 SQL servers
** that you can use to run this sample. Each DSN; data
** source name, must point to the pubs database in
** a separate SQL server. The authors table in the pubs
** database is used in this sample.
**
** Build Instructions:
** This sample must link with the following files: xolehlp.lib, odbc32.lib, odbccp32.lib
**
** Usage Instructions:
** Please run this program with the '-h' flag.
**
*/

//------------------------------------------------------------------------------
// Include standard header files
//------------------------------------------------------------------------------
#include <windows.h>
#include <stdio.h>
#include <conio.h>
#include <ctype.h>

//------------------------------------------------------------------------------
// Include MS DTC specific header files.
//------------------------------------------------------------------------------
#define INITGUID

#include "txdtc.h"
#include "xolehlp.h"


//------------------------------------------------------------------------------
// Include ODBC specific header file.
//------------------------------------------------------------------------------
#ifndef DBNTWIN32
#define DBNTWIN32

#include <SQL.h>
#include <SQLEXT.h>
#include "odbcss.h"
#include <ODBCINST.h>

#endif /* DBNTWIN32 */

//------------------------------------------------------------------------------
// Define constants
//------------------------------------------------------------------------------
#define STR_LEN40

//------------------------------------------------------------------------------
// Define datatypes
//------------------------------------------------------------------------------
typedef struct DBCONN
{
TCHARpszSrv[STR_LEN];// data source name, configured through control panel
TCHARpszUser [STR_LEN];// Login user name
TCHARpszPasswd[STR_LEN];// Login user password
HDBChdbc;// handle to an ODBC database connection
HSTMThstmt;// an ODBC statement handle, for use with SQLExecDirect

}DBCONN;


//------------------------------------------------------------------------------
// Define Globals
//------------------------------------------------------------------------------
static DBCONNgSrv1, gSrv2;// global DB connection struct for server 1 and 2

static TCHARgAuthorID[STR_LEN];// use 11 chars only - per Authors table.
static TCHARgNewAddress[STR_LEN+1];// max. address length in the Author's table.

//-------------------------------------------------------------------------------
// ODBC specific global vars...
//-------------------------------------------------------------------------------
HENVgHenv = SQL_NULL_HENV;

//-------------------------------------------------------------------------------
// Forward declaration of routines used.
//-------------------------------------------------------------------------------

void InitGlobals(int argc, char **argv);
void LogonToDB(DBCONN *ptr);
void Enlist(DBCONN *ptr, ITransaction *pTransaction);
void ExecuteStatement(DBCONN *ptr, LPTSTR pszBuf);
BOOL ProcessRC(LPTSTR pszFuncName, DBCONN *ptr, RETCODE rc);
void DoSQLError(DBCONN *ptr);
void FreeODBCHandles(DBCONN *ptr);


//-------------------------------------------------------------------------------
// main()
//-------------------------------------------------------------------------------

void main(int argc, char **argv)
{

ITransactionDispenser*pTransactionDispenser;
ITransaction*pTransaction;
HRESULThr = S_OK ;
BOOLtf = 0 ;
TCHARSqlStatement[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,// /* in */ REFIID rid,
0,// /* in */ DWORDdwReserved1,
0, // /* in */ WORDwcbReserved2,
NULL,// /* in */ void FAR * pvReserved2,
(void **)&pTransactionDispenser // /* out */ 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,///* [in] */ IUnknown __RPC_FAR *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: %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 InitGlobals(INT argc, char **argv)
{

TCHAR Usage[] = "\n [-S1 server_1] \n"\
" [-U1 user_name_1]\n"\
" [-P1 passwd_1]\n"\
" [-S2 server_2]\n"\
" [-U2 user_name_2]\n"\
" [-P2 passwd_2]\n"\
" [-ID au_id]\n"\
" [-Ad new_address\n"\
" [-h Usage]\n";


// Init ODBC handles to be invalid
gSrv1.hdbc = SQL_NULL_HDBC;
gSrv1.hstmt= SQL_NULL_HSTMT;

gSrv2.hdbc = SQL_NULL_HDBC;
gSrv2.hstmt= SQL_NULL_HSTMT;

// Null login info, set defaults as appropriate

lstrcpy(gSrv1.pszSrv,"");
lstrcpy(gSrv1.pszUser,"sa");
lstrcpy(gSrv1.pszPasswd,"");
lstrcpy(gSrv2.pszSrv,"");
lstrcpy(gSrv2.pszUser,"sa");
lstrcpy(gSrv2.pszPasswd,"");

lstrcpy(gAuthorID,"172-32-1176");// default au_id value from authors table in pubs db.

// scan command line arguments for user input.
for (INT i = 1; i < argc; i++)
{
if (*argv[i] == '-' || *argv[i] == '/')
{
switch (argv[i][1])
{
// get the server or DSN name
case 's':
case 'S':
switch (argv[i][2])
{

case '1':
lstrcpy(gSrv1.pszSrv,argv[++i]);
break;

case '2':
lstrcpy(gSrv2.pszSrv,argv[++i]);
break;

default:
printf("Invalid Input %s\n",argv[i]);
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
};
break;

// get user name
case 'u':
case 'U':
switch (argv[i][2])
{

case '1':
lstrcpy(gSrv1.pszUser,argv[++i]);
break;

case '2':
lstrcpy(gSrv2.pszUser,argv[++i]);
break;

default:
printf("Invalid Input %s\n",argv[i]);
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
};
break;

// get password
case 'p':
case 'P':
switch (argv[i][2])
{

case '1':
lstrcpy(gSrv1.pszPasswd,argv[++i]);
break;

case '2':
lstrcpy(gSrv2.pszPasswd,argv[++i]);
break;

default:
printf("Invalid Input %s\n",argv[i]);
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
};
break;

// get au_id, overriding default value.
case 'i':
case 'I':
lstrcpy(gAuthorID,argv[++i]);
break;
// get new address to associate with the au_id
case 'a':
case 'A':
lstrcpy(gNewAddress,argv[++i]);
break;
case '?':
case 'h':
case 'H':
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
break;

default:
printf("Invalid Input: %s\n",argv[i]);
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
}
}
else
{
printf("Illegal command line argument #%d, %s\n",i,argv[i]);
printf("\nUsage: %s %s",argv[0],Usage);
exit(1);
}
}

printf("-----------------------------\n");

printf("MS DTC/ODBC Sample Configuration parameters\n");
printf( "server_1: %s\n"\
"user_name_1: %s\n"\
"passwd_1: %s\n"\
"server_2: %s\n"\
"user_name_2: %s\n"\
"passwd_2: %s\n",

gSrv1.pszSrv,gSrv1.pszUser,gSrv1.pszPasswd,
gSrv2.pszSrv,gSrv2.pszUser,gSrv2.pszPasswd);

printf("-----------------------------\n");

}

//-------------------------------------------------------------------------------

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


// ---------------------------------------------------------------------------

BOOL ProcessRC(LPTSTR pszFuncName, DBCONN *ptr,RETCODE rc)
{

switch (rc)
{

case SQL_SUCCESS:
return TRUE;
break;

case SQL_SUCCESS_WITH_INFO:
DoSQLError(ptr);
return TRUE;
break;

case SQL_ERROR:
printf("%s Failed - see more info\n",pszFuncName);
DoSQLError(ptr);
exit(-1);
return FALSE;
break;

case SQL_INVALID_HANDLE:
printf("%s Failed - SQL_INVALID_HANDLE\n",pszFuncName);
exit(-1);
return FALSE;
break;

case SQL_NO_DATA_FOUND:
printf("%s Failed - SQL_NO_DATA_FOUND\n",pszFuncName);
return FALSE;
break;

case SQL_STILL_EXECUTING:
printf("%s Failed - SQL_STILL_EXECUTING\n",pszFuncName);
return FALSE;
break;

case SQL_NEED_DATA:
printf("%s Failed - SQL_NEED_DATA\n",pszFuncName);
return FALSE;
break;

default:
printf("%s Failed - unexpected error, rc = %x\n",pszFuncName,rc);
DoSQLError(ptr);
exit(-1);
return FALSE;
break;
}

}

// ---------------------------------------------------------------------------

void DoSQLError(DBCONN *ptr)
{

const INTMSG_BUF_SIZE = 300;
UCHAR szSqlState[MSG_BUF_SIZE];
UCHARszErrorMsg[MSG_BUF_SIZE];

SQLINTEGERfNativeError= 0;
SWORDcbErrorMsg= MSG_BUF_SIZE;
RETCODErc;

rc = SQLError(gHenv,
ptr ? ptr->hdbc : 0,
ptr ? ptr->hstmt : 0,
szSqlState,
&fNativeError,
szErrorMsg,
MSG_BUF_SIZE,
&cbErrorMsg
);

if (rc != SQL_NO_DATA_FOUND || rc != SQL_ERROR)
{
if (fNativeError != 0x1645)// ignore change database to master context message
{
printf("SQLError info:\n");
printf("SqlState: %s, fNativeError: %x\n",szSqlState,fNativeError);
printf("Error Message: %s\n",szErrorMsg);
}
}
else
{
printf("SQLError() failed: %x, NO_DATA_FOUND OR SQL_ERROR\n",rc);
}

}
// ---------------------------------------------------------------------------