基于boost和otlv4连接oracle进行简单查询笔记

头文件：

#ifndef DAO_H
#define DAO_H
#include <iostream>
#include<strstream>
#define OTL_ORA10G
#define OTL_ORA10G_R2
//#define OTL_ORA9I // Compile OTL 4.0/OCI9i
//#define OTL_ORA_TIMESTAMP // enable Oracle 9i TIMESTAMPs [with [local] time zone]
#include "otlv4.h" // include the OTL 4.0 header file
#include <string>
#include <boost/xpressive/xpressive.hpp>
#include<vector>
#include <boost/regex.hpp>
using namespace boost::xpressive;
using namespace std;


class Dao {
public:
    Dao();
    virtual ~Dao();
    string* select(string& sql);
    int otlInitialize(string& dsn);
    int logon();
    int getRows() const;
    vector<vector<string>*> * getResult();


protected:
private:
    otl_connect* db; // connect object
    int rows=0;
    string* dsn;
    string * result;
};

#endif // DAO_H

源文件：

#include "Dao.h"
Dao::Dao() {
    db=NULL;
    result=NULL;
}
int Dao::otlInitialize(string & dsn) {
    this->dsn=&dsn;
    db=new otl_connect();
    return otl_connect::otl_initialize(); // initialize OCI environment
}
int Dao::logon() {
    try {
        (*db).rlogon((*this->dsn).c_str()); // connect to Oracle
        //cout<<"Connect to Database"<<endl;
    } catch (otl_exception& p) {
        // intercept OTL exceptions
        cerr<<p.msg<<endl; // print out error message
        // cerr<<p.stm_text<<endl; // print out SQL that caused the error
        // cerr<<p.sqlstate<<endl; // print out SQLSTATE message
        // cerr<<p.var_info<<endl; // print out the variable that caused the error
        return 1;
    }
    return 0;
}
string* Dao::select(string& sql) {
    // const char* dsn="observe/tigger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=demo1)))";

//    cout<<sql<<endl;
    const char* csql = sql.c_str();
    //param 1: buffer size.param 2:SQL statement.param 3: connect object.
    otl_stream o(2000, csql, *db);
    result=NULL;
    result = new string;
    char* filename = new char[100];
    char* product_id = new char[100];
    char* producer = new char[100];
    char* risk_warn_product = new char[100];
    char* mete_code = new char[100];
    char* elevation = new char[100];
    char* cover_area = new char[100];
    char* start_time = new char[100];
    char* time_range = new char[100];
    char* file_type = new char[100];
    char* storage_time = new char[100];
    char* wrong_info = new char[1000];
    int* state =new int();

    boost::regex e1("\r\n");

    while (!o.eof()) {
        o >> filename >> product_id >> producer >> risk_warn_product
          >> mete_code >> elevation >> cover_area >> start_time
          >> time_range >> file_type >> storage_time >> wrong_info
          >>*state;
        *result += filename;
        *result += "@";
        *result += product_id;
        *result += "@";
        *result += producer;
        *result += "@";
        *result += risk_warn_product;
        *result += "@";
        *result += mete_code;
        *result += "@";
        *result += elevation;
        *result += "@";
        *result += cover_area;
        *result += "@";
        *result += start_time;
        *result += "@";
        *result += time_range;
        *result += "@";
        *result += file_type;
        *result += "@";
        *result += storage_time;
        *result += "@";

        //wrong_info = boost::regex_replace(string(wrong_info),e1,"", boost::match_default | boost::format_all).c_str();

        *result +=  boost::regex_replace(string(wrong_info),e1,"", boost::match_default | boost::format_all);
        *result += "@";
        strstream ss;
        string* stat=new string;
        ss<<*state;
        ss>>*stat;
        *result += *stat;
        *result+="@";
        *result += "\r\n";
        this->rows++;
    }
    o.close();
    db->logoff(); // disconnect from Oracle
    return result;

}

int Dao::getRows() const {
    return rows;
}
vector<vector<string>*> * Dao::getResult() {
    vector<vector<string>*> * rt=new vector<vector<string>*>();
    sregex token = sregex::compile(".+?\r\n");
    sregex_iterator cur( result->begin(), result->end(), token );
    sregex_iterator end;

    sregex token1 = sregex::compile("(.*?)@");
    for( ; cur != end; ++cur ) {
        smatch const &what = *cur;
        string field=what[0];
        //cout << field << "==="<<'\n';
        sregex_iterator cur1( field.begin(), field.end(), token1 );
        vector<string>* l=new vector<string>();
        for(; cur1!=end; ++cur1) {
            smatch const &what1 = *cur1;
            // assert(what1.size() == 1);
          // cout << what1[1] << "==="<<'\n';
            l->push_back(what1[1]);
        }
        rt->push_back(l);
    }

    return rt;
}

Dao::~Dao() {
    delete dsn;
    delete db;
}

测试：

void testdao() {
    BoostXmlUtil* xml = new BoostXmlUtil;
    string filename("./dsn.xml");
    map<string, string>* newMap = xml->dsn(filename);
//	newMap->insert(pair<string, string>("a", "d"));
    std::map<string, string>::iterator iter = newMap->find("dsn");

    string &dsn = iter->second;
    cout<<dsn;
    Dao* dao = new Dao();

    string* result = NULL;
    dao->otlInitialize(dsn);
    int r=dao->logon();
    cout<<r<<endl;
    if(r==1) {
        return;
    }
    string * sql=new string("select filename,product_id,producer,risk_warn_product,mete_code,elevation,cover_area,start_time,time_range,file_type,storage_time,wrong_info,state from data_statistics t");
    result = dao->select(*sql);

    vector<vector<string>*>* ret=dao->getResult();

    vector<string>* in;
    for (vector<vector<string>*>::iterator it = ret->begin(); it<ret->end(); it++) {
        in=*it;
                cout<<"---------------------------------"<<endl;

        for (vector<string>::iterator ite = in->begin(); ite<in->end(); ite++) {
            cout<<*ite<<endl;
        }
                cout<<"---------------------------------"<<endl;

    }
//	cout<<"result"<<*result;
//	RegexUtil ru;
//	ru.removeSurplusSpace(*result);
//	string newString = "32766";
//	ru.replaceSlash(*result, newString);
//	cout << *result << endl;
    //cout<<"-------------------"<<endl;
}

dsn.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<dsn>
<dsn>scott/tigger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xxx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=testsid)))</dsn>
</dsn>