OCI for Windows 環境手工配置

http://www.cppblog.com/Khan/archive/2010/06/02/110361.html

本來公司的項目是在linux下處理的. 由於近期人員調動. 需要增加幾個新人. 為了讓不熟悉linux開發環境的新人能夠儘快上手使用公司的基礎類庫, 所以我承擔了搭建windows下的開發環境搭建工作

其中第一項就是將公司使用的基於oci的封裝ocicpp編譯出一個mingw32的版本

1. 必要工具

Instant Client Downloads
for Microsoft Windows

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

oracle instant Client

mingw32

自行搜尋, 本人使用3.42
gcc的win32版本

pexports for mingw32

自行搜尋
將c的dll匯出為def的工具, 如果你的mingw沒有內建. 請自行下載. 並放到path可以找到的地方, 不支援c++的dll.

以上工具自行安裝
instantclient解壓到D:/Develop/Cplus/instantclient_11_1/
mingw32安裝到D:/Develop/Cplus/MinGW/
pexports.exe解壓到D:/Develop/Cplus/MinGW/bin/

2. 產生能被mingw32調用的oci lib
由於oracle 官方的oci sdk並無提供可供mingw link的包. 所以我們需要手工根據dll做一個

使用dll產生mingw32的lib
匯出def
D:/Develop/Cplus/instantclient_11_1/sdk/lib>pexports oci.dll > oci.def

產生lib
D:/Develop/Cplus/instantclient_11_1/sdk/lib>dlltool --dllname oci.dll --def oci.def --output-lib liboci.a

這樣我們就有了一個可以mingw32 link的 liboci.a了

3.配置oracle instant Client
只需要3個環境變數
TNS_ADMIN=D:/Develop/Cplus/instantclient_11_1
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
PATH=%PATH%;%TNS_ADMIN%/

4.編譯執行測試程式.
保密協議導致我無法公開ocicpp的源碼.
但是可以貼基於ocicpp的測試程式

#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <iostream>
#include "ocicpp/ocicpp.h"
#include "ocicpp/db.h"

using    namespace    OCICPP;

bool InsertDataBySQL(Connection* pConn)
{
    Cursor    cursor;
    string strSQL;

    strSQL = "INSERT INTO testOCICPP (TestId, UserName, PassWord, Address ) VALUES(13, 'xxxxx', '!@#$1234', 'xxxxx')";

    try {
        pConn->execQuery(strSQL, cursor);
        cursor.drop();
        pConn->transCommit();
    }
    catch (OraError err) {
        cout << "InsertDataBySQL Exception: " <<  err.message << endl;
        return false;
    }
    return true;
}

bool UpdateDataBySQL(Connection* pConn)
{
    Cursor    cursor;
    string strSQL;

    strSQL = "UPDATE testOCICPP SET PassWord = '&*()7890'";

    try {
        pConn->execQuery(strSQL, cursor);
        cursor.drop();
        pConn->transCommit();
    }
    catch (OraError err) {
        cout << "UpdateDataBySQL Exception: " <<  err.message << endl;
        return false;
    }
    return true;
}

bool DelRecordBySQL(Connection* pConn)
{
    Cursor    cursor;
    string strSQL;

    strSQL = "DELETE FROM testOCICPP";

    try {
        pConn->execQuery(strSQL, cursor);
        cursor.drop();
        pConn->transCommit();
    }
    catch (OraError err) {
        cout << "DelRecordBySQL Exception: " <<  err.message << endl;
        return false;
    }
    return true;
}
bool FetchDataBySQL(Connection* pConn)
{
    Cursor    cursor;
    string strSQL;

    strSQL = "SELECT MEMBERID,MOBILE,PASSWD,ADDRESS FROM tblmember where rownum<20";

    try {
        pConn->execQuery(strSQL, cursor);
        while (cursor.fetch())        {
            cout << ".......... Record .........." << endl;
            cout << "TestId: " << cursor.getInt("MEMBERID") << endl;
            cout << "UserName: " << cursor.getStr("MOBILE") << endl;
            cout << "PassWord: " << cursor.getStr("PASSWD") << endl;
            cout << "Address: " << cursor.getStr("ADDRESS") << endl;
        }
        cursor.drop();
        pConn->transCommit();
    }
    catch (OraError err) {
        cout << "FetchDataBySQL Exception: " <<  err.message << endl;
        return false;
    }
    return true;
}

bool InsertDataByProcedure(Connection* pConn)
{
    Cursor    cursor;
    string strSQL;
    int iRetCode=0;
    strSQL = "BEGIN testProcInsert(13, 'xxxxx', '!@#$1234', 'xxxxx', :RetCode); END;";

    try {
        pConn->prepare(strSQL, cursor);
        cursor.bind(":RetCode", iRetCode);
        cursor.execute();        cursor.drop();
    }
    catch (OraError err) {
        cout << "InsertDataBySQL Exception: " <<  err.message << endl;
        return false;
    }
    cout << "InsertDataByProcedure RetCode: " << iRetCode << endl;

    return true;
}

bool UpdateDataByProcedure(Connection* pConn)
{

    return false;
}

bool DelRecordByProcedure(Connection* pConn)
{
    return false;
}

bool FetchDataByProcedure(Connection* pConn)
{

    return false;
}

int StartRun(int p_iMode)
{
    Connection    dbConn;

    OCICPP::db::init();

    try{
        OCICPP::db::connect("smsdb", "point", "point1234", dbConn);

        switch(p_iMode)
        {
        case 1:
            InsertDataBySQL(&dbConn);
            break;
        case 2:
            UpdateDataBySQL(&dbConn);
            break;
        case 3:
            DelRecordBySQL(&dbConn);
            break;
        case 4:
            FetchDataBySQL(&dbConn);
            break;
        case 5:
            InsertDataByProcedure(&dbConn);
            break;
        case 6:
            UpdateDataByProcedure(&dbConn);
            break;
        case 7:
            DelRecordByProcedure(&dbConn);
            break;
        case 8:
            FetchDataByProcedure(&dbConn);
            break;
        default:
            break;
        }
    } catch(OraError err) {
        cout << "OraError Exception: " << err.message << endl;
    }
    return 0;
}

int main(int argc, char* argv[])
{
    if (argc != 2)
    {
        cout << endl;
        cout << "Please input run mode (1 - 6): " << endl;
        cout << endl;
        cout << "    1:  InsertDataBySQL" << endl;
        cout << "    2:  UpdateDataBySQL" << endl;
        cout << "    3:  DelRecordBySQL" << endl;
        cout << "    4:  FetchDataBySQL" << endl;
        cout << "    5:  InsertDataByProcedure" << endl;
        cout << "    6:  UpdateDataByProcedure" << endl;
        cout << "    7:  DelRecordByProcedure" << endl;
        cout << "    8:  FetchDataByProcedure" << endl;
        cout << endl;
        exit(-1);
    }
    int iMode = atoi(argv[1]);

    //pid_t iPid;
   
    //iPid = fork();
   
    //if (iPid == 0)
    //{
        StartRun(iMode);
        exit(0);
    //}
    //else if (iPid < 0)
    //{
    //    cout << "Fork Failure" << endl;
    //}
    //sleep(3);
    cout << "................. Finish ................." << endl;
    return 0;
}

/*****************************************************************
CREATE TABLE testOCICPP
(
    TestId            number(10, 0)    DEFAULT 1 NOT NULL,
    UserName        varchar2(16)    DEFAULT ' ' NOT NULL,
    PassWord        varchar2(16)    DEFAULT ' ' NOT NULL,
    Address            varchar2(128)    DEFAULT ' ' NOT NULL
)tablespace rism_db;

CREATE OR REPLACE PROCEDURE testProcInsert(
    p_iTestId IN number, p_sUserName IN string, p_sPassWord IN string,
    p_sAddress IN string, p_iRetCode OUT number)
IS
BEGIN
    p_iRetCode := 0;
    INSERT INTO testOCICPP(TestId, UserName, PassWord, Address)
        VALUES(p_iTestId, p_sUserName, p_sPassWord, p_sAddress);
    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
            p_iRetCode := SQLCODE;
END;
/

*****************************************************************/

編譯成ocicpp.exe

此時需要兩個dll檔案與ocicpp.exe在同一個目錄
在oracle instant Client安裝目錄下找到oraociei11.dll oci.dll
根據版本不同. oraociei11.dll這個檔案名稱或許會有差異.
也可以將倆dll扔到system32目錄下

本人伺服器端為rhel 3.4 ,資料庫環境為9.2.04
用戶端環境為windows mingw32-gcc3.42