|
/*-- 專項技術學習 －數據庫訪問技術之ODBC API
這個類 通過ODBC API 函數訪問ODBC 數據源的數據表
功能簡介:能夠幫定一個臨時指定表,調用類的函數即可對該表
插入、增加、修改、簡單統計等等操作(注:通過動態改變表的信息:表名字、
表字段總數、字段名字、字段類型、字段是否為數值、字段是否為主鍵。這樣
就可以訪問ODBC 數據源的所有表了)
另外提供一個函數 可以對該數據源的其他表（包括幫定的表）進行
刪除、修改功能
LSZ 2001.08.25 */
/*頭文件*/
#ifndef _WDC_DATABASE_H
#define _WDC_DATABASE_H
#include <sql.h>
#include <sqlext.h>
#define MAX_FIELDS 500
#define MAX_ERROR_MSG 1024
#define MAX_DATA 500
class CWdcDatabase
{
public:
CWdcDatabase();
virtual ~CWdcDatabase();
BOOL Open(const CString& sDSN,
const CString& sTable,
const CString& sUserId,
const CString& sPassword) ; /*打開一個ODBC數據源數據庫*/
public:
BOOL m_bOpen; /*標志數據庫是否打開*/
BOOL m_bBrackets; /*標識表名、字段名是否用方擴號擴起來*/
int m_nError; /*ODBC錯誤返回代碼*/
CString m_sQuery; /*操作ODBC 數據源公用的語句，用于方便構造*/
CString m_sErrMsg; /*ODBC錯誤返回信息*/
CString m_sDSN; /*ODBC 數據源的 DSN*/
CString m_sTable; /*臨時綁定的表名*/
public:
void DropHstmtSelect(); /*確保公共查詢的語句句柄釋放*/
long GetRecordCount(CString &sSel); /*取得符合條件的記錄數*/
BOOL NextMatchingRecord(); /*查詢符合條件的下一條記錄*/
int SelectMatchingRecords(CString& sWhereContext); /*查詢符合條件的記錄*/
BOOL UpdateRecord(); /*更新臨時綁定的表的一條記錄*/
BOOL AddRecord(); /*增加臨時綁定表一條新記錄*/
BOOL DeleteRecord(CString &sWhereDelete); /*刪除臨時綁定表符合條件記錄*/
BOOL Execute(const CString &sText); /*執行一個SQL語句，注:可以執行存儲過程*/
void Close(); /*關閉數據庫*/
public:
int m_nFields; /*臨時綁定表字段的總數*/
CString m_sFieldName[MAX_FIELDS]; /*臨時綁定表字段名字*/
CString m_sFieldValue[MAX_FIELDS];/*臨時綁定表字段值*/
BOOL m_bKeyField[MAX_FIELDS];/*臨時綁定表字段是否為主鍵*/
BOOL m_bNumeric[MAX_FIELDS];/*臨時綁定表字段是否為數值類型*/
private:
CString Quote(CString &sQuote); /*字符串替換*/
RETCODE rc; /*ODBC API 涵數執行返回碼*/
HSTMT hstmt, hstmt_select; /*公共語句句柄*/
unsigned char szDSN[200]; /*連接數據源的 DSN */
unsigned char szID[200]; /*連接數據源的 UID */
unsigned char szPassword[200]; /*連接數據源的 PWD*/
unsigned char szQuery[3000]; /*公共執行的語句句柄*/
unsigned char szTemp[1000]; /*公共使用的字符串*/
CString sTemp ; /*公共使用的字符串*/
HENV henv; /*環境句柄*/
HDBC hdbc; /*ODBC 連接句柄*/
};
#endif
/*實現文件*/
#include "stdafx.h"
#include "WdcDatabase.h"
#include <sql.h>
#include <sqlext.h>
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
CWdcDatabase::CWdcDatabase()
{
m_nError = 0;
m_bOpen = false;
m_nFields = 0;
m_bBrackets = false;
}
CWdcDatabase::~CWdcDatabase()
{
if(m_bOpen)
{
Close() ;
}
}
BOOL CWdcDatabase::Open(const CString& sDSN,
const CString& sTable,
const CString& sUserId,
const CString& sPassword)
{
if(m_bOpen)
{
Close() ;
}
hstmt_select = NULL ;
RETCODE rc ;
CString stemp ;
memset(szDSN,0x00,200) ;
memset(szID,0x00,200) ;
memset(szPassword,0x00,200) ;
/*Open Database */
SQLAllocEnv(&henv) ;
SQLAllocConnect(henv,&hdbc) ;
strcpy((char*)szDSN,sDSN) ;
stemp = sUserId ;
strcpy((char*)szID,stemp) ;
stemp = sPassword ;
strcpy((char*)szPassword,stemp) ;
m_sDSN = sDSN ;
m_sTable = sTable ;
rc = SQLConnect(hdbc,
szDSN,
SQL_NTS,
szID,
SQL_NTS,
szPassword,
SQL_NTS);
m_nError = (int) rc ;
if((rc == SQL_SUCCESS ) ||(rc == SQL_SUCCESS_WITH_INFO))
{
m_bOpen = TRUE ;
TRACE0("Open database Succeed !\n") ;
return TRUE ;
}
else
{
m_bOpen = FALSE ;
TRACE0("Open Database Failed !\n") ;
return FALSE ;
}
}
void CWdcDatabase::Close()
{
if(m_bOpen)
{
if(hstmt_select)
{
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
}
SQLDisconnect(hdbc) ; /*Dis hdbc*/
SQLFreeConnect(hdbc) ; /*Free Odbc*/
SQLFreeEnv(henv) ; /*Free henv*/
m_bOpen = false;
TRACE0("Close database Succeed !\n") ;
}
}
BOOL CWdcDatabase::Execute(const CString &sSQL)
{
RETCODE rc ;
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG + 1] ;
SWORD cbmsg ;
m_sErrMsg = "" ;
m_sQuery = sSQL ;
if(!m_bOpen)
return FALSE ;
SQLAllocStmt(hdbc,&hstmt) ;
memset((char*)szQuery,0x00,sizeof(szQuery)) ;
strcpy((char*)szQuery,m_sQuery) ;
rc = SQLExecDirect(hstmt,
szQuery,
SQL_NTS) ;
m_nError = (int) rc ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
SQLTransact(henv,hdbc,SQL_COMMIT);
SQLFreeStmt(hstmt,SQL_DROP) ;
return TRUE ;
}
else
{
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt,
szSQLSTATE,
&nErr,
msg,
sizeof(msg),
&cbmsg) == SQL_SUCCESS)
m_sErrMsg = "\t\t" + (CString)msg + "\n" ;
SQLFreeStmt(hstmt,SQL_DROP) ;
return FALSE ;
}
}
/*帶入條件為 Where 以后的條件*/
BOOL CWdcDatabase::DeleteRecord(CString &sWhereDelete)
{
RETCODE rc ;
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG+1] ;
SWORD cbmsg ;
CString sTemp ;
if(!m_bOpen)
return FALSE ;
m_sQuery = "" ;
m_sErrMsg = "" ;
/*開始構造刪除語句*/
m_sQuery.Format("Delete From %s ",m_sTable) ;
/* int ff =0 ;
for(int f= 0 ;f<m_nFields;f++)
{
if(m_bKeyField[f]) /*如果是表的主鍵
{
if(ff>0) /*如果主鍵超過2個,要把條件連接起來
m_sQuery += " AND " ;
ff++ ;
if (m_bNumeric[f])
sTemp.Format("%s=%s",m_sFieldName[f],m_sFieldValue[f]) ;
else
sTemp.Format("%s=%s",m_sFieldName[f],Quote(m_sFieldValue[f])) ;
m_sQuery += sTemp ;
}
}
*/
if(sWhereDelete !="")
m_sQuery +=" Where " + sWhereDelete ;
TRACE0(m_sQuery) ;
memset((char*)szQuery,0x00,sizeof(szQuery)) ;
strcpy((char*)szQuery,m_sQuery) ;
SQLAllocStmt(hdbc,&hstmt) ;
rc = SQLExecDirect(hstmt,
szQuery,
SQL_NTS) ;
m_nError = (int) rc ;
if(rc ==SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{ /*如果執行成功*/
SQLTransact(henv,hdbc,SQL_COMMIT);
SQLFreeStmt(hstmt,SQL_DROP) ;
return TRUE ;
}
else
{
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt,
szSQLSTATE,
&nErr,
msg,
sizeof(msg),
&cbmsg) == SQL_SUCCESS)
m_sErrMsg = "\t\t" + (CString) msg + "\n" ;
SQLFreeStmt(hstmt,SQL_DROP) ;
return FALSE ;
}
}
CString CWdcDatabase::Quote(CString &sText)
{
CString sResult="" ;
CString sChar ;
int iTextLen = sText.GetLength() ;
for(int pos = 0 ;pos<iTextLen;pos++)
{
sChar = sText.Mid(pos,1) ;
if (sChar == "'")
sChar = "''" ;
sResult += sChar ;
}
return CString("'" + sResult + "'") ;
}
BOOL CWdcDatabase::AddRecord()
{
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG+1] ;
SWORD cbmsg ;
CString sTemp ;
if(!m_bOpen)
return FALSE ;
m_sQuery.Format("Insert Into %s(",m_sTable) ;
int ff=0 ;
for(int f=0 ;f<m_nFields;f++)
{
if(ff>0)
m_sQuery += "," ;
ff ++ ;
m_sQuery += m_sFieldName[f] ;
}
m_sQuery += ") values(" ;
ff = 0 ;
for(f=0 ;f<m_nFields ;f++)
{
if(ff>0)
m_sQuery += "," ;
ff ++ ;
/*---如果數據不為空*/
if(m_sFieldValue[f]!="")
{
if(m_bNumeric[f])
sTemp.Format("%s",m_sFieldValue[f]) ;
else
sTemp.Format("%s",Quote(m_sFieldValue[f])) ;
}
else
{ /*如果數據為空*/
sTemp.Format("%s","NULL") ;
}
m_sQuery += sTemp ;
}
m_sQuery += ")" ;
memset((char*)szQuery,0x00,sizeof(szQuery)) ;
strcpy((char*)szQuery,m_sQuery) ;
SQLAllocStmt(hdbc,&hstmt) ;
rc = SQLExecDirect(hstmt,
szQuery,
SQL_NTS) ;
m_nError = (int) rc ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
SQLTransact(henv,hdbc,SQL_COMMIT);
SQLFreeStmt(hstmt,SQL_DROP) ;
hstmt=NULL ;
return TRUE ;
}
else
{
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt,
szSQLSTATE,
&nErr,
msg,
sizeof(msg),
&cbmsg) == SQL_SUCCESS )
m_sErrMsg += "\t\t" + (CString) msg +"\n" ;
SQLFreeStmt(hstmt,SQL_DROP) ;
return FALSE ;
}
}
/*更新表的指定紀錄*/
BOOL CWdcDatabase::UpdateRecord()
{
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG + 1] ;
SWORD cbmsg ;
RETCODE rc ;
CString sTemp ;
if(!m_bOpen)
return FALSE ;
m_sQuery.Format("Update %s Set ",m_sTable) ;
int ff = 0 ;
for(int f= 0 ;f<m_nFields ;f++)
{
if (ff>0)
m_sQuery += "," ;
ff ++ ;
if(m_sFieldValue[f]!="") /*數據為空*/
{
if(m_bNumeric[f])
sTemp.Format("%s=%s",m_sFieldName[f],m_sFieldValue[f]) ;
else
sTemp.Format("%s=%s",m_sFieldName[f],Quote(m_sFieldValue[f])) ;
}
else
sTemp.Format("%s=%s",m_sFieldName[f],NULL) ;
m_sQuery += sTemp ;
}
m_sQuery += " Where " ;
ff = 0 ;
for(f=0 ;f<m_nFields ;f++)
{
if(m_bKeyField[f]) /*如果為主鍵*/
{
if(ff>0)
m_sQuery += "," ;
ff ++ ;
if(m_bNumeric[f])
sTemp.Format("%s=%s",m_sFieldName[f],m_sFieldValue[f]) ;
else
sTemp.Format("%s=%s",m_sFieldName[f],Quote(m_sFieldValue[f])) ;
m_sQuery += sTemp ;
}
}
memset((char*)szQuery,0x00,sizeof(szQuery)) ;
strcpy((char*)szQuery,m_sQuery) ;
SQLAllocStmt(hdbc,
&hstmt) ;
rc = SQLExecDirect(hstmt,
szQuery,
SQL_NTS) ;
m_nError = (int) rc ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
SQLTransact(henv,hdbc,SQL_COMMIT);
SQLFreeStmt(hstmt,SQL_DROP) ;
return TRUE ;
}
else
{
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt,
szSQLSTATE,
&nErr,
msg,
sizeof(msg) ,
&cbmsg ) == SQL_SUCCESS )
m_sErrMsg += "\t\t" +(CString) msg + "\n" ;
SQLFreeStmt(hstmt,SQL_DROP) ;
return FALSE ;
}
}
/* 查找紀錄---
返回值
1： 即沒有錯誤發生的找到紀錄
-1： 在查找過程中出現錯誤
0： 查找過程當中沒有錯誤出現，但是沒有找到適合條件的紀錄，即沒有返回紀錄 */
int CWdcDatabase::SelectMatchingRecords(CString &sWhereContext)
{
unsigned char szSQLSTATE[6];
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG+1] ;
SWORD cbmsg ;
CString sTemp ;
char szData[MAX_DATA] ;
SDWORD cbData ;
RETCODE rc ;
if(!m_bOpen)
return -1 ;
sTemp="" ;
int ff=0 ;
for(int j=0;j<m_nFields;j++)
{
if(ff>0)
sTemp += "," ;
ff ++ ;
sTemp += m_sFieldName[j] ;
}
if(sWhereContext == "")
m_sQuery.Format("Select %s from %s",sTemp,m_sTable) ;
else
m_sQuery.Format("Select %s From %s Where %s",sTemp,m_sTable,sWhereContext) ;
SQLAllocStmt(hdbc,&hstmt_select) ;
memset((char*)szQuery,0x00,sizeof(szQuery)) ;
strcpy((char*)szQuery,m_sQuery) ;
rc = SQLExecDirect(hstmt_select,
szQuery,
SQL_NTS) ;
m_nError = (int) rc ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
rc = SQLFetch(hstmt_select );
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{ /*執行成功，并且有數據*/
for(int f = 0 ; f<m_nFields ; f++)
{
rc = SQLGetData(hstmt_select,
f+1 ,
SQL_C_CHAR,
szData,
sizeof(szData) ,
&cbData ) ;
if(cbData != SQL_NULL_DATA)
{ /*如果數據不為空*/
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
m_sFieldValue[f] = CString(szData) ;
else
m_sFieldValue[f] = "" ;
}
else
m_sFieldValue[f] = "" ;
}
return 1 ; /*執行成功，并且有數據*/
}
else if(rc == 100)
{ /*查找成功，但是沒有數據*/
return 0 ;
}
}
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt_select,
szSQLSTATE,
&nErr,
msg,
sizeof(msg),
&cbmsg) == SQL_SUCCESS )
m_sErrMsg += "\t\t" +CString(msg) + "\n" ;
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
return -1 ;
}
BOOL CWdcDatabase::NextMatchingRecord()
{
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char msg[MAX_ERROR_MSG+1] ;
SWORD cbmsg ;
char szData[MAX_DATA] ;
SDWORD cbData ;
RETCODE rc ;
if(!m_bOpen)
return FALSE ;
rc = SQLFetch(hstmt_select) ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
for(int f =0 ;f<m_nFields ; f++)
{
rc = SQLGetData(hstmt_select,
f+1 ,
SQL_C_CHAR,
szData,
sizeof(szData),
&cbData) ;
if(cbData !=SQL_NULL_DATA)
{ /*如果數據不為空*/
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
m_sFieldValue[f] = (CString) szData ;
else
m_sFieldValue[f] = "" ;
}
else
m_sFieldValue[f] = "" ;
}
return TRUE ;
}
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt_select,
szSQLSTATE,
&nErr,
msg,
sizeof(msg),
&cbmsg )==SQL_SUCCESS )
m_sErrMsg += "\t\t" +CString (msg) + "\n" ;
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
return FALSE ;
}
/*統計符合條件的紀錄總數
返回值
-1: 執行出錯
>0: 執行成功,并且返回統計結果 */
long CWdcDatabase::GetRecordCount(CString &sSel)
{
unsigned char szSQLSTATE[6] ;
SDWORD nErr ;
unsigned char ErrMsg[500] ;
SWORD cbmsg;
long lRet ; /*返回值*/
unsigned char lpSelect[1000];
memset((char*)lpSelect,0x00,sizeof(lpSelect)) ;
if(sSel=="")
sTemp.Format("Select count(*) from %s ",m_sTable) ;
else
sTemp.Format("Select Count(*) From %s Where %s",m_sTable,sSel) ;
if(!m_bOpen)
return -1 ;
strcpy((char*)lpSelect,sTemp) ;
SQLAllocStmt(hdbc,&hstmt_select) ;
rc=SQLExecDirect(hstmt_select,
lpSelect,
SQL_NTS) ;
if(rc==SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{ /* SQLExecDirect True*/
SQLBindCol(hstmt_select,1,SQL_INTEGER,&lRet,sizeof(int),NULL) ;
rc = SQLFetch(hstmt_select) ;
if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{ /*有數據*/
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
return lRet ;
}
else if (rc == 100)
{ /*沒有數據*/
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
return 0 ;
}
else if (rc < 0)
{ /*出錯*/
goto end ;
}
}
else
{ /* SQLExecDirect FALSE */
goto end ;
}
end:
{
m_sErrMsg = "" ;
while(SQLError(0,
0,
hstmt_select,
szSQLSTATE,
&nErr,
ErrMsg,
sizeof(ErrMsg),
&cbmsg)==SQL_SUCCESS)
m_sErrMsg += "\t\t" + CString(ErrMsg) +"\n" ;
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
return -1 ;
}
}
/*非常有用函數,用在確保語句句柄釋放*/
void CWdcDatabase::DropHstmtSelect()
{
if(hstmt_select !=NULL)
{
SQLFreeStmt(hstmt_select,SQL_DROP) ;
hstmt_select = NULL ;
}
}
|
