一个非常灵活的ODBC编程方法

通常我们在用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;

}