FIX: Setting of connection attribute fails when you use connection pooling for the SQL Server ODBC connection

// ODBCTestCase.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include "windows.h"
#include "sqlext.h"
#include "sql.h" 
#include "stdlib.h"

void GetSQLError();
long InitializeEnvironment();
long Connect(BOOL lbUseDSN);
long Disconnect();
int  ExecuteProcedure();

SQLHENV     ghEnvironment = NULL;
SQLHDBC     ghConnection  = NULL;
HSTMT       ghStatement   = NULL;
/**********************************************
 * main
 **********************************************/
int main(int argc, char* argv[])
{
	BOOL lbTransaction = TRUE;
	BOOL lbUseDSN      = FALSE;
	long lValue =0;

	if(argc > 1)
	{
		if(strcmp(argv[1], "DSN") == 0)
			lbUseDSN = TRUE;
		if(argc > 2)
		{
			if(strcmp(argv[2], "TRAN") == 0)
				lbTransaction = TRUE;
		}
	}
    
	if(InitializeEnvironment() == 0)
	{
		for(long llSub = 0; llSub < 2; llSub++)
		{
			if(Connect(lbUseDSN) == 0)
			{

				if(lbTransaction)
					SQLSetConnectOption(ghConnection, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

				//SQLGetConnectAttr(ghConnection,SQL_ATTR_AUTOCOMMIT,&lValue,0,NULL);

				ExecuteProcedure();

				if(lbTransaction)
				{
					SQLTransact(ghEnvironment, ghConnection, SQL_COMMIT); 
					
					//If you do not call the following, the problem does not occur:
					SQLSetConnectOption(ghConnection, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);


					//If you call the following the problem does not occur:
					//SQLSetConnectOption(ghConnection, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
					//SQLGetConnectAttr(ghConnection,SQL_ATTR_AUTOCOMMIT,&lValue,0,NULL);
				}

				Disconnect();
			}
		}
		SQLFreeHandle(SQL_HANDLE_ENV, ghEnvironment);
	}
	
	return 0;
}


/**********************************************
 * InitializeEnvironment
 **********************************************/
long InitializeEnvironment()
{
	if (!SQL_SUCCEEDED(SQLSetEnvAttr(NULL,
									 SQL_ATTR_CONNECTION_POOLING,
									 (SQLPOINTER)SQL_CP_ONE_PER_DRIVER,
									 SQL_IS_INTEGER)))
	{
		GetSQLError();
		return 8;
	}

	if(!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, NULL, &ghEnvironment)))
	{
		GetSQLError();
		return 8;
	}
	if(!SQL_SUCCEEDED(SQLSetEnvAttr(ghEnvironment, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC2, SQL_IS_INTEGER)))
	{
		GetSQLError();
		return 8;
	}
	if (!SQL_SUCCEEDED(SQLSetEnvAttr(ghEnvironment, 
									 SQL_ATTR_CP_MATCH,
									 (SQLPOINTER) SQL_CP_STRICT_MATCH , 
									 //(SQLPOINTER) SQL_CP_RELAXED_MATCH , 
									 SQL_IS_INTEGER)))
	{
		GetSQLError();
		return 8;
	}

	return 0;   
}
/**********************************************
 * Connect
 **********************************************/
long Connect(BOOL lbUseDSN)
{
	SQLCHAR     lszOutConnectString[1024];
	SQLSMALLINT llReturnLength;
	SQLAllocHandle(SQL_HANDLE_DBC, ghEnvironment, &ghConnection);

//  If you set the isolation before opening the connection, no error reported.
//  Customer cannot set this attribute before opening connection because the object 
//	is running under COM+, and under COM+ isolation levels automatically are set to serializable
//	if(!SQL_SUCCEEDED(::SQLSetConnectAttr(ghConnection, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER)SQL_TXN_READ_COMMITTED , SQL_IS_INTEGER)))
//	{
//	 GetSQLError();
//	  return 8;
//	}

	if(lbUseDSN)
	{
		int iReturn = ::SQLConnect(ghConnection,
									   (SQLCHAR*)"LocalCPR",
									   SQL_NTS,
									   (SQLCHAR*)"sa",
									   SQL_NTS,
									   (SQLCHAR*)"password1",
									   SQL_NTS);
		if(!SQL_SUCCEEDED(iReturn))
		{
			GetSQLError();
			return 8;
		}
	}
	else
	{
		if(!SQL_SUCCEEDED(SQLDriverConnect(ghConnection, 
										   NULL,
										   (SQLCHAR*)"DSN=LocalCPR;UID=sa;PWD=password1;",
										   SQL_NTS,
										   lszOutConnectString,
										   1024,
										   &llReturnLength,
										   SQL_DRIVER_NOPROMPT)))
		{
			GetSQLError();
			return 8;
		}
	}

	SQLAllocStmt(ghConnection, &ghStatement);

	//If you set the isolation after you open the connection, you see the problem.
	if(!SQL_SUCCEEDED(::SQLSetConnectAttr(ghConnection, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER)SQL_TXN_READ_COMMITTED , SQL_IS_INTEGER)))
	{
	 GetSQLError();
	  return 8;
	}
	return 0;
}
/**********************************************
 * Disconnect
 **********************************************/
long Disconnect()
{
	if(ghStatement)
	{
		if(!SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, ghStatement)))
		{
			GetSQLError();
			return 8;
		}
		ghStatement = NULL;
	}
 
	if(ghConnection)
	{
		::SQLDisconnect(ghConnection);

		if(!SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_DBC, ghConnection)))
		{
			GetSQLError();
			return 8;
		}
		ghConnection = NULL;
	}
	return 0;
}
/**********************************************
 * ExecuteProcedure
 **********************************************/
int ExecuteProcedure()
{
	SQLINTEGER  mlIndicator = 0; 
	SQLRETURN   lnSqlRetCd = SQL_SUCCESS; 
	::SQLFreeStmt(ghStatement, SQL_CLOSE);
	::SQLFreeStmt(ghStatement, SQL_UNBIND);

	/*****************************************************************/
	/* Execute Procedure
	/*****************************************************************/
	RETCODE llDbRetCd = SQLExecDirect(ghStatement, (SQLCHAR*)"SELECT * From Table1", SQL_NTS);
	if((llDbRetCd != SQL_SUCCESS) && (llDbRetCd != SQL_SUCCESS_WITH_INFO))
	{
		GetSQLError();
		return 8;
	}


	/*****************************************************************/
	/* Bind return Value
	/*****************************************************************/
	char lszReturnBuf[300];
	SDWORD lSts;

	llDbRetCd = SQLBindCol(ghStatement, 1, SQL_C_TCHAR, &lszReturnBuf, 300, &lSts);

	if ((llDbRetCd != SQL_SUCCESS) && (llDbRetCd != SQL_SUCCESS_WITH_INFO))
	{
		GetSQLError();
		return 8;
	}

	
	/*****************************************************************/
	/* Fetch Result
	/*****************************************************************/
	llDbRetCd = SQLFetch(ghStatement);
	if ((llDbRetCd != SQL_SUCCESS) && (llDbRetCd != SQL_SUCCESS_WITH_INFO))
	{
		GetSQLError();
		return 8;
	}

	printf("Output Value : %s\n",lszReturnBuf);
	return 0;
}

/**********************************************
 * GetSQLError
 **********************************************/
void GetSQLError()
{
long    llDbErrCd = 0;
short   llRetMsgLen = 0;
char    lszSqlErrMsg[255];
char    lszSqlMsg[255];

   SQLError(ghEnvironment,
            ghConnection,
            ghStatement,
            (SQLCHAR*) lszSqlErrMsg,
            &llDbErrCd,
            (SQLCHAR*) lszSqlMsg,
            255,
            &llRetMsgLen);
	printf(lszSqlErrMsg);
	printf(lszSqlMsg);
}