dsncollection.cpp

一个非常不错的MFC ODBC范例

// DSNCollection.cpp: implementation of the CDSNCollection class.
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "DSNCollection.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

const unsigned char CDSNCollection::s_SYSTEM = 0x01;
const unsigned char CDSNCollection::s_USER	= 0x02;

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CDSNCollection::CDSNCollection() 
	//给变量赋初值
	: m_sErrorMsg("")
	, m_iSQLResult(SQL_SUCCESS)
	, m_hSQLEnvironment(SQL_NULL_HANDLE)
{
	//初始化ODBC环境，并分配环境变量
	m_iSQLResult = SQLAllocHandle(SQL_HANDLE_ENV,
								  SQL_NULL_HANDLE,
								  &m_hSQLEnvironment);
	if (m_iSQLResult != SQL_SUCCESS)
	{
		m_sErrorMsg = "SQLAllocHandle failed";
		return;
	}

	//设置ODBC版本属性，否则，系统将返回错误	
	m_iSQLResult = SQLSetEnvAttr(m_hSQLEnvironment,
								 SQL_ATTR_ODBC_VERSION,
								 (SQLPOINTER)SQL_OV_ODBC3,
								 0); 
	if (m_iSQLResult != SQL_SUCCESS)
	{
		m_sErrorMsg = "SQLSetEnvAttr 设置ODBC版本失败!";
		return;
	}
}

CDSNCollection::~CDSNCollection()
{
	//释放句柄资源等
	m_iSQLResult = SQLFreeHandle(SQL_HANDLE_ENV, m_hSQLEnvironment);
}

SQLRETURN CDSNCollection::CallSQLDataSources(CString& sName, SQLUSMALLINT direction)
{
	SQLSMALLINT dataLen1,dataLen2;
	SQLCHAR sTemp[DSNCOLLECTION_BUFLEN];
	SQLCHAR sDesc[DSNCOLLECTION_BUFLEN];

	//查找DSNs
	m_iSQLResult = SQLDataSources(m_hSQLEnvironment, 
								  direction,
								  sTemp,
								  DSNCOLLECTION_BUFLEN,
								  &dataLen1,
								  sDesc,
								  DSNCOLLECTION_BUFLEN,
								  &dataLen2);

	switch (m_iSQLResult)
	{
	case SQL_SUCCESS:			
	case SQL_SUCCESS_WITH_INFO:	
		sName = sTemp;
		break;
	case SQL_NO_DATA://穷尽列举 
		m_sErrorMsg = "No more DSNs to enumerate";
		break;
	case SQL_ERROR://出错
		{
			SQLCHAR sState[10];
			SQLINTEGER iNativeError;
			SQLCHAR sMsg[DSNCOLLECTION_BUFLEN];
			(void)SQLGetDiagRec(SQL_HANDLE_ENV,
								m_hSQLEnvironment,
								1,
								sState,
								&iNativeError,
								sMsg,
								DSNCOLLECTION_BUFLEN,
								&dataLen2);
			m_sErrorMsg.Format("SQL_ERROR: %s:%s",sState,sMsg);
		}
		break;
	case SQL_INVALID_HANDLE:
		m_sErrorMsg = "无效句柄!";
		break;
	default:
		m_sErrorMsg = "无法归类的错误!";
	}

	return m_iSQLResult;
}

//得到第一个DSN
CString CDSNCollection::GetFirstDSNName(unsigned char ucType /* = (s_SYSTEM | s_USER)*/)
{
	CString sResult = "";
	//判断欲查找数据源类型是否合法
	if (ucType != s_SYSTEM &&
		ucType != s_USER &&
		ucType != (s_SYSTEM | s_USER))
	{
		m_sErrorMsg = "Unknown DSN type";
		m_iSQLResult = SQL_ERROR;
	}
	else
	{
		SQLUSMALLINT direction = SQL_FETCH_FIRST;
		if (ucType == s_SYSTEM)
			direction = SQL_FETCH_FIRST_SYSTEM;//该常量表示搜索第一个系统数据源
		else if (ucType == s_USER)
			direction = SQL_FETCH_FIRST_USER;//搜索第一个用户数据源
		if (SQL_SUCCESS != CallSQLDataSources(sResult, direction))
		{
			AfxMessageBox("搜索第一个数据源时出错!");
		}
	}

	return sResult;
}

CString CDSNCollection::GetNextDSNName()
{
	CString sResult = "";
	if (SQL_SUCCESS != CallSQLDataSources(sResult, SQL_FETCH_NEXT))
	{
		AfxMessageBox("搜索后续数据源时出错!");
	}
	return sResult;
}