sqlquery.cpp

visual c++ 数据库技术,包括ODBC编程、 DAO编程、OLEDB编程、ADO编程四部分代码！很值得参考

	}
	else 
	{
		LCID lcid = GetUserDefaultLCID();
		for (ULONG l=0; l<ulRecords; l++)
		{
			// Get the error information from the source
			struct MYERRORINFO* pInfo = new MYERRORINFO;
			hr = errInfo.GetAllErrorInfo(l, lcid, &pInfo->bstrDescription,
				&pInfo->bstrSource, &pInfo->guid, &pInfo->dwHelpContext,
				&pInfo->bstrHelpFile);
			if (FAILED(hr))
			{
				delete pInfo;
				continue;
			}

			strError += CString(pInfo->bstrDescription) + "\n";
			delete pInfo;
		}
	}

	if(!strError.IsEmpty())
	{
		ShowError(_T(strError));
		return true;
	}
	return false;
}

void CSQLQuery::Empty()
{
	m_strError.Empty();
	m_strQuery.Empty();
	m_object_list.RemoveAndDestroy();
	DeleteCommand();
	ClearColumns();
}

bool CSQLQuery::InitSession()
{
	ASSERT(m_pSession == NULL);

	if(m_pSession == NULL)
		m_pSession = new CSession;

	m_bMySession = true;
	if (m_pSession->m_spOpenRowset != NULL)
		m_pSession->m_spOpenRowset.Release();

	USES_CONVERSION;

	if (m_pSession->Open(*GetDataSource()) != S_OK)
	{
		ShowError(_T("Couldn't create session on data source"));
		return false;
	}

	return true;
}


COleVariant CSQLQuery::ToVariant(CDynamicAccessor*  pDynamicAccessor,int nCol)
{
	ASSERT(pDynamicAccessor != NULL);
	COleVariant vt;
	DBSTATUS dbStatus;
	DBTYPE dbType;

	pDynamicAccessor->GetStatus(nCol, &dbStatus);
	if(dbStatus == DBSTATUS_S_ISNULL)
		return vt;                      // just return a blank variant

	if(!pDynamicAccessor->GetColumnType(nCol, &dbType))
		return vt;
	switch (dbType)
	{
	case DBTYPE_VARIANT:
		vt = COleVariant((LPCVARIANT)pDynamicAccessor->GetValue(nCol));
		break;
	case DBTYPE_STR:
		vt = COleVariant(CString((LPCSTR)pDynamicAccessor->GetValue(nCol)), VT_BSTR);
		break;
	case DBTYPE_WSTR:
	case DBTYPE_BSTR:
		vt = COleVariant(CString((LPCWSTR)pDynamicAccessor->GetValue(nCol)), VT_BSTR);
		break;
	case DBTYPE_I1:
	case DBTYPE_UI1:
		vt = COleVariant(*((BYTE*)pDynamicAccessor->GetValue(nCol)));
		break;
	case DBTYPE_I2:
	case DBTYPE_UI2:
		vt = COleVariant(*((short*)pDynamicAccessor->GetValue(nCol)));
		break;
	case DBTYPE_I4:
	case DBTYPE_UI4:
		vt = COleVariant(*((long*)pDynamicAccessor->GetValue(nCol)));
		break;
	case DBTYPE_R4:
		vt = COleVariant(*((float*)pDynamicAccessor->GetValue(nCol)));
		break;
	case DBTYPE_R8:
		vt = COleVariant(*((double*)pDynamicAccessor->GetValue(nCol)));
		break;
	case DBTYPE_BOOL:
		vt = COleVariant((short)*(BOOL*)pDynamicAccessor->GetValue(nCol), VT_BOOL);
		break;
	case DBTYPE_DATE:
		{
			COleDateTime dt(*((DATE*)pDynamicAccessor->GetValue(nCol)));
			vt = COleVariant(dt);
		}
		break;
	case DBTYPE_CY:
		{
			COleCurrency cy(*((CURRENCY*)pDynamicAccessor->GetValue(nCol)));
			vt = COleVariant(cy);
		}
		break;
	case DBTYPE_NUMERIC:
		{
			DB_NUMERIC num;

			if(pDynamicAccessor->GetValue(nCol, &num))
			{
				double dbl;

				dbl = (double)*((__int64*)num.val);
				while(num.scale-- > 0)
					dbl /= 10;
				if(num.sign == 0)
					dbl = -dbl;
				vt = COleVariant(dbl);
			}
		}
		break;
	case DBTYPE_DBDATE:
		{
			DBDATE dbDate;

			if(pDynamicAccessor->GetValue(nCol, &dbDate))
			{
				COleDateTime dt;

				dt.SetDate(dbDate.year, dbDate.month, dbDate.day);
				vt = COleVariant(dt);
			}
		}
		break;
	case DBTYPE_DBTIME:
		{
			DBTIME dbTime;

			if(pDynamicAccessor->GetValue(nCol, &dbTime))
			{
				COleDateTime dt;

				dt.SetTime(dbTime.hour, dbTime.minute, dbTime.second);
				vt = COleVariant(dt);
			}
		}
		break;
	case DBTYPE_DBTIMESTAMP:
		{
			DBTIMESTAMP dbTimeStamp;

			if(pDynamicAccessor->GetValue(nCol, &dbTimeStamp))
			{
				vt = COleVariant(COleDateTime(dbTimeStamp.year, dbTimeStamp.month, dbTimeStamp.day,
											  dbTimeStamp.hour, dbTimeStamp.minute, dbTimeStamp.second));
			}
		}
		break;
	case DBTYPE_NULL:
	case DBTYPE_EMPTY:
		break;
	default:
		TRACE2("Unsupported DBTYPE (%d) in column %d\n", dbType, nCol);
		ASSERT(0);
		break;
	}
	return vt;
}

CQueryObject* CSQLQuery::Get()
{
	return m_object_list.IsEmpty() ? NULL : m_object_list.RemoveHead();
}

CSQLQuery& CSQLQuery::operator>>(BYTE &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}

CSQLQuery& CSQLQuery::operator>>(short &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(CY &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(COleCurrency &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}

CSQLQuery& CSQLQuery::operator>>(int &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}

CSQLQuery& CSQLQuery::operator>>(long &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}

CSQLQuery& CSQLQuery::operator>>(float &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(double &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(CTime &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(COleDateTime &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}
CSQLQuery& CSQLQuery::operator>>(CString &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj >> value;
		delete pObj;
	}
	return *this;
}

CSQLQuery& CSQLQuery::operator>(CString &value)
{
	CQueryObject* pObj = Get();
	ASSERT(pObj);
	if(pObj)
	{
		*pObj > value;
		delete pObj;
	}
	return *this;
}

void CSQLQuery::AddColumns(LPCSTR lpszCol)
{
	m_columns.Add(lpszCol);
}

void CSQLQuery::ClearColumns()
{
	m_columns.RemoveAll();
}

UINT CSQLQuery::GetColumnsCount()
{
	ASSERT(m_pCommand);
	if(!m_pCommand)
		return 0;

	UINT cnt = pACCESSOR->GetColumnCount();
	return cnt;
}

const CString CSQLQuery::GetColumnName(int nIndex)
{
	ULONG nCol = nIndex + 1;
	CString strName = pACCESSOR->GetColumnName(nCol);
	return strName;
}

void CSQLQuery::GetColumns(CStringArray& dest)
{
	UINT cnt = GetColumnsCount();
	ULONG nCol = 1;
	while(cnt--)
	{
		CString str = pACCESSOR->GetColumnName(nCol++);
		dest.Add(str);
	}
}

bool CSQLQuery::IsExistColumn(LPCSTR lpszCol)
{
	UINT cnt = GetColumnsCount();
	if(cnt == 0)
		return false;
	ULONG nCol = 1;
	while(cnt--)
	{
		CString str = pACCESSOR->GetColumnName(nCol++);
		if(str == lpszCol)
			return true;
	}

	return false;
}

void CSQLQuery::RemoveLastComma()
{
	int idx = m_strQuery.ReverseFind(',');
	if(idx != -1)
		m_strQuery.SetAt(idx,' ');
}

bool CSQLQuery::ExecuteSQL(LPCSTR szSQL)
{
	m_strQuery = szSQL;
	return ExecuteSQL();
}

bool CSQLQuery::ExecuteSQL()
{
	HRESULT hr;
	if(m_strQuery.IsEmpty())
	{
		ShowError("Empty Query");
		return false;
	}
	
	m_object_list.RemoveAndDestroy();

	DeleteCommand();

	m_pCommand = new CSingleQuery;

	CDBPropSet	propset(DBPROPSET_ROWSET);

	bool bBind = true;
	hr = m_pCommand->Open(*GetSession(), m_strQuery, &propset, NULL, DBGUID_DEFAULT, false);
	if (hr != S_OK)
	{
		ShowDBError(m_pCommand->m_spCommand, IID_ICommand);
		return false;
	}

	if(m_pCommand->GetInterface() != NULL)
	{
		hr = m_pCommand->Bind();
		if (hr != S_OK)
		{
			ShowDBError(m_pCommand->m_spCommand, IID_ICommand);
			return false;
		}
	}

	// Display the data
	if(m_pCommand->GetInterface() != NULL)
	while(m_pCommand->MoveNext() == S_OK)
	{
		if(m_columns.GetSize() == 0)
		{
			for(ULONG nCol = 0; nCol < m_pCommand->GetColumnCount(); nCol++)
			{
				COleVariant vt = ToVariant(m_pCommand,nCol+1);
				CQueryObject*pObj = new CQueryObject(vt);
				m_object_list.AddTail(pObj);
				vt.Clear();
			}
		}
		else
		{
			for(int i = 0; i < m_columns.GetSize(); i++)
			{
				ULONG nCol = 0;
				LPCSTR pColumnName = m_columns[i];
				bool bOk = m_pCommand->GetInternalColumnNo((char*)pColumnName,&nCol);
				ASSERT(bOk);
				COleVariant vt = ToVariant(m_pCommand,nCol+1);
				CQueryObject*pObj = new CQueryObject(vt);
				m_object_list.AddTail(pObj);
				vt.Clear();
			}
		}
	
	}
	return true;
}

void CSQLQuery::DeleteCommand()
{
	if(m_pCommand)
	{
		delete m_pCommand;
	}
	m_pCommand = NULL;
}