通常我们在用VC进行数据库编程时首先会考虑到用向导通过ADO控件操作.的确,这是一个非常方便的方法.但也存在很大的不足,编程上的一些限制暂且不提,在客户使用方面,如你把用ADO控件写成的程序移动到另一台机器,由于相应的控件可能并没有提前安装,还有路径等问题.使得使用起来极为不便,本文提供了一个封装好的ODBC类,很好地解决了这个问题,实现很灵活的编程.我通过自己创建的一个示例程序说明:
1. 我封装SQL的操作封装成一个类,用户使用时只要调用就行.
//tool.h
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <odbcinst.h>
#define SQLERR_FORMAT "SQL Error State:%s, Native Error Code: %lX, ODBC Error: %s"
#define MM_MAX_DB_ERRMSG_SIZE 1024
typedef struct tagHIS_ADMIN //示例程序所用结构
{
int iId;
char strName[64];
char strPwd[64];
char strRemark[256];
}HIS_ADMIN, *LPHIS_ADMIN;
BOOL InitSQLEnvironment(SQLHANDLE *pEnv);
BOOL CreateDBConnect(SQLHDBC *phDBC, SQLHANDLE dbEnv, const char* pOdbcName,
const char* pUserName, const char* pPwd, char *errmsg);
BOOL GetDBError(SQLCHAR *errmsg, SWORD fHandleType, SQLHANDLE handle);
BOOL CreateDBState(SQLHSTMT *phStMt, SQLHDBC hDBC, char *errmsg);
//tool.cpp
注意在头部包含此语句:
#pragma comment(lib, "odbc32.lib")
//初始化.
BOOL InitSQLEnvironment(SQLHANDLE *pEnv)
{
// Allocate an Environment Handle
if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, pEnv) != SQL_SUCCESS)
{
return FALSE;
}
SQLRETURN sRet = SQLSetEnvAttr(*pEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if (sRet != SQL_SUCCESS)
{
SQLFreeHandle(SQL_HANDLE_ENV, *pEnv);
return FALSE;
}
return TRUE;
}
//创建数据库连接
BOOL CreateDBConnect(SQLHDBC *phDBC, SQLHANDLE dbEnv, const char* pOdbcName,
const char* pUserName, const char* pPwd, char *errmsg)
{
// Allocate ODBC connection handle and connect.
SQLRETURN sRet;
sRet = SQLAllocHandle(SQL_HANDLE_DBC, dbEnv, phDBC);
if((sRet != SQL_SUCCESS_WITH_INFO) && (sRet != SQL_SUCCESS))
{
GetDBError((SQLCHAR *)errmsg, SQL_HANDLE_DBC, NULL);
return FALSE;
}
sRet = SQLConnect(*phDBC, (UCHAR *)pOdbcName, SQL_NTS,
(UCHAR *)pUserName, SQL_NTS,
(UCHAR *)pPwd, SQL_NTS);
if((sRet != SQL_SUCCESS) && (sRet != SQL_SUCCESS_WITH_INFO))
{
GetDBError((SQLCHAR *)errmsg, SQL_HANDLE_DBC, *phDBC);
SQLFreeHandle(SQL_HANDLE_DBC, *phDBC);
*phDBC = SQL_NULL_HDBC;
return FALSE;
}
return TRUE;
}
//自定义错误函数
BOOL GetDBError(SQLCHAR *errmsg, SWORD fHandleType, SQLHANDLE handle)
{
UCHAR szErrState[SQL_SQLSTATE_SIZE + 1]; // SQL Error State string
UCHAR szErrText[SQL_MAX_MESSAGE_LENGTH + 1]; // SQL Error Text string
char szBuffer[SQL_SQLSTATE_SIZE + SQL_MAX_MESSAGE_LENGTH + 1];
// formatted Error text Buffer
SWORD wErrMsgLen; // Error message length
SQLINTEGER iErrCode; // Native Error code
int iSize; // Display Error Text size
SQLRETURN nErrResult; // Return Code from SQLGetDiagRec
SWORD sMsgNum = 1;
BOOL bRetVal = TRUE;
szBuffer[0] = '/0';
while((nErrResult = SQLGetDiagRec(fHandleType, handle, sMsgNum++, szErrState, &iErrCode, szErrText,
SQL_MAX_MESSAGE_LENGTH - 1, &wErrMsgLen)) != SQL_NO_DATA)
{
if(nErrResult == SQL_ERROR || nErrResult == SQL_INVALID_HANDLE)
{
break;
}
wsprintf(szBuffer, SQLERR_FORMAT, (LPSTR)szErrState, iErrCode, (LPSTR)szErrText);
if (strncmp((char *)szErrState, "08", 2) == 0 || strncmp((char *)szErrState, "01000", 5) == 0)
{
//数据库已经断开
bRetVal = FALSE;
}
iSize = strlen((char *)errmsg);
if (iSize && (iSize + strlen(szBuffer) + 1) >= MM_MAX_DB_ERRMSG_SIZE)
{
break;
}
if (iSize)
{
strcat((char *)errmsg, "/n");
}
strcat((char *)errmsg, szBuffer);
}
return bRetVal;
}
//执行函数
BOOL CreateDBState(SQLHSTMT *phStMt, SQLHDBC hDBC, char *errmsg)
{
// Allocate statement handle, then execute command.
SQLRETURN sRet;
sRet = SQLAllocHandle(SQL_HANDLE_STMT, hDBC, phStMt);
if((sRet != SQL_SUCCESS) && (sRet != SQL_SUCCESS_WITH_INFO))
{
GetDBError((SQLCHAR *)errmsg, SQL_HANDLE_STMT, NULL);
return FALSE;
}
return TRUE;
}
2.实际使用方法:
在His.cpp中
BOOL CHisApp::InitInstance()
{
///begin///
if (!InitSQLEnvironment(&m_hDBEnv))
{
AfxMessageBox("设置ODBC环境失败");
return FALSE;
}
char errmsg1[512];
BOOL bRetVal = TRUE;
memset(errmsg1, 0, sizeof(errmsg1));
// his_conn为数据源名,hisuser:用户名,888888:密码
bRetVal = CreateDBConnect(&m_hDBC, m_hDBEnv, "his_conn", "hisuser", "888888", errmsg1);
if(!bRetVal)
{
AfxMessageBox(errmsg1);
return FALSE;
}
bRetVal = CreateDBState(&m_hStMt, m_hDBC, errmsg1);
if(!bRetVal)
{
AfxMessageBox(errmsg1);
return FALSE;
}
///end///
AfxEnableControlContainer();
// Standard initialization
// If you are not using these features and wish to reduce the size
// of your final executable, you should remove from the following
// the specific initialization routines you do not need.
#ifdef _AFXDLL
Enable3dControls(); // Call this when using MFC in a shared DLL
#else
Enable3dControlsStatic(); // Call this when linking to MFC statically
#endif
// Change the registry key under which our settings are stored.
// TODO: You should modify this string to be something appropriate
// such as the name of your company or organization.
SetRegistryKey(_T("Local AppWizard-Generated Applications"));
LoadStdProfileSettings(); // Load standard INI file options (including MRU)
// Register the application's document templates. Document templates
// serve as the connection between documents, frame windows and views.
CSingleDocTemplate* pDocTemplate;
pDocTemplate = new CSingleDocTemplate(
IDR_MAINFRAME,
RUNTIME_CLASS(CHisDoc),
RUNTIME_CLASS(CMainFrame), // main SDI frame window
RUNTIME_CLASS(CHisView));
AddDocTemplate(pDocTemplate);
// Parse command line for standard shell commands, DDE, file open
CCommandLineInfo cmdInfo;
ParseCommandLine(cmdInfo);
// Dispatch commands specified on the command line
if (!ProcessShellCommand(cmdInfo))
return FALSE;
// The one and only window has been initialized, so show and update it.
m_pMainWnd->ShowWindow(SW_SHOW);
m_pMainWnd->UpdateWindow();
return TRUE;
}
3.具体使用举例:
BOOL CRightListView::ReadRecord(CArray<HIS_ADMIN *, HIS_ADMIN*> *ur)
{
SQLRETURN sRet;
char errmsg[MM_MAX_DB_ERRMSG_SIZE];
char szState[512];
long reason;
SQLINTEGER len[12];
char szAction[1024];
sprintf(szAction, "管理员查询");
sprintf(szState, "exec his..his_proc_get_admin");//已经创建好的存储过程
sRet = SQLExecDirect(theApp.m_hStMt, (SQLCHAR *)szState, SQL_NTS);
if (sRet != SQL_SUCCESS && sRet != SQL_SUCCESS_WITH_INFO)
{
AfxMessageBox("执行查询语句失败");
return FALSE;
}
else
{
SQLBindCol(theApp.m_hStMt, 1, SQL_C_LONG, &reason, 0, &len[0]);
SQLBindCol(theApp.m_hStMt, 2, SQL_C_CHAR, errmsg, MM_MAX_DB_ERRMSG_SIZE, &len[1]);
sRet = SQLFetch(theApp.m_hStMt);
if (sRet != SQL_SUCCESS && sRet != SQL_SUCCESS_WITH_INFO)
{
AfxMessageBox("没有结果");
return FALSE;
}
sRet = SQLMoreResults(theApp.m_hStMt);
if (sRet != SQL_SUCCESS && sRet != SQL_SUCCESS_WITH_INFO)
{
AfxMessageBox("failed");
return FALSE;
}
HIS_ADMIN dat;
HIS_ADMIN *pData;
SQLBindCol(theApp.m_hStMt, 1, SQL_C_LONG, &dat.iId, 0, &len[0]);
SQLBindCol(theApp.m_hStMt, 2, SQL_C_CHAR, &dat.strName, 64, &len[1]);
SQLBindCol(theApp.m_hStMt, 3, SQL_C_CHAR, &dat.strPwd, 64, &len[2]);
SQLBindCol(theApp.m_hStMt, 4, SQL_C_CHAR, &dat.strRemark, 255, &len[3]);
while(1)
{
memset(&dat, 0, sizeof(HIS_ADMIN));
sRet = SQLFetch(theApp.m_hStMt);
if (sRet != SQL_SUCCESS && sRet != SQL_SUCCESS_WITH_INFO)
{
break;
}
pData = (HIS_ADMIN *)calloc(1, sizeof(HIS_ADMIN));
VERIFY(pData);
memcpy(pData, &dat, sizeof(HIS_ADMIN));
ur->Add(pData); //save recordset
}
}
return TRUE;
}