Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » ODBC » Downloads
 
Add your own
alternative version
Go to top

The CODBCDynamic class

, 14 Mar 2000
A class to dynamically read data from any ODBC data source
dynamic_odbc_class_demo.zip
Debug
ODBCDynamicTest.clw
ODBCDynamicTest.dsp
ODBCDynamicTest.dsw
ODBCDynamicTest.plg
res
ODBCDynamicTest.ico
dynamic_odbc_class_src.zip
ODBCClasses.clw
ODBCClasses.def
ODBCClasses.dsp
ODBCClasses.dsw
ODBCClasses.plg
#include "stdafx.h"
#include "ODBCDynamic.hpp"

#define MAX_COLNAME 50

class CODBCColumnInfo : public CObject
{
public:
	CODBCColumnInfo(char lpszColName[MAX_COLNAME + 1], SWORD nLen, short nFieldType, SWORD nSQLType, UDWORD nPrecision);

public:
	char m_lpszColName[MAX_COLNAME + 1];
	SWORD m_nLen;
	short m_nFieldType;
	SWORD m_nSQLType;
	UDWORD m_nPrecision;
};

CODBCColumnInfo::CODBCColumnInfo(char lpszColName[MAX_COLNAME + 1], SWORD nLen, short nFieldType, SWORD nSQLType, UDWORD nPrecision)
{
	strcpy(m_lpszColName, lpszColName);
	m_nLen = nLen;
	m_nFieldType = nFieldType;
	m_nSQLType = nSQLType;
	m_nPrecision = nPrecision;
}

CODBCDynamic::CODBCDynamic(LPCSTR lpszDSN, 
LPCSTR lpszUser /* = NULL */, LPCSTR lpszPassword /* = NULL */, 
SQLHENV henv /* = NULL */, SQLHDBC hdbc /* = NULL */)
// throws CUserException
{
	CUserException* pUserException = NULL;

	SQLRETURN rc;
	
	m_henv = henv;
	m_hdbc = hdbc;
	m_bIsConnected = FALSE;
	
	if (m_henv
	|| SQL_SUCCESS == (rc = ::SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv)))
	{
		if (SQL_SUCCESS == (rc = ::SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER)))
		{
			if (m_hdbc
			|| SQL_SUCCESS == (rc = ::SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc)))
			{
				rc = ::SQLConnect(m_hdbc, 
					(unsigned char*)lpszDSN, SQL_NTS,
					(unsigned char*)lpszUser, SQL_NTS, 
					(unsigned char*)lpszPassword, SQL_NTS);
				if ((SQL_SUCCESS == rc)
				|| (SQL_SUCCESS_WITH_INFO == rc))
				{
					m_bIsConnected = TRUE;
				}
				else
				{
					pUserException = new CUserException(TRUE, CODBCDynamic::errorSqlConnect);
				}
			}
			else
			{
				pUserException = new CUserException(TRUE, CODBCDynamic::errorSqlAllocHandleHdbc);
			}
		}
		else
		{
			pUserException = new CUserException(TRUE, CODBCDynamic::errorSqlSetEnvAttr);
		}
	}
	else
	{
		pUserException = new CUserException(TRUE, CODBCDynamic::errorSqlAllocHandleHenv);
	}


	if (NULL == pUserException 
	&& FALSE == m_bIsConnected)
	{
		pUserException = new CUserException(TRUE, CODBCDynamic::errorUnknownCause);
	}

	if (pUserException)
	{
		pUserException->InitString();
		throw pUserException;
	}
}

CODBCDynamic::~CODBCDynamic()
{
	if (m_henv)
	{
		if (m_hdbc)
		{
			if (m_bIsConnected)
			{
				::SQLDisconnect(m_hdbc);
				m_bIsConnected = FALSE;
			}
			::SQLFreeHandle(SQL_HANDLE_DBC, m_hdbc);
			m_hdbc = NULL;
		}
		::SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
		m_henv = NULL;
	}

	CleanOutRecordArray();
}

void CODBCDynamic::CleanOutRecordArray()
{
	for (int iRecord = m_ODBCRecordArray.GetUpperBound(); iRecord >= 0; iRecord--)
	{
		POSITION pos;
		CString strColName;
		CDBVariantEx* pvarValue;

		CODBCRecord* pODBCRecord = m_ODBCRecordArray[iRecord];
		if (pODBCRecord)
		{
			for (pos = pODBCRecord->GetStartPosition(); pos != NULL;)
			{
				pODBCRecord->GetNextAssoc(pos, strColName, pvarValue);

				ASSERT(pvarValue);
				if (pvarValue)
				{
					delete pvarValue;
				}
				pODBCRecord->RemoveKey(strColName);
			}
			ASSERT(0 == pODBCRecord->GetCount());

			delete pODBCRecord;
		}
		m_ODBCRecordArray.RemoveAt(iRecord);
	}
	ASSERT(0 == m_ODBCRecordArray.GetSize());
}

short CODBCDynamic::GetFieldTypeFromSQLType(short nSQLType)
{
	short nFieldType;

	switch (nSQLType)
	{
	case SQL_BIT:
		nFieldType = SQL_C_BIT;
		break;

	case SQL_TINYINT:
		nFieldType = SQL_C_UTINYINT;
		break;

	case SQL_SMALLINT:
		nFieldType = SQL_C_SSHORT;
		break;

	case SQL_INTEGER:
		nFieldType = SQL_C_SLONG;
		break;

	case SQL_REAL:
		nFieldType = SQL_C_FLOAT;
		break;

	case SQL_FLOAT:
	case SQL_DOUBLE:
		nFieldType = SQL_C_DOUBLE;
		break;

	case SQL_DATE:
	case SQL_TIME:
	case SQL_TIMESTAMP:
#if (ODBCVER >= 0x0300)
	case SQL_TYPE_TIMESTAMP:
#endif
		nFieldType = SQL_C_TIMESTAMP;
		break;

	case SQL_NUMERIC:
	case SQL_DECIMAL:
	case SQL_BIGINT:
	case SQL_CHAR:
	case SQL_VARCHAR:
	case SQL_LONGVARCHAR:
		nFieldType = SQL_C_CHAR;
		break;

	case SQL_BINARY:
	case SQL_VARBINARY:
	case SQL_LONGVARBINARY:
		nFieldType = SQL_C_BINARY;
		break;

	default:
		ASSERT(FALSE);
	}

	return nFieldType;
}

void* CODBCDynamic::GetDataBuffer(CDBVariantEx* pvarValue,
	short nFieldType, int* pnLen, short nSQLType, UDWORD nPrecision)
{
	void* pvData = NULL;

	switch (nFieldType)
	{
		case SQL_C_BIT:
			pvData = &pvarValue->m_boolVal;
			pvarValue->m_dwType = DBVT_BOOL;
			*pnLen = sizeof(pvarValue->m_boolVal);
		break;

		case SQL_C_UTINYINT:
			pvData = &pvarValue->m_chVal;
			pvarValue->m_dwType = DBVT_UCHAR;
			*pnLen = sizeof(pvarValue->m_chVal);
		break;

		case SQL_C_SSHORT:
			pvData = &pvarValue->m_iVal;
			pvarValue->m_dwType = DBVT_SHORT;
			*pnLen = sizeof(pvarValue->m_iVal);
		break;

		case SQL_C_SLONG:
			pvData = &pvarValue->m_lVal;
			pvarValue->m_dwType = DBVT_LONG;
			*pnLen = sizeof(pvarValue->m_lVal);
		break;

		case SQL_C_FLOAT:
			pvData = &pvarValue->m_fltVal;
			pvarValue->m_dwType = DBVT_SINGLE;
			*pnLen = sizeof(pvarValue->m_fltVal);
		break;

		case SQL_C_DOUBLE:
			pvData = &pvarValue->m_dblVal;
			pvarValue->m_dwType = DBVT_DOUBLE;
			*pnLen = sizeof(pvarValue->m_dblVal);
		break;

		case SQL_C_TIMESTAMP:
			pvData = pvarValue->m_pdate = new TIMESTAMP_STRUCT;
			pvarValue->m_dwType = DBVT_DATE;
			*pnLen = sizeof(*pvarValue->m_pdate);
		break;

		case SQL_C_CHAR:
		{
			pvarValue->m_pstring = new CString;
			pvarValue->m_dwType = DBVT_STRING;

			// Need to have at least a length of 1 for the NULL terminator
			*pnLen = 1;

			if ((nSQLType != SQL_LONGVARCHAR) 
			&& (nSQLType != SQL_LONGVARBINARY))
			{
				*pnLen = nPrecision + 1;

				// If type is Numeric or Decimal add 2 bytes for decimal
				// point and sign
				if ((nSQLType == SQL_NUMERIC)
				|| (nSQLType == SQL_DECIMAL))
				{
					*pnLen += 2;
				}
			}
			pvData = pvarValue->m_pstring->GetBufferSetLength(*pnLen);
		}
		break;


		case SQL_C_BINARY:
			pvarValue->m_pbinary = new CLongBinary;
			pvarValue->m_dwType = DBVT_BINARY;

			if (nSQLType == SQL_LONGVARBINARY)
			{
				*pnLen = 1;
			}
			else
			{
				*pnLen = nPrecision;
			}

			pvarValue->m_pbinary->m_hData = ::GlobalAlloc(GMEM_MOVEABLE, *pnLen);
			pvarValue->m_pbinary->m_dwDataLength = *pnLen;

			pvData = ::GlobalLock(pvarValue->m_pbinary->m_hData);
		break;

		default: ASSERT(FALSE);
	}

	return pvData;
}

long CODBCDynamic::GetData(SQLHSTMT hstmt, short nFieldIndex, short nFieldType, LPVOID pvData, int nLen, short nSQLType)
{
	UNUSED(nSQLType);

	long nActualSize = -1;

	SQLRETURN rc;
	if (SQL_SUCCESS != (rc = ::SQLGetData(hstmt, nFieldIndex, nFieldType, pvData, nLen, &nActualSize)))
	{
		UCHAR lpszMsg[SQL_MAX_MESSAGE_LENGTH];
		UCHAR lpszState[SQL_SQLSTATE_SIZE];
		CString strMsg;
		CString strState;
		SDWORD lNative;
		SWORD nOutlen;

		::SQLError(m_henv, m_hdbc, hstmt, lpszState, &lNative,
					lpszMsg, SQL_MAX_MESSAGE_LENGTH-1, &nOutlen);
		TRACE1("SQLGetData failed: %s\n", lpszMsg);
	}

	return nActualSize;
}

long CODBCDynamic::ExecuteSQL(LPCTSTR lpszSQL)
{
	long lRecordsRead = 0;

	CleanOutRecordArray();

	BOOL bSuccess = FALSE;
	SQLRETURN rc;
	SQLHSTMT hstmt = NULL;

	if (SQL_SUCCESS == (rc = ::SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hstmt)))
	{
		if (SQL_SUCCESS == (rc = ::SQLPrepare(hstmt, (unsigned char*)lpszSQL, SQL_NTS)))
		{
			if (SQL_SUCCESS == (rc = ::SQLExecute(hstmt)))
			{
				lRecordsRead = FetchData(hstmt);
			}
			::SQLFreeHandle(SQL_HANDLE_STMT, &hstmt);
		}
	}

	return lRecordsRead;
}

long CODBCDynamic::FetchData(HSTMT hstmt)
{
	SQLRETURN rc;
	
	long lRecordsRead = 0;
	CODBCRecord* pODBCRecord;

	SWORD FAR iNumResultCols = 0;
	CObArray arrODBCColumns;
	CODBCColumnInfo* pODBCColumnInfo;

	rc = SQLFetch(hstmt);
	while (SQL_SUCCESS == rc)
	{
		if (1 == ++lRecordsRead)
		{
			if (SQL_SUCCESS == (rc = ::SQLNumResultCols(hstmt, &iNumResultCols)))
			{
				for (int i = 1; i <= iNumResultCols; i++)
				{
					char lpszColName[MAX_COLNAME + 1];
					SWORD nLen;
					SWORD nSQLType;
					UDWORD nPrecision;
					SWORD nScale;
					SWORD nNullability;

					if (SQL_SUCCESS == (rc = ::SQLDescribeCol(hstmt, i, reinterpret_cast<UCHAR*>(lpszColName), MAX_COLNAME, &nLen, 
					&nSQLType, &nPrecision, &nScale, &nNullability)))
					{
						// Determine the default field type and get the data buffer
						short nFieldType = GetFieldTypeFromSQLType(nSQLType);

						pODBCColumnInfo = new CODBCColumnInfo(lpszColName, nLen, nFieldType, nSQLType, nPrecision);
						arrODBCColumns.Add(static_cast<CObject*>(pODBCColumnInfo));
					}
				}
			}
		}

		pODBCRecord = new CODBCRecord();

		for (int j = 0; j < iNumResultCols; j++)
		{
			CString strColName = (static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_lpszColName;

			CDBVariantEx* pvarValue = new CDBVariantEx();
			pvarValue->Clear();

			int nGetDataBufferLen = 0;
			void* pvData = GetDataBuffer(pvarValue, 
				(static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nFieldType,
				&nGetDataBufferLen, 
				(static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nSQLType, 
				(static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nPrecision);

			long nActualSize = GetData(hstmt, j+1, 
				(static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nFieldType, 
				pvData, 
				nGetDataBufferLen, 
				(static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nSQLType);

			// deal with NULLs
			if (-1 == nActualSize)
			{
				pvarValue->Clear();
				pvarValue->m_dwType = DBVT_NULL;
			}
 
			pODBCRecord->SetAt((static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_lpszColName, pvarValue);

			if ((-1 != nActualSize)
			&& ((static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nFieldType == SQL_C_CHAR))
			{
				// Release the string buffer 
				CString strValue = (CString)*pvarValue->m_pstring;
				strValue.ReleaseBuffer(nActualSize < (static_cast<CODBCColumnInfo*>(arrODBCColumns[j]))->m_nLen ? nActualSize : ((CODBCColumnInfo*)arrODBCColumns[j])->m_nLen);
			}
		}

		m_ODBCRecordArray.Add(pODBCRecord);

		rc = SQLFetch(hstmt);
	}

	for (int iColumn = arrODBCColumns.GetUpperBound(); iColumn >= 0; iColumn--)
	{
		CODBCColumnInfo* pODBCColumnInfo = static_cast<CODBCColumnInfo*>(arrODBCColumns[iColumn]);
		if (pODBCColumnInfo)
		{
			delete pODBCColumnInfo;
		}
		arrODBCColumns.RemoveAt(iColumn);
	}
	ASSERT(0 == arrODBCColumns.GetSize());

	return lRecordsRead;
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Tom Archer
Software Developer (Senior) Microsoft
United States United States
I'm a Senior Programming Writer in the Microsoft Windows Server organization where my focus is WMI, BITS, WinRM, and SMI-S.

| Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 15 Mar 2000
Article Copyright 2000 by Tom Archer
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid