一個用功能強大的ODBC API 函數訪問數據庫類

/*-- 專項技術學習?。?STRONG>數據庫訪問技術之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 Suclearcase/" target="_blank" >cceed !\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 ;
}
}

原文轉自：http://www.kjueaiud.com