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