[C++]Oracle資料庫存取函式(OCCI)

開發環境

• VS2008
• Oracle 10g Express Edition

下載OCCI函式庫

1. 下載occivc9win32-111060-132181.zip，解壓縮到自行建立的lib目錄後可以得到目錄
2. 下載instantclient-sdk-win32-11.2.0.1.0.zip，解壓縮後可在[instantclient_11_2\sdk\]目錄找到[include]
3. 將上述兩個目錄複製到某個目錄下，如[D:\Project\CPPProject\occivc9win32-111060-132181]
   

設定開發專案

1. 將[lib]目錄中的oraocci11.dll與oraocci11d.dll都複製到[C:\WINDOWS\system32]
2. 於開發用的C++專案設定屬性：展開[組態屬性→C/C++→一般]，修改[其他Include目錄]，指到[include]目錄
   
3. 展開[組態屬性→連結器→一般]，修改[其他程式庫目錄]，指到lib目錄
   
4. 展開[組態屬性→連結器→輸入]，修改[其他相依性]，，增加[oraocci11d.lib]
   

資料庫連線函式庫

1. OracleIO.h
   

   #pragma once
   #include
   namespace commonio {
    class OracleIO
    {
     private:
      oracle::occi::Environment *env;
      oracle::occi::Connection *conn;
      oracle::occi::Statement *stmt;
      oracle::occi::ResultSet *rs;
      const char* _ip;
      unsigned int _port;
      const char* _account;
      const char* _pswd;
      const char* _defaultDB;
     public:
      OracleIO(const char* ip,unsigned int port,const char* account,const char* pswd,const char* defaultDB);
      ~OracleIO(void);
   
      
      /**
      **取得連線
      **/
      oracle::occi::Connection *getConn();
      void closeConn();
      void closeStatement();
      /**
      **執行查詢
      **/
      oracle::occi::ResultSet *executeQuery(const char* sqlStr);
      /**
      **執行新增、更新、刪除
      **/
      unsigned int executeUpdate(const char* sqlStr);
    };
   };

2. OracleIO.cpp
   

   #include "StdAfx.h"
   #include "OracleIO.h"
   #include "CommonFunc.h"
   #include 
   using namespace std;
   using namespace commonio;
   using namespace oracle::occi;
   OracleIO::OracleIO(const char* ip,unsigned int port,const char* account,const char* pswd,const char* defaultDB)
   {
    _ip=ip;
    _port=port;
    _account=account;
    _pswd=pswd;
    _defaultDB=defaultDB;
    env = oracle::occi::Environment::createEnvironment("UTF8","UTF8",Environment::THREADED_MUTEXED);
    conn=NULL;
    stmt=NULL;
    rs=NULL;
   }
   
   
   
   OracleIO::~OracleIO(void)
   {
    closeConn();
    oracle::occi::Environment::terminateEnvironment(env);
   }
   
   Connection* OracleIO::getConn(){
    if(conn==NULL){
     string port;
     commonio::CommonFunc::itostr(_port,port,10);
     string connectString="//";
     connectString=connectString + _ip+ ":" + port + "/" + _defaultDB;
     conn = env->createConnection(_account, _pswd, connectString);
    }
    return conn;
   }
   void OracleIO::closeConn(){
    if(conn!=NULL){
     closeStatement();
     env->terminateConnection(conn);
     conn=NULL;
    }
   }
   void OracleIO::closeStatement(){
    if(stmt!=NULL){
     if(rs!=NULL){
      stmt->closeResultSet(rs);
      rs=NULL;
     }
     conn->terminateStatement(stmt);
     stmt=NULL;
    }
   }
   
   ResultSet *OracleIO::executeQuery(const char* sqlStr){
    try{
     getConn();
     string sql=string(sqlStr);
     stmt = conn->createStatement(sql);
     rs = stmt->executeQuery();  
    }catch (SQLException &sqlExcp){
     cerr <createStatement(sql);
     result=stmt->executeUpdate();
     closeStatement();
    }catch (SQLException &sqlExcp){
     cerr <

3. 額外函式
   
   • CommonFunc.h
     

     #pragma once
     using namespace std;
     namespace commonio {
      class CommonFunc
      {
      public:
       static void itostr(int value, std::string& buf, int base);
      };
     };

   • CommonFunc.cpp
     

     #include "stdafx.h"
     #include 
     #include "CommonFunc.h"
     using namespace std;
     using namespace commonio;
     
     void CommonFunc::itostr(int value, std::string& buf, int base){
      
      int i = 30;
      
      buf = "";
      
      for(; value && i ; --i, value /= base) buf = "0123456789abcdef"[value % base] + buf;
      
     }

使用範例

#include "stdafx.h"
#include 
#include 
#include "OracleIO.h"
using namespace std;
using namespace commonio;

int main(int argc, _TCHAR* argv[])
{

 OracleIO oracleio=OracleIO("localhost",1521,"oracle","oracle","XE");
 /**
 **更新測試Start
 **/ 
 unsigned int changeNum= oracleio.executeUpdate("update test1 set testname='test123' where testsn=1");
 cout<<"changeNum:"< listOfColumns =rs->getColumnListMetaData();
 for(int i=0;inext())
 {
  for(int i=0;igetString(i+1)<<"\t";
  }
  cout <