最近工作需要，研究了一下ODBC API，并對它進(jìn)行了簡單的封裝，下面介紹一下：
假設(shè)有一個數(shù)據(jù)庫表為：
CREATE TABLE `testTable` (
`TestTEXT` text,
`TestBigInt` bigint(20) default NULL
)
使用我的封裝類的方法如下：
#include "query.h"
#include <iostream>
using namespace std;
int main(int argc, char* argv[])
{
int nConnectTimeOut = 5;
int nLoginTimeOut = 3;
int nQueryTimeOut = 3;
CConnection * pCon = NULL;
CDBAccess dbAccess;
//初始化
if(!dbAccess.Init(NULL, &pCon, nConnectTimeOut, nLoginTimeOut, nQueryTimeOut))
{
cout << dbAccess.GetLastError()<<endl;
return -1;
}
//連接數(shù)據(jù)庫
if(!dbAccess.Connect("DNSName", "UserName", "Password"))
{
cout << dbAccess.GetLastError()<<endl;
return -1;
}
//oK，連接成功了，可以進(jìn)行你的sql操作了。
char pszSQL[256];
memset(pszSQL, 0x00, sizeof(pszSQL));
sprintf((char*)pszSQL, "SELECT * FROM %s", "testTable");
if(dbAccess.ExecuteSQL(pszSQL))
{ //執(zhí)行查詢語句完畢，開始取數(shù)據(jù)
if(dbAccess.FetchFirst())
{
do
{
long nRetLen = 0;
char pszTest[300];
memset((char*)pszTest, 0x00, sizeof(pszTest));
if(!dbAccess.GetData("TestTEXT", pszTest, sizeof(pszTest), &nRetLen))
{
cout << "無法取到TestTEXT字段信息!"<<endl;
break;
}
cout << "get TestTEXT data:" << pszTest << endl;
_INT64 lTestBigInt = 0;
if(!dbAccess.GetData("TestBigInt", &(lTestBigInt), sizeof(_INT64), &nRetLen, SQL_C_SBIGINT))
{
cout << "無法取到TestBigInt字段信息!"<<endl;
break;
}
char pszTemp[30];
memset(pszTemp, 0x00, 24);
_i64toa(lTestBigInt, (char*)pszTemp, 10);
cout << "get TestBigInt data:" << pszTemp <<endl;
}
while(dbAccess.Fetch());
}
else
{
cout <<dbAccess.GetLastError()<<endl;
}
}
//下面關(guān)閉句柄，否則會出現(xiàn)訪問非法，非常重要！
dbAccess.CloseStmt();
//斷開到數(shù)據(jù)庫的連接
if(!dbAccess.Disconnect())
{
cout <<"error disconnect" <<dbAccess.GetLastError()<<endl;
return -1;
}
dbAccess.Close();
return 0;
}
下面是Query.h文件內(nèi)容：
// Query.h: interface for the CQuery class.
//
//////////////////////////////////////////////////////////////////////
#if !defined(AFX_QUERY_H__CAEAF203_40C0_4C32_BA76_9A4B0245984B__INCLUDED_)
#define AFX_QUERY_H__CAEAF203_40C0_4C32_BA76_9A4B0245984B__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#ifdef WIN32
#include "windows.h"
#endif
//ODBC API
#include <sql.h>
#include <sqlext.h>
#include <odbcinst.h>
//--
#pragma comment(lib,"odbc32.lib")
#pragma comment(lib,"OdbcCP32.Lib")
#ifdef WIN32
typedef __int64 _INT64;
#endif
#ifdef GCC
typedef long long int _INT64;
#endif
//--
/* SQLEndTran() options */
#define SQL_COMMIT 0 //提交
#define SQL_ROLLBACK 1 //回滾
//
//////////////////////////////////////////////////////////////////////////
//錯誤代碼定義
//錯誤代碼定義規(guī)則：ERROR_類名_函數(shù)名_函數(shù)內(nèi)編號
//暫定四位錯誤編號
#define BASE_ERROR 100000
//數(shù)據(jù)庫模塊錯誤代碼
#define DATABASE_BASE_ERROR BASE_ERROR + 0
//CConnection類中的錯誤代碼
#define CON_BASE_ERROR DATABASE_BASE_ERROR + 0
#define ERROR_CON_INIT_1 CON_BASE_ERROR + 1
#define ERROR_CON_INIT_2 CON_BASE_ERROR + 2
#define ERROR_CON_INIT_3 CON_BASE_ERROR + 3
#define ERROR_CON_CONNECT_1 CON_BASE_ERROR + 4
#define ERROR_CON_CONNECT_2 CON_BASE_ERROR + 5
#define ERROR_CON_DISCONNECT_1 CON_BASE_ERROR + 6
#define ERROR_CON_DISCONNECT_2 CON_BASE_ERROR + 7
#define ERROR_CON_BEGINTRAN_1 CON_BASE_ERROR + 8
#define ERROR_CON_ENDTRAN_1 CON_BASE_ERROR + 9
#define ERROR_CON_ENDTRAN_2 CON_BASE_ERROR + 10
#define ERROR_CON_SETTIMEOUT_1 CON_BASE_ERROR + 11
#define ERROR_CON_SETTIMEOUT_2 CON_BASE_ERROR + 12
#define ERROR_CON_SETTIMEOUT_3 CON_BASE_ERROR + 13
#define ERROR_CON_SETTIMEOUT_4 CON_BASE_ERROR + 14
#define ERROR_CON_CONNECT_3 CON_BASE_ERROR + 15
#define ERROR_CON_DISCONNECT_3 CON_BASE_ERROR + 16
#define ERROR_CON_BEGINTRAN_2 CON_BASE_ERROR + 17
#define ERROR_CON_ENDTRAN_3 CON_BASE_ERROR + 18
#define ERROR_CON_SETTIMEOUT_5 CON_BASE_ERROR + 19
#define ERROR_CON_CONNECT_4 CON_BASE_ERROR + 20
#define ERROR_CON_BEGINTRAN_3 CON_BASE_ERROR + 21
#define ERROR_CON_ENDTRAN_4 CON_BASE_ERROR + 22
#define ERROR_CON_ISCONNECT_1 CON_BASE_ERROR + 23
#define ERROR_CON_ISCONNECT_2 CON_BASE_ERROR + 24
//CQuery類中的錯誤代碼
#define QUERY_BASE_ERROR DATABASE_BASE_ERROR + 100
#define ERROR_QUERY_INIT_1 QUERY_BASE_ERROR + 1
#define ERROR_QUERY_INIT_2 QUERY_BASE_ERROR + 2
#define ERROR_QUERY_INIT_3 QUERY_BASE_ERROR + 3
#define ERROR_QUERY_GETCOLCOUNT_1 QUERY_BASE_ERROR + 4
#define ERROR_QUERY_GETCOLCOUNT_2 QUERY_BASE_ERROR + 5
#define ERROR_QUERY_GETCROWCOUNT_1 QUERY_BASE_ERROR + 6
#define ERROR_QUERY_GETCROWCOUNT_2 QUERY_BASE_ERROR + 7
#define ERROR_QUERY_EXECSQL_1 QUERY_BASE_ERROR + 8
#define ERROR_QUERY_EXECSQL_2 QUERY_BASE_ERROR + 9
#define ERROR_QUERY_FETCH_1 QUERY_BASE_ERROR + 10
#define ERROR_QUERY_FETCH_2 QUERY_BASE_ERROR + 11
#define ERROR_QUERY_FETCHNEXT_1 QUERY_BASE_ERROR + 12
#define ERROR_QUERY_FETCHNEXT_2 QUERY_BASE_ERROR + 13
#define ERROR_QUERY_FETCHPRE_1 QUERY_BASE_ERROR + 14
#define ERROR_QUERY_FETCHPRE_2 QUERY_BASE_ERROR + 15
#define ERROR_QUERY_FETCHFIRST_1 QUERY_BASE_ERROR + 16
#define ERROR_QUERY_FETCHFIRST_2 QUERY_BASE_ERROR + 17
#define ERROR_QUERY_FETCHLAST_1 QUERY_BASE_ERROR + 18
#define ERROR_QUERY_FETCHLAST_2 QUERY_BASE_ERROR + 19
#define ERROR_QUERY_FETCHROW_1 QUERY_BASE_ERROR + 20
#define ERROR_QUERY_FETCHROW_2 QUERY_BASE_ERROR + 21
#define ERROR_QUERY_CANCEL_1 QUERY_BASE_ERROR + 22
#define ERROR_QUERY_CANCEL_2 QUERY_BASE_ERROR + 23
#define ERROR_QUERY_GETDATA_1 QUERY_BASE_ERROR + 24
#define ERROR_QUERY_GETDATA_2 QUERY_BASE_ERROR + 25
#define ERROR_QUERY_GETDATA_3 QUERY_BASE_ERROR + 26
#define ERROR_QUERY_GETDATA_4 QUERY_BASE_ERROR + 27
#define ERROR_QUERY_GETCOLBYNAME_1 QUERY_BASE_ERROR + 28
#define ERROR_QUERY_GETCOLNAME_1 QUERY_BASE_ERROR + 29
#define ERROR_QUERY_GETCOLNAME_2 QUERY_BASE_ERROR + 30
#define ERROR_QUERY_INIT_4 QUERY_BASE_ERROR + 31
#define ERROR_QUERY_GETCOLCOUNT_3 QUERY_BASE_ERROR + 32
#define ERROR_QUERY_GETCROWCOUNT_3 QUERY_BASE_ERROR + 33
#define ERROR_QUERY_EXECSQL_3 QUERY_BASE_ERROR + 34
#define ERROR_QUERY_FETCH_3 QUERY_BASE_ERROR + 35
#define ERROR_QUERY_FETCHNEXT_3 QUERY_BASE_ERROR + 36
#define ERROR_QUERY_FETCHPRE_3 QUERY_BASE_ERROR + 37
#define ERROR_QUERY_FETCHFIRST_3 QUERY_BASE_ERROR + 38
#define ERROR_QUERY_FETCHLAST_3 QUERY_BASE_ERROR + 39
#define ERROR_QUERY_FETCHROW_3 QUERY_BASE_ERROR + 40
#define ERROR_QUERY_CANCEL_3 QUERY_BASE_ERROR + 41
#define ERROR_QUERY_GETDATA_5 QUERY_BASE_ERROR + 42
#define ERROR_QUERY_GETCOLNAME_3 QUERY_BASE_ERROR + 43
#define ERROR_QUERY_INSERTARRAY_1 QUERY_BASE_ERROR + 44
#define ERROR_QUERY_INSERTARRAY_2 QUERY_BASE_ERROR + 45
#define ERROR_QUERY_INSERTARRAY_3 QUERY_BASE_ERROR + 46
#define ERROR_QUERY_INSERTARRAY_4 QUERY_BASE_ERROR + 47
#define ERROR_QUERY_INSERTARRAY_5 QUERY_BASE_ERROR + 48
#define ERROR_QUERY_INSERTARRAY_6 QUERY_BASE_ERROR + 49
#define ERROR_QUERY_INIT_5 QUERY_BASE_ERROR + 50
//CDBAccess類中的錯誤代碼
#define DBACCESS_BASE_ERROR DATABASE_BASE_ERROR + 200
#define ERROR_DBACCESS_INIT_1 DBACCESS_BASE_ERROR + 1
#define ERROR_DBACCESS_INIT_2 DBACCESS_BASE_ERROR + 2
#define ERROR_DBACCESS_CONNECT_1 DBACCESS_BASE_ERROR + 3
#define ERROR_DBACCESS_CONNECT_2 DBACCESS_BASE_ERROR + 4
#define ERROR_DBACCESS_DISCONNECT_1 DBACCESS_BASE_ERROR + 5
#define ERROR_DBACCESS_DISCONNECT_2 DBACCESS_BASE_ERROR + 6
#define ERROR_DBACCESS_RECONNECT_1 DBACCESS_BASE_ERROR + 7
#define ERROR_DBACCESS_EXECUTESQL_1 DBACCESS_BASE_ERROR + 8
#define ERROR_DBACCESS_EXECUTESQL_2 DBACCESS_BASE_ERROR + 9
#define ERROR_DBACCESS_EXECUTESQL_3 DBACCESS_BASE_ERROR + 10
#define ERROR_DBACCESS_EXECUTESQL_4 DBACCESS_BASE_ERROR + 11
#define ERROR_DBACCESS_EXECUTESQL_5 DBACCESS_BASE_ERROR + 12
#define ERROR_DBACCESS_INIT_3 DBACCESS_BASE_ERROR + 13
#define ERROR_DBACCESS_INIT_4 DBACCESS_BASE_ERROR + 14
#define ERROR_DBACCESS_GETTASKDATA_1 DBACCESS_BASE_ERROR + 15
#define ERROR_DBACCESS_GETTASKDATA_2 DBACCESS_BASE_ERROR + 16
#define ERROR_DBACCESS_GETTASKDATA_3 DBACCESS_BASE_ERROR + 17
#define ERROR_DBACCESS_GETTASKDATA_4 DBACCESS_BASE_ERROR + 18
#define ERROR_DBACCESS_GETTASKDATA_5 DBACCESS_BASE_ERROR + 19
#define ERROR_DBACCESS_GETTASKDATA_6 DBACCESS_BASE_ERROR + 20
#define ERROR_DBACCESS_GETTASKDATA_7 DBACCESS_BASE_ERROR + 21
#define ERROR_DBACCESS_GETTASKDATA_8 DBACCESS_BASE_ERROR + 22
#define ERROR_DBACCESS_GETTASKDATA_9 DBACCESS_BASE_ERROR + 23
#define ERROR_DBACCESS_GETTASKDATA_10 DBACCESS_BASE_ERROR + 24
#define ERROR_DBACCESS_GETTASKDATA_11 DBACCESS_BASE_ERROR + 25
#define ERROR_DBACCESS_GETTASKDATA_12 DBACCESS_BASE_ERROR + 26
#define ERROR_DBACCESS_GETTASKDATA_13 DBACCESS_BASE_ERROR + 27
//////////////////////////////////////////////////////////////////////////
class CConnection
{
public:
CConnection();
virtual ~CConnection();
public:
//提交事務(wù)處理.參數(shù): SQL_COMMIT為提交，SQL_ROLLBACK為回滾.返回值:是否提交成功
bool EndTran(short nAction);
//開始事務(wù)處理.返回值:是否成功
bool BeginTran();
//斷開連接. 斷開連接前，要確定是否提交了事務(wù)。返回值:是否成功
bool Disconnect();
//連接到DNS，參數(shù):DNS名，用戶名，口令, 返回值:是否連接成功
bool Connect(const char* pszDNS, const char* pszUser, const char* pszPwd);
//取錯誤信息,返回值:錯誤信息
const char* GetLastError(){return (char*)m_pszLastError;}
//是否連接到數(shù)據(jù)庫,返回值:是否連接到數(shù)據(jù)庫
bool IsConnect();
//設(shè)置超時，參數(shù)分別為連接超時，登錄超時.如果為-1，表示該參數(shù)不起作用,返回值:是否設(shè)置成功
bool SetTimeOut(int nConnect = -1, int nLogin = -1);
//是否正在進(jìn)行事務(wù)處理,返回值:是否進(jìn)行事務(wù)處理.
bool IsInTran(){return m_bTraning;}
//重新連接數(shù)據(jù)庫,返回值:是否重連成功
bool ReConnect();
//初始化,參數(shù):連接超時，登錄超時,返回值:是否初始化成功.
bool Init(int nConnectTimeOut = -1, int nLoginTimeOut = -1);
//可以方便的為CQuery類使用。
operator SQLHANDLE(){return m_hCon;}
private:
//設(shè)置錯誤信息,參數(shù):錯誤信息,錯誤代碼
void SetErrorInfo(const char* pszError, long lErrorCode);
//取得錯誤信息，參數(shù):句柄類型，出錯的句柄，錯誤代碼,返回值:是否成功
bool GetErrorInfo(SQLSMALLINT nHandleType, SQLHANDLE nHandle, long lErrorCode);
//是否成功執(zhí)行了，參數(shù):需要判斷的返回值，句柄類型，需要判斷的句柄，錯誤代碼,返回值:是否成功
bool IsSuccess(SQLRETURN nRet, SQLSMALLINT nHandleType, SQLHANDLE nHandle, long lErrorCode);
public:
SQLHANDLE m_hCon; //數(shù)據(jù)庫連接句柄
protected:
int m_nLoginTimeOut;
int m_nConnectTimeOut;
SQLHANDLE m_hEnv; //環(huán)境句柄
SQLRETURN m_nRet; //返回值
protected:
bool m_bIsConnect; //是否連接數(shù)據(jù)庫
SQLCHAR m_pszLastError[SQL_MAX_MESSAGE_LENGTH+100]; //錯誤信息
bool m_bTraning; //事務(wù)處理是否進(jìn)行中
bool m_bInit; //初始化是否正常
char m_pszDNS[255]; //ODBC DNS名
char m_pszUser[255]; //ODBC 用戶名
char m_pszPwd[255]; //ODBC 用戶口令
};
class CQuery
{
public:
CQuery(CConnection** ppDBCon, int nQueryTimeOut = 3);
virtual ~CQuery();
//取得記錄集的列數(shù).返回值:列數(shù)
unsigned short GetColumnCount();
//取得影響行數(shù),返回值:影響行數(shù)
long GetChangedRowCount(void);
//執(zhí)行指定的sql語句,參數(shù):要執(zhí)行的sql語句,返回值:是否成功
bool ExecuteSQL(const char* pszSQL);
//下一個記錄,返回值:是否成功
bool Fetch();
//前一個記錄,返回值:是否成功
bool FetchPrevious();
//下一個記錄,返回值:是否成功
bool FecthNext();
//當(dāng)Absolute為true是，跳到nRow指定的絕對行，否則，由當(dāng)前位置滾動到參數(shù)FetchOffset指定的相對行，nRow大于0表示向前滾動，nRow小于0表示向后滾動
bool FetchRow(unsigned int nRow, bool bAbsolute = true);
//跳到第一行,返回值:是否成功
bool FetchFirst();
//跳到最后一行,返回值:是否成功
bool FetchLast();
//取消,返回值:是否成功
bool Cancel();
//取得當(dāng)前行的第nColumn列的值。參數(shù)：哪列，接收緩沖區(qū)，接收緩沖區(qū)大小，返回值大小，緩沖區(qū)的C語言類型。返回值:是否成功
bool GetData(unsigned short nColumn, void* pBuffer,
unsigned long nBufLen,
long * nDataLen = NULL,
int nType=SQL_C_DEFAULT);
//取得當(dāng)前行的pszName字段的值。參數(shù)：哪列，接收緩沖區(qū)，接收緩沖區(qū)大小，返回值大小，緩沖區(qū)的C語言類型。返回值:是否成功
bool GetData(const char* pszName, void* pBuffer,
unsigned long nBufLen,
long * nDataLen = NULL,
int nType=SQL_C_DEFAULT);
//關(guān)閉連接，重新執(zhí)行sql語句時，必須先斷開連接，然后在分配句柄才行，否則會包非法的游標(biāo)
void Close();
//取錯誤信息.返回值:錯誤信息
const char* GetLastError(){return (char*)m_pszLastError;}
protected:
//初始化，分配句柄.返回值:是否成功
bool Init();
//取得nColumn列的字段名.參數(shù):哪列,字段名,字段名長度.返回值:是否成功
bool GetColumnName(unsigned short nColumn, char* pszName, short nNameLen);
//取得字段名為pszColumn所在的列,參數(shù):字段名,返回值:字段名所在列
unsigned short GetColumnByName(const char* pszColumn);
//設(shè)置錯誤信息，參數(shù):錯誤信息，錯誤代碼
void SetErrorInfo(const char* pszError, long lErrorCode);
//取得錯誤信息，參數(shù):句柄類型，出錯的句柄，錯誤代碼.返回值:是否成功
bool GetErrorInfo(SQLSMALLINT nHandleType, SQLHANDLE nHandle, long lErrorCode);
//是否成功執(zhí)行了，參數(shù):需要判斷的返回值，句柄類型， 需要判斷的句柄，錯誤代碼,返回值:是否成功
bool IsSuccess(SQLRETURN nRet, SQLSMALLINT nHandleType, SQLHANDLE nHandle, long lErrorCode);
//是否可用.返回值:是否成功
bool IsValid();
protected:
SQLHSTMT m_hStmt; //STMT句柄
SQLRETURN m_nRet; //返回值
private:
CConnection** m_ppDBCon;
protected:
SQLCHAR m_pszLastError[SQL_MAX_MESSAGE_LENGTH+100]; //錯誤信息
int m_nQueryTimeOut; //查詢超時時間
};
class CDBAccess
{
public:
//執(zhí)行SQL語句,參數(shù):要執(zhí)行的SQL語句.返回值:是否執(zhí)行成功
bool ExecuteSQL(const char* pszSQL);
//關(guān)閉到數(shù)據(jù)庫的連接,如果想繼續(xù)使用,必須先調(diào)用Init函數(shù).
void Close();
//重新連接數(shù)據(jù)庫,返回值:是否重連成功
bool ReConnect();
//斷開到數(shù)據(jù)庫的連接.返回值:是否斷開連接
bool Disconnect();
//連接到數(shù)據(jù)庫.參數(shù):DNS名,用戶名,口令.返回值:是否連接到數(shù)據(jù)庫.
bool Connect(const char* pszDNS, const char* pszUser, const char* pszPwd);
//初始化,參數(shù):sql語句保存文件,連接超時,登錄超時.返回值:是否初始化成功.
bool Init(const char* pszSaveSQLFile, CConnection** ppDBCon, int nConnectTimeOut = -1, int nLoginTimeOut = -1, int nQueryTimeOut = 3);
//是否已經(jīng)連到數(shù)據(jù)庫了,返回值,是否連接到數(shù)據(jù)庫
bool IsConnect();
//開始事務(wù)處理,返回值:是否成功
bool BeginTran(){return (*m_ppDBCon)->BeginTran();}
//提交事務(wù)處理,參數(shù):提交類型,返回值:是否成功
bool EndTran(short nAction){return (*m_ppDBCon)->EndTran(nAction);}
//add by stavck at 20051031
//取得當(dāng)前行的cszName字段的值。參數(shù)：哪列，接收緩沖區(qū)，接收緩沖區(qū)大小，返回值大小，緩沖區(qū)的C語言類型。返回值:是否成功
bool GetData(const char* pszName, void* pBuffer, unsigned long nBufLen
, long * nDataLen = NULL, int nType=SQL_C_DEFAULT)
{return m_pQuery->GetData(pszName, pBuffer, nBufLen, nDataLen, nType);}
//下一個記錄,返回值:是否成功
bool Fetch(){return m_pQuery->Fetch();}
//end add
//跳到第一行,返回值:是否成功
bool FetchFirst(){return m_pQuery->FetchFirst();}
//前一個記錄,返回值:是否成功
bool FetchPrevious(){return m_pQuery->FetchPrevious();}
//下一個記錄,返回值:是否成功
bool FecthNext(){return m_pQuery->FecthNext();}
//當(dāng)Absolute為true是，跳到nRow指定的絕對行，否則，由當(dāng)前位置滾動到參數(shù)FetchOffset指定的相對行，nRow大于0表示向前滾動，nRow小于0表示向后滾動
bool FetchRow(unsigned int nRow, bool bAbsolute = true)
{return m_pQuery->FetchRow(nRow, bAbsolute);}
//跳到最后一行,返回值:是否成功
bool FetchLast(){return m_pQuery->FetchLast();}
//取錯誤信息.返回值:錯誤信息
const char* GetLastError(){return (char*)m_pszLastError;}
CDBAccess();
virtual ~CDBAccess();
//add at 20051124
//關(guān)閉語句句柄，因?yàn)橥顺鰰r關(guān)閉會出錯。所以每次數(shù)據(jù)庫操作完畢要使用該函數(shù)來關(guān)閉
void CloseStmt(){m_pQuery->Close();}
protected:
int m_nLoginTimeOut; //登錄超時時間
int m_nConnectTimeOut; //連接超時時間
int m_nQueryTimeOut; //查詢超時時間
char m_pszLastError[255]; //最后誤操作的錯誤信息
CConnection **m_ppDBCon; //數(shù)據(jù)庫連接
CQuery * m_pQuery; //查詢測試任務(wù)時使用,記錄集
protected:
//設(shè)置錯誤信息,參數(shù):錯誤信息,發(fā)生錯誤的位置,是否添加到日志
void SetErrorInfo(const char *pszError, long lErrorCode);
protected:
bool m_bCreateCon; //是否是自己創(chuàng)建的數(shù)據(jù)庫連接
bool m_bEnd; //查詢測試任務(wù)時使用,是否到了最后一條記錄了.
};
#endif // !defined(AFX_QUERY_H__CAEAF203_40C0_4C32_BA76_9A4B0245984B__INCLUDED_)
下面是Query.cpp文件內(nèi)容：
// Query.cpp: implementation of the CQuery class.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "Query.h"
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CConnection::CConnection()
{
memset(m_pszLastError, 0x00, SQL_MAX_MESSAGE_LENGTH+100);
memset(m_pszDNS, 0x00, sizeof(m_pszDNS));
memset(m_pszUser, 0x00, sizeof(m_pszUser));
memset(m_pszPwd, 0x00, sizeof(m_pszPwd));
m_bIsConnect = false;
m_hCon = INVALID_HANDLE_VALUE;
m_hEnv = INVALID_HANDLE_VALUE;
m_bTraning = false;
m_nRet = SQL_SUCCESS;
m_bInit = false;
}
CConnection::~CConnection()
{
try
{
if(m_bTraning)
{//如果沒有手工斷掉連接，這里會進(jìn)行回滾
EndTran(SQL_ROLLBACK);
}
if(IsConnect())
{//如果連接還沒有斷開，那么這里斷開。
Disconnect();
}
if(m_hEnv != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_ENV, m_hEnv);
m_hEnv = INVALID_HANDLE_VALUE;
}
if(m_hCon != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_DBC, m_hCon);
m_hCon = INVALID_HANDLE_VALUE;
}
}
catch (...)
{
//
}
}
bool CConnection::GetErrorInfo(SQLSMALLINT nHandleType, SQLHANDLE nHandle, long lErrorCode)
{//還不完整，要進(jìn)一步寫一下，否則，只能取到最后一行的錯誤信息,不過大部分情況時夠用了.
bool bConnInd = nHandleType == SQL_HANDLE_DBC ? true :false;
SQLRETURN nRet = SQL_SUCCESS;
SQLCHAR pszSqlState[SQL_MAX_MESSAGE_LENGTH] = "";
SQLCHAR pszErrorMsg[SQL_MAX_MESSAGE_LENGTH] = "";
SQLINTEGER nNativeError = 0L;
SQLSMALLINT nErrorMsg = 0;
SQLSMALLINT nRecNmbr = 1;
//執(zhí)行錯誤時ODBC返回的是一個錯誤信息的結(jié)果集，需要遍歷結(jié)果集合中所有行
memset(pszSqlState, 0x00, sizeof(pszSqlState));
memset(pszErrorMsg, 0x00, sizeof(pszErrorMsg));
nRet = SQLGetDiagRec(nHandleType, nHandle,
nRecNmbr, pszSqlState, &nNativeError,
pszErrorMsg, SQL_MAX_MESSAGE_LENGTH - 1,
&nErrorMsg);
SetErrorInfo((char*)pszErrorMsg, lErrorCode);
return true;
}
bool CConnection::IsSuccess(SQLRETURN nRet, SQLSMALLINT nHandleType,
SQLHANDLE nHandle, long lErrorCode)
{
if(nRet == SQL_SUCCESS)
{
return true;
}
else if(nRet == SQL_SUCCESS_WITH_INFO)
{
return true;
}
else
{
GetErrorInfo(nHandleType, nHandle, lErrorCode);
return false;
}
return false;
}
bool CConnection::Init(int nConnectTimeOut /* = -1 */, int nLoginTimeOut /* = -1 */)
{
m_nRet = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_hEnv);
if(IsSuccess(m_nRet, SQL_HANDLE_ENV, m_hEnv, ERROR_CON_INIT_1))
{
//將ODBC設(shè)置成為版本3，否則某些ODBC API 函數(shù)不能被支持。
m_nRet = SQLSetEnvAttr(m_hEnv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
if(!IsSuccess(m_nRet, SQL_HANDLE_ENV, m_hEnv, ERROR_CON_INIT_2))
{
//系統(tǒng)不支持ODBC3
return false;
}
m_nRet = SQLAllocHandle(SQL_HANDLE_DBC, m_hEnv, &m_hCon);
if(!IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_INIT_3))
{
//分配連接句柄不成功！
return false;
}
}
else
{
//分配環(huán)境句柄不成功！
return false;
}
m_nConnectTimeOut = nConnectTimeOut;
m_nLoginTimeOut = nLoginTimeOut;
m_bInit = true;
SetTimeOut(nConnectTimeOut, nLoginTimeOut);
return true;
}
bool CConnection::Connect(const char *pszDNS, const char *pszUser, const char *pszPwd)
{
if(!m_bInit)
{//如果沒有初始化,是不能連接的!
SetErrorInfo("沒有初始化,不能連接到數(shù)據(jù)庫！", ERROR_CON_CONNECT_4);
return false;
}
if(pszDNS == NULL)
{//DNS怎么也不能為空的啊
SetErrorInfo("DNS名為空,無法連接數(shù)據(jù)庫！", ERROR_CON_CONNECT_1);
return false;
}
else
{
strncpy((char*)m_pszDNS, pszDNS, sizeof(m_pszDNS));
}
if(pszUser != NULL)
{
strncpy((char*)m_pszUser, pszUser, sizeof(m_pszUser));
}
else
{
m_pszUser[0] = NULL;
}
if(pszPwd != NULL)
{
strncpy((char*)m_pszPwd, pszPwd, sizeof(m_pszPwd));
}
else
{
m_pszPwd[0] = NULL;
}
try
{
//開始連接
m_nRet = SQLConnect(m_hCon,
(SQLCHAR*)pszDNS, SQL_NTS,
(SQLCHAR*)pszUser, SQL_NTS,
(SQLCHAR*)pszPwd, SQL_NTS);
}
catch (...)
{
SetErrorInfo("連接數(shù)據(jù)庫時發(fā)生錯誤！", ERROR_CON_CONNECT_3);
return false;
}
m_bIsConnect = IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_CONNECT_2);
return m_bIsConnect;
}
void CConnection::SetErrorInfo(const char *pszError, long lErrorCode)
{//先把錯誤代碼填到錯誤信息中,接著填具體的錯誤信息
memset(m_pszLastError, 0x00, sizeof(m_pszLastError));
char pszErrorCode[20] = "";
//先設(shè)置錯誤代碼
sprintf((char*)pszErrorCode, "EC[%ld]", lErrorCode);
int nLen = strlen((char*)pszErrorCode);
strncpy((char*)m_pszLastError, (const char*)pszErrorCode, nLen);
//不能超過了最大長度,要控制一下
size_t nMaxSize = sizeof(m_pszLastError)-1-nLen;
strncat((char*)m_pszLastError, pszError, nMaxSize>strlen(pszError)?strlen(pszError):nMaxSize);
}
bool CConnection::Disconnect()
{
if(!IsConnect())
{//如果沒有連接,只是簡單的返回
return true;
}
if(m_bTraning)
{//事務(wù)處理還沒有提交呢!
SetErrorInfo("必須先提交事務(wù)，才能斷開！", ERROR_CON_DISCONNECT_1);
return false;
}
try
{
//斷開連接
m_nRet = SQLDisconnect(m_hCon);
if(m_hEnv != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_ENV, m_hEnv);
m_hEnv = INVALID_HANDLE_VALUE;
}
if(m_hCon != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_DBC, m_hCon);
m_hCon = INVALID_HANDLE_VALUE;
}
m_bInit = false;
}
catch (...)
{
SetErrorInfo("斷開數(shù)據(jù)庫連接時發(fā)生錯誤！", ERROR_CON_DISCONNECT_3);
m_bIsConnect = false;
return false;
}
//判斷是否成功斷開
m_bIsConnect = !IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_DISCONNECT_2);
return !m_bIsConnect;
}
bool CConnection::BeginTran()
{
if(!IsConnect())
{//如果沒有連接,怎么開始事務(wù)處理!
SetErrorInfo("沒有連接,怎么開始事務(wù)處理?", ERROR_CON_BEGINTRAN_3);
return false;
}
try
{
//這里是設(shè)置手動提交.
m_nRet= SQLSetConnectAttr(m_hCon,
SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_IS_POINTER);
}
catch (...)
{
SetErrorInfo("開始事務(wù)處理時發(fā)生錯誤！", ERROR_CON_BEGINTRAN_2);
return false;
}
//判斷是否成功
m_bTraning = IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_BEGINTRAN_1);
return m_bTraning;
}
bool CConnection::EndTran(short nAction)
{
if(!IsConnect() || !m_bTraning)
{
SetErrorInfo("沒有連接,或者還沒有開始事務(wù)處理!無法提交事務(wù)", ERROR_CON_ENDTRAN_4);
return true;
}
if(nAction != SQL_COMMIT)
{//如果不是提交,都當(dāng)回滾處理
nAction = SQL_ROLLBACK;
}
try
{
//先結(jié)束事務(wù)處理
m_nRet = SQLEndTran(SQL_HANDLE_DBC, m_hCon, nAction);
//判斷是否成功
m_bTraning = !IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_ENDTRAN_1);
//再改成自動提交.
m_nRet= SQLSetConnectAttr(m_hCon,
SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON,
SQL_IS_POINTER);
}
catch (...)
{
SetErrorInfo("結(jié)束事務(wù)處理時發(fā)生錯誤！", ERROR_CON_ENDTRAN_3);
return false;
}
//修改成功了沒
IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_ENDTRAN_2);
return !m_bTraning;
}
bool CConnection::SetTimeOut(int nConnect, int nLogin)
{
bool bRet = false;
if(!m_bInit)
{//如果沒有初始化,不能使用該函數(shù)!
SetErrorInfo("請在初始化后使用該函數(shù)", ERROR_CON_SETTIMEOUT_1);
return bRet;
}
try
{
if(nLogin >= 0)
{//如果是負(fù)數(shù),就不用管啦!
if(m_bIsConnect)
{//連接后,不能設(shè)置登錄超時了.
SetErrorInfo("設(shè)置登錄超時必須在連接前使用該函數(shù)", ERROR_CON_SETTIMEOUT_2);
}
else
{
m_nRet = SQLSetConnectAttr(m_hCon, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)nLogin, SQL_IS_INTEGER);
bRet = IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_SETTIMEOUT_3);
}
}
if(nConnect >= 0)
{//如果是負(fù)數(shù),就不用管啦!
m_nRet = SQLSetConnectAttr(m_hCon, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)nConnect, SQL_IS_INTEGER);
bRet = IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_SETTIMEOUT_4) || bRet;
}
else
{
return bRet;
}
}
catch (...)
{
SetErrorInfo("設(shè)置系統(tǒng)超時時發(fā)生錯誤！", ERROR_CON_SETTIMEOUT_5);
return false;
}
return bRet;
}
bool CConnection::ReConnect()
{
try
{
//斷開連接
SQLDisconnect(m_hCon);
m_bIsConnect = false;
if(m_hEnv != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_ENV, m_hEnv);
m_hEnv = INVALID_HANDLE_VALUE;
}
if(m_hCon != INVALID_HANDLE_VALUE)
{
SQLFreeHandle(SQL_HANDLE_DBC, m_hCon);
m_hCon = INVALID_HANDLE_VALUE;
}
m_bInit = false;
if(!Init(m_nConnectTimeOut, m_nLoginTimeOut))
{
return false;
}
m_bIsConnect = false;
}
catch (...)
{
m_bIsConnect = false;
m_bInit = false;
return false;
}
return Connect(m_pszDNS, m_pszUser, m_pszPwd);
}
//add by stavck at 20051116
bool CConnection::IsConnect()
{
//如果連接已經(jīng)手工斷開了，這里就沒有必要再檢查了。
if(!m_bIsConnect)
{
return false;
}
else
{
return true;
}
//下面代碼會在數(shù)據(jù)庫重連后產(chǎn)生錯誤，刪除。
SQLINTEGER lRet = SQL_CD_TRUE;
SQLINTEGER lRetSize = 0;
try
{
//判斷連接是否活著，不過斷開時一定要等到對數(shù)據(jù)庫有SQL請求后才有可以。
m_nRet = SQLGetConnectAttr(m_hCon, SQL_ATTR_CONNECTION_DEAD, (SQLPOINTER)&lRet, SQL_IS_INTEGER, &lRetSize);
if(!IsSuccess(m_nRet, SQL_HANDLE_DBC, m_hCon, ERROR_CON_ISCONNECT_1))
{
return false;
}
}
catch (...)
{
SetErrorInfo("查詢系統(tǒng)連接時發(fā)生錯誤！", ERROR_CON_ISCONNECT_2);
return false;
}
//連接仍然是活動的
if(lRet == SQL_CD_FALSE)
{
return true;
}
//連接已經(jīng)斷掉了
return false;
}
//////////////////////////////////////////////////////////////////////////
CQuery::CQuery(CConnection** ppDBCon, int nQueryTimeOut /* = 3 */)
{
memset(m_pszLastError, 0x00, SQL_MAX_MESSAGE_LENGTH+100);
m_nQueryTimeOut = 3;
m_ppDBCon = ppDBCon;
m_nRet = SQL_SUCCESS;
m_hStmt = INVALID_HANDLE_VALUE;
m_nQueryTimeOut = nQueryTimeOut;
}
CQuery::~CQuery()
{
Close();
}
bool CQuery::Init()
{
try
{
if (m_ppDBCon == NULL || *m_ppDBCon == NULL ||!(*m_ppDBCon)->IsConnect())
{
return false;
}
//分配SQL語句句柄
m_nRet = SQLAllocHandle( SQL_HANDLE_STMT,
(*m_ppDBCon)->m_hCon, &m_hStmt );
if(!IsSuccess(m_nRet, SQL_HANDLE_DBC,
(*m_ppDBCon)->m_hCon, ERROR_QUERY_INIT_1))
{
m_hStmt = INVALID_HANDLE_VALUE;
return false;
}
//指定要使用的游標(biāo)并發(fā)級別
m_nRet = SQLSetStmtAttr(m_hStmt, SQL_ATTR_CONCURRENCY,
(SQLPOINTER) SQL_CONCUR_ROWVER, 0);
if(!IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_INIT_2))
{
Close();
return false;
}
//設(shè)置光標(biāo)類型為鍵集光標(biāo),
//鍵集光標(biāo)能夠檢測到行的刪除和修改，但是無法檢測到檢測到行的添加和結(jié)果集順序變化。
//因?yàn)樵诠鈽?biāo)創(chuàng)建時就創(chuàng)建了整個結(jié)果集，結(jié)果集合中記錄和順序已經(jīng)被固定，
//這一點(diǎn)和靜態(tài)光標(biāo)一樣。所以鍵集光標(biāo)可以說是一種介于靜態(tài)光標(biāo)和動態(tài)光標(biāo)之間的光標(biāo)類型。
m_nRet = SQLSetStmtAttr(m_hStmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)
SQL_CURSOR_DYNAMIC /* SQL_CURSOR_KEYSET_DRIVEN*/, 0);
if(!IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_INIT_3))
{
Close();
return false;
}
m_nRet = SQLSetStmtAttr(m_hStmt, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)m_nQueryTimeOut, SQL_IS_UINTEGER);
if(!IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_INIT_5))
{
Close();
return false;
}
/*#ifdef _DEBUG
SQLINTEGER dummy;
m_nRet = SQLGetStmtAttr(m_hStmt, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)&lTimeOut, SQL_IS_UINTEGER, &dummy);
if(IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_INIT_5))
{
printf("query time out is: %ld\n\r", lTimeOut);
}
#endif*/
}
catch (...)
{
SetErrorInfo("初始化時發(fā)生錯誤！", ERROR_QUERY_INIT_4);
Close();
return false;
}
return true;
}
void CQuery::Close()
{
try
{
if(m_hStmt != INVALID_HANDLE_VALUE)
{
//釋放句柄
//SQLFreeStmt(m_hStmt, SQL_DROP);
SQLFreeHandle(SQL_HANDLE_STMT,m_hStmt);
m_hStmt = INVALID_HANDLE_VALUE;
}
}
catch (...)
{
m_hStmt = INVALID_HANDLE_VALUE;
}
}
bool CQuery::GetErrorInfo(SQLSMALLINT nHandleType, SQLHANDLE nHandle,
long lErrorCode)
{//還不完整，要進(jìn)一步寫一下，否則，只能取到最后一行的錯誤信息。
bool bConnInd = nHandleType == SQL_HANDLE_DBC ? true :false;
SQLRETURN nRet = SQL_SUCCESS;
SQLCHAR pszSqlState[SQL_MAX_MESSAGE_LENGTH] = "";
SQLCHAR pszErrorMsg[SQL_MAX_MESSAGE_LENGTH] = "";
SQLINTEGER nNativeError = 0L;
SQLSMALLINT nErrorMsg = 0;
SQLSMALLINT nRecNmbr = 1;
//執(zhí)行錯誤時ODBC返回的是一個錯誤信息的結(jié)果集，需要遍歷結(jié)果集合中所有行
memset(pszSqlState, 0x00, sizeof(pszSqlState));
memset(pszErrorMsg, 0x00, sizeof(pszErrorMsg));
nRet = SQLGetDiagRec(nHandleType, nHandle,
nRecNmbr, pszSqlState, &nNativeError,
pszErrorMsg, SQL_MAX_MESSAGE_LENGTH - 1,
&nErrorMsg);
SetErrorInfo((char*)pszErrorMsg, lErrorCode);
return true;
}
bool CQuery::IsSuccess(SQLRETURN nRet, SQLSMALLINT nHandleType,
SQLHANDLE nHandle, long lErrorCode)
{
if(nRet == SQL_SUCCESS)
{
return true;
}
else if(nRet == SQL_SUCCESS_WITH_INFO)
{//表明執(zhí)行成功但是帶有一定錯誤信息,此時不應(yīng)該記錄到log中,否則log會與日俱增
//GetErrorInfo(nHandleType, nHandle, lErrorCode, false);
return true;
}
else
{
GetErrorInfo(nHandleType, nHandle, lErrorCode);
return false;
}
return false;
}
void CQuery::SetErrorInfo(const char *pszError, long lErrorCode)
{//先把錯誤代碼填到錯誤信息中,接著填具體的錯誤信息
memset(m_pszLastError, 0x00, sizeof(m_pszLastError));
char pszErrorCode[20] = "";
//先設(shè)置錯誤代碼
sprintf((char*)pszErrorCode, "EC[%ld]", lErrorCode);
int nLen = strlen((char*)pszErrorCode);
strncpy((char*)m_pszLastError, (const char*)pszErrorCode, nLen);
//不能超過了最大長度,要控制一下
size_t nMaxSize = sizeof(m_pszLastError)-1-nLen;
strncat((char*)m_pszLastError, pszError,
nMaxSize>strlen(pszError)?strlen(pszError):nMaxSize);
}
bool CQuery::IsValid()
{
return m_hStmt != INVALID_HANDLE_VALUE;
}
unsigned short CQuery::GetColumnCount()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_GETCOLCOUNT_1);
return 0;
}
short nCols=0;
try
{
if(!IsSuccess(m_nRet = SQLNumResultCols(m_hStmt, &nCols),
SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_GETCOLCOUNT_2))
{
//如果不成功,返回0個
return 0;
}
}
catch (...)
{
SetErrorInfo("取得列個數(shù)時發(fā)生錯誤！", ERROR_QUERY_GETCOLCOUNT_3);
Close();
return 0;
}
return nCols;
}
long CQuery::GetChangedRowCount()
{//對select語句是無效的,請選擇使用
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_GETCROWCOUNT_1);
return 0;
}
long nRows=0;
try
{
if(!IsSuccess(m_nRet = SQLRowCount(m_hStmt,&nRows),
SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_GETCROWCOUNT_2))
{
return 0;
}
}
catch (...)
{
SetErrorInfo("取得影響記錄集的個數(shù)數(shù)時發(fā)生錯誤！", ERROR_QUERY_GETCROWCOUNT_3);
Close();
return 0;
}
return nRows;
}
bool CQuery::ExecuteSQL(const char* pszSQL)
{
//因?yàn)橐粋€語句句柄只能執(zhí)行一次sql語句,所有,得先釋放才能執(zhí)行
if(IsValid())
{//如果有效,先關(guān)閉
Close();
}
if(!Init())
{//再初始化
return false;
}
if(!IsValid())
{//這時不能用?
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_EXECSQL_1);
return false;
}
try
{
//執(zhí)行相應(yīng)的sql語句
m_nRet = SQLExecDirect(m_hStmt, (SQLTCHAR *)pszSQL, SQL_NTS);
}
catch (...)
{
SetErrorInfo("執(zhí)行SQL語句出錯!", ERROR_QUERY_EXECSQL_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_EXECSQL_2);
}
bool CQuery::Fetch()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCH_1);
return false;
}
try
{
m_nRet = SQLFetch(m_hStmt);
}
catch (...)
{
SetErrorInfo("Fetch出錯!", ERROR_QUERY_FETCH_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCH_2);
}
bool CQuery::FecthNext()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCHNEXT_1);
return false;
}
try
{
m_nRet = SQLFetchScroll(m_hStmt, SQL_FETCH_NEXT, 0);
}
catch (...)
{
SetErrorInfo("FecthNext出錯!", ERROR_QUERY_FETCHNEXT_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCHNEXT_2);
}
bool CQuery::FetchPrevious()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCHPRE_1);
return false;
}
try
{
m_nRet = SQLFetchScroll(m_hStmt, SQL_FETCH_PRIOR, 0);
}
catch (...)
{
SetErrorInfo("FetchPrevious出錯!", ERROR_QUERY_FETCHPRE_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCHPRE_2);
}
bool CQuery::FetchFirst()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCHFIRST_1);
return false;
}
try
{
m_nRet = SQLFetchScroll(m_hStmt, SQL_FETCH_FIRST, 0);
}
catch (...)
{
SetErrorInfo("FetchFirst出錯!", ERROR_QUERY_FETCHFIRST_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCHFIRST_2);
}
bool CQuery::FetchLast()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCHLAST_1);
return false;
}
try
{
m_nRet = SQLFetchScroll(m_hStmt,SQL_FETCH_LAST,0);
}
catch (...)
{
SetErrorInfo("FetchLast出錯!", ERROR_QUERY_FETCHLAST_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCHLAST_2);
}
bool CQuery::FetchRow(unsigned int nRow, bool bAbsolute /* = true */)
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_FETCHROW_1);
return false;
}
try
{
m_nRet = SQLFetchScroll(m_hStmt,
(bAbsolute ? SQL_FETCH_ABSOLUTE : SQL_FETCH_RELATIVE),
nRow);
}
catch (...)
{
SetErrorInfo("FetchRow出錯!", ERROR_QUERY_FETCHROW_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_FETCHROW_2);
}
bool CQuery::Cancel()
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_CANCEL_1);
return true;
}
try
{
m_nRet = SQLCancel(m_hStmt);
}
catch (...)
{
SetErrorInfo("Cancel出錯!", ERROR_QUERY_CANCEL_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_CANCEL_2);
}
bool CQuery::GetData(unsigned short nColumn, void* pBuffer,
unsigned long nBufLen,
long * nDataLen /* = NULL */,
int nType/* =SQL_C_DEFAULT */)
{
if(nColumn <= 0 || nColumn > GetColumnCount() || pBuffer == NULL)
{
SetErrorInfo("列范圍不對，或者pBuffer為空！", ERROR_QUERY_GETDATA_1);
return false;
}
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_GETDATA_2);
return true;
}
SQLINTEGER nOutLen = 0;
try
{
m_nRet = SQLGetData(m_hStmt, nColumn, nType, pBuffer, nBufLen, &nOutLen);
}
catch (...)
{
SetErrorInfo("GetData出錯!", ERROR_QUERY_GETDATA_5);
Close();
return false;
}
if(!IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_GETDATA_3))
{
return false;
}
if(nDataLen)
{
*nDataLen=nOutLen;
}
return true;
}
bool CQuery::GetData(const char* pszName, void* pBuffer,
unsigned long nBufLen,
long * nDataLen /* = NULL */,
int nType/* =SQL_C_DEFAULT */)
{
if(pszName == NULL || pBuffer == NULL)
{
SetErrorInfo("PszName or pBuffer 不能為空！", ERROR_QUERY_GETDATA_4);
return false;
}
unsigned short nColumn = GetColumnByName(pszName);
//有效性判斷讓GetData自己判斷
return GetData(nColumn, pBuffer, nBufLen, nDataLen, nType);
}
unsigned short CQuery::GetColumnByName(const char *pszColumn)
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_GETCOLBYNAME_1);
return true;
}
unsigned short nCols = GetColumnCount();
for(unsigned short i = 1; i < (nCols+1) ; i++)
{//依次得到每個列的字段名，然后比較。
TCHAR pszName[256] = "";
if(GetColumnName(i, pszName, sizeof(pszName)))
{
if(stricmp(pszName, pszColumn) == 0)
{
return i;
}
}
}
return 0;
}
bool CQuery::GetColumnName(unsigned short nColumn, char *pszName, short nNameLen)
{
if(!IsValid())
{
SetErrorInfo("STMT句柄不可用！", ERROR_QUERY_GETCOLNAME_1);
return true;
}
int nType = SQL_C_DEFAULT;
SQLSMALLINT nSwCol=0, nSwType=0, nSwScale=0, nSwNull=0; //這些數(shù)據(jù)，在這個函數(shù)中，我們不用關(guān)心
SQLUINTEGER pcbColDef=0;
try
{
m_nRet = SQLDescribeCol( m_hStmt, nColumn,
(SQLTCHAR*)pszName, nNameLen,
&nSwCol, &nSwType, &pcbColDef,
&nSwScale, &nSwNull);
}
catch (...)
{
SetErrorInfo("取字段名稱時出錯!", ERROR_QUERY_GETCOLNAME_3);
Close();
return false;
}
return IsSuccess(m_nRet, SQL_HANDLE_STMT, m_hStmt, ERROR_QUERY_GETCOLNAME_2);
}
//////////////////////////////////////////////////////////////////////////
CDBAccess::CDBAccess()
{
m_ppDBCon = NULL;
memset(m_pszLastError, 0x00, sizeof(m_pszLastError));
m_nConnectTimeOut = -1;
m_nLoginTimeOut = -1;
m_pQuery = NULL;
m_bEnd = false;
m_bCreateCon = false;
}
CDBAccess::~CDBAccess()
{
if(m_pQuery != NULL)
{
delete m_pQuery;
m_pQuery = NULL;
}
}
void CDBAccess::SetErrorInfo(const char *pszError, long lErrorCode)
{//先把錯誤代碼填到錯誤信息中,接著填具體的錯誤信息
memset(m_pszLastError, 0x00, sizeof(m_pszLastError));
char pszErrorCode[20] = "";
//先設(shè)置錯誤代碼
sprintf((char*)pszErrorCode, "EC[%ld]", lErrorCode);
int nLen = strlen((char*)pszErrorCode);
strncpy((char*)m_pszLastError, (const char*)pszErrorCode, nLen);
//不能超過了最大長度,要控制一下
size_t nMaxSize = sizeof(m_pszLastError)-1-nLen;
strncat((char*)m_pszLastError, pszError, nMaxSize>strlen(pszError)?strlen(pszError):nMaxSize);
}
bool CDBAccess::Init(const char* pszSaveSQLFile, CConnection** ppDBCon,
int nConnectTimeOut /* = -1 */, int nLoginTimeOut /* = -1 */,
int nQueryTimeOut /* = 3 */)
{
m_nLoginTimeOut = nLoginTimeOut;
m_nConnectTimeOut = nConnectTimeOut;
m_nQueryTimeOut = nQueryTimeOut;
m_ppDBCon = ppDBCon;
if((*ppDBCon) != NULL)
{
return true;
}
try
{
(*m_ppDBCon) = new CConnection;
if ((*m_ppDBCon) == NULL)
{
SetErrorInfo("沒有足夠的內(nèi)存!", ERROR_DBACCESS_INIT_1);
return false;
}
m_bCreateCon = true;
if(!(*m_ppDBCon)->Init(nConnectTimeOut, nLoginTimeOut))
{
delete (*m_ppDBCon);
(*m_ppDBCon) = NULL;
SetErrorInfo("數(shù)據(jù)庫連接初始化失敗!", ERROR_DBACCESS_INIT_2);
m_bCreateCon = false;
return false;
}
}
catch (...)
{
SetErrorInfo("初始化時出錯!", ERROR_DBACCESS_INIT_3);
return false;
}
m_pQuery = new CQuery(m_ppDBCon, m_nQueryTimeOut);
if(m_pQuery == NULL)
{
SetErrorInfo("沒有足夠的內(nèi)存!", ERROR_DBACCESS_INIT_4);
return false;
}
return true;
}
bool CDBAccess::Disconnect()
{
if((*m_ppDBCon) == NULL)
{
SetErrorInfo("重新連接數(shù)據(jù)庫時出錯!,請稍后再試!", ERROR_DBACCESS_DISCONNECT_1);
return false;
}
//注意,這里試著回滾了,請確保事務(wù)已經(jīng)被提交.
if((*m_ppDBCon)->IsInTran())
{
(*m_ppDBCon)->EndTran(SQL_ROLLBACK);
}
return (*m_ppDBCon)->Disconnect();
}
bool CDBAccess::Connect(const char *pszDNS, const char *pszUser, const char *pszPwd)
{
if((*m_ppDBCon) == NULL)
{
SetErrorInfo("還沒有初始化,請重新初始化后再試!", ERROR_DBACCESS_CONNECT_1);
return false;
}
if(pszDNS == NULL)
{
SetErrorInfo("DNS不能為空!", ERROR_DBACCESS_CONNECT_2);
return false;
}
return (*m_ppDBCon)->Connect(pszDNS, pszUser, pszPwd);
}
bool CDBAccess::ReConnect()
{
//add at 20051124 for 關(guān)閉前，先試著關(guān)閉語句句柄，以免數(shù)據(jù)庫關(guān)閉后，語句句柄無效！
m_pQuery->Close();
return (*m_ppDBCon)->ReConnect();
}
bool CDBAccess::IsConnect()
{
if((*m_ppDBCon) == NULL)
{
return false;
}
else
{
return (*m_ppDBCon)->IsConnect();
}
}
void CDBAccess::Close()
{
try
{
if(m_pQuery != NULL)
{
delete m_pQuery;
m_pQuery = NULL;
}
if(m_bCreateCon)
{
if((*m_ppDBCon) != NULL)
{
//強(qiáng)制斷開,很霸道的,一定要保證所有的事務(wù)都提交在斷開!
delete (*m_ppDBCon);
(*m_ppDBCon) = NULL;
}
m_bCreateCon = false;
}
}
catch (...)
{
//
}
}
bool CDBAccess::ExecuteSQL(const char *pszSQL)
{
if(!IsConnect())
{
SetErrorInfo("沒有連接到數(shù)據(jù)庫,無法執(zhí)行SQL語句!", ERROR_DBACCESS_EXECUTESQL_1);
return false;
}
bool bRet = false;
try
{ if(m_pQuery == NULL)
{
m_pQuery = new CQuery((m_ppDBCon), m_nQueryTimeOut);
if(m_pQuery == NULL)
{
SetErrorInfo("沒有足夠的內(nèi)存!", ERROR_DBACCESS_EXECUTESQL_2);
return false;
}
}
if (!m_pQuery->ExecuteSQL(pszSQL))
{
SetErrorInfo(m_pQuery->GetLastError(), ERROR_DBACCESS_EXECUTESQL_3);
bRet = false;
}
else
{
bRet = true;
}
}
catch (...)
{
SetErrorInfo("執(zhí)行指定的SQL語句時出錯!", ERROR_DBACCESS_EXECUTESQL_5);
return false;
}
return bRet;
}