C++使用occi连接oracle数据库

什么是OCCI？

occi就是Oracle C++ Call Interface。

安装和使用

下载

去到oracle的官网，到下载页：https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

然后网页往下拉，这个对应的sdk包就有相关头文件和使用例子等。

配项目属性

我把对应头文件和lib库放到了我的计算机的这里：

(1) 然后用使用的项目（VS2019下）属性配置相关的头文件引用和lib库引用，D:\usinglibs我配了环境变量LIBS_ROOT:

(2)静态库


debug版本的库一般是名字后面有一个d的。 oraocci19d.lib.

写代码

（1）DatabaseManager.h头文件

#pragma once
#include <Utils/ExportDefine.h>
#include <Utils/LogUtil.h>
#include <Utils/Config.h>
#include <occi.h>

using namespace utils;
using namespace oracle::occi;

namespace datacenter
{
    class DATACENTER_EXPORT DatabaseManager
    {
    public:
        DatabaseManager() {};
        ~DatabaseManager();
        //eg.connStr= "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.97)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = MyDb)))"
        int connect(const std::string& user, const std::string& passwd, const std::string& connStr);
        void getSecutityCodesFromDB(map<int, map<string, string> >& codeToNameMap, const string& sql, int date);
    private:
        //boost::shared_ptr<Environment> m_envPtr;
        //boost::shared_ptr<Connection> m_connPtr;
        Environment* m_envPtr;
        Connection* m_connPtr;
    };
}

（2）对应的cpp文件，DatabaseManager.cpp

#include <DataCenter\DatabaseManager.h>
using namespace datacenter;

DatabaseManager::~DatabaseManager()
{
    if (nullptr != m_connPtr)
    {
        m_envPtr->terminateConnection(m_connPtr);
        m_connPtr = nullptr;
    }
    if (nullptr != m_envPtr)
    {
        Environment::terminateEnvironment(m_envPtr);
        m_envPtr = nullptr;
    }
}

int DatabaseManager::connect(const std::string& user, const std::string& passwd,
    const std::string& connStr)
{
    try
    {
        m_envPtr = /*boost::shared_ptr<Environment>(*/Environment::createEnvironment("ZHS16GBK","ZHS16GBK");
        m_connPtr = /*boost::shared_ptr<Connection>(*/m_envPtr->createConnection(user, passwd, connStr);
    }
    catch (SQLException& e)
    {
        LLogError("Using " << user << "/" << passwd << "@" << connStr << " connect to database error," << e.getErrorCode() << ": " << e.getMessage());
        return -1;
    }
    return 0;
}

void DatabaseManager::getSecutityCodesFromDB(map<int, map<string,string> >& codeToNameMap, const string& sql, int date)
{
    try
    {
        Statement* stmt = m_connPtr->createStatement();
        ResultSet* rs = stmt->executeQuery(sql);
        while (rs->next() != 0)
        {
            codeToNameMap[date][rs->getString(1)] = rs->getString(2);
        }
        LLogInfo("Secutity codes,date:" << date << ",nums:" << codeToNameMap[date].size());
        stmt->closeResultSet(rs);
        m_connPtr->terminateStatement(stmt);
    }
    catch (SQLException& e)
    {
        LLogError("Error," << e.getErrorCode() << ": " << e.getMessage());
    }

}

（3）某个使用的cpp中

void DbData::initCodeToNameMap(int startDate, int endDate)
{
    Configuration cfg(m_configFile);
    cfg.load();
    string user = cfg.read("oracle", "userName");
    string pwd = cfg.read("oracle", "password");
    string host = cfg.read("oracle", "host");
    string port = cfg.read("oracle", "port");
    string db = cfg.read("oracle", "serviceName");
    string stypes = cfg.read("oracle", "stype");
    string exchanges = cfg.read("oracle", "exchange");
    string connStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=" + port + "))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + db + ")))";
    LLogInfo("Read Config, userName:" << user << ",password:" << pwd << ",host:" << host << ",port:" << port << ",serviceName:" << db << ",stype:" << stypes << ",exchange:" << exchanges);
    datacenter::DatabaseManager dbMan = datacenter::DatabaseManager();
    if (dbMan.connect(user, pwd, connStr) != 0)
    {
        return;
    }
    m_codeToNameMap.clear();
    stringstream ss;
    int nowdate = startDate;
    LLogInfo("Get data from date:" << startDate << " to date:" << endDate);
    vector<string> stypeVec = cfg.readVector("oracle", "stype", ",");
    vector<string> exchangeVec = cfg.readVector("oracle", "exchange", ",");
    stringstream sstype;
    stringstream sexchange;
    addSingleQuotes(sstype, stypeVec);
    addSingleQuotes(sexchange, exchangeVec);
    while (nowdate < endDate)
    {
        ss << nowdate;
        string date = ss.str();
        ss.clear();//情况状态
        ss.str("");//清空缓存
        string sql = "select symbol,sname from securitycode where stype in (" + sstype.str() + ") and exchange in (" + sexchange.str() + ") and status = 0 and enabled=0 and to_char(ListDate,'YYYYMMDD') <='" + date + "'";
        dbMan.getSecutityCodesFromDB(m_codeToNameMap, sql, nowdate);
        nowdate = utils::nextDate(nowdate);
    }
}

注意：本人的代码因为是实际使用的，如日志库和配置文件读取等上面的代码用并没有。想要编译通过，需要注释掉。

遇到的问题

（1）对应的动态库放到运行目录下，不单单是oraocci19d.dll这个库，还需要如下两个，否则运行数据库连接的时候，会抛出异常，连接不成功。
oraociei19.dll
oraons.dll
（2）sql语句后面不需要分号;，注意看我的代码，否则也会出错抛异常。

（3）下载occi的时候请自行根据自己的vs版本下相应的版本，如果你vs版本低，occi的版本也要跟着用低的。

之前看官网这里说要求是vs2017，以为2019不行，实践证明其实也是可以的。

完毕。