【YashanDB知识库】YashanDB-OCI-快速上手

背景

OCI 是Oracle调用接口（Oracle Call Interface 简称OCI) 提供了一组对Oracle数据库进行存取的接口子例程（函数），通过在第三方设计语言（如C语言）中进行调用可达到存取ORACLE数据库的目的。

崖山数据库基于Oracle的OCI接口，开发了强兼容的接口，应用可以做比较少的改动，平滑地迁移到崖山数据库。

主要优点

1、Oracle数据库服务器特性中的即时可用性

2、企业级的性能和可伸缩性

3、强健的安全模型

4、在所有运行Oracle的平台上的可移植性。

本文将介绍cmake 方式编译崖山数据库，帮助读者快速上手崖山数据库的OCI接口，并在使用OCI解决实际工作遇到的问题。

环境准备

1、配置yashandb的C驱动 和 OCI 文件

2、准备Oracle的OCI软件包，需要下载两个:

其一是instantclient-basic-linux.x64-21.14.0.0.0dbru.zip,

其二是instantclient-sdk-linux.x64-21.14.0.0.0dbru.zip

3、机器提前安装GDB和CMAKE工具

如何编译yashandb的OCI代码？

接下来，以官网OCI示例代码为例，快速上手编译Yashandb的OCI代码

准备编译前命令

编辑 CMakeList.txt，它是CMAKE工程编译的声明命令文件

准备Yashandb OCI 依赖文件

执行编译命令

执行二进制软件

以一个OCI代码解释代码常用语法的含义

#include <stdio.h>

#include <stdint.h>

#include <stdlib.h>

#include <string.h>

#include <oci.h>





// 调用OCI代码的CALL函数

#define OCI_TEST_CALL(ociFunc) \

    do { \

        sword r = ociFunc; \

        if (r != OCI_SUCCESS) { \

            checkerr(errhp, r); \

            return r; \

        } \

    } while (0)



static text* username = (text*)"sys";

static text* password = (text*)"Cod-2022";

static text* dbname = (text*)"CITEST";



static OCIEnv* envhp = NULL;

static OCIError* errhp = NULL;

static OCISvcCtx* svchp = NULL;

static OCISession* authp = NULL;

static OCIServer* srvhp = NULL;



// ## 检查代码执行是否返回异常的函数

void checkerr(OCIError* errhp, sword status)

{

    text errbuf[512];

    sb4 errcode = 0;



      // 根据status 判断OCI执行结果状态

    switch (status) {

        case OCI_SUCCESS:

            break;

        case OCI_SUCCESS_WITH_INFO:

            (void)printf("Error - OCI_SUCCESS_WITH_INFO\n");

            break;

        case OCI_NEED_DATA:

            (void)printf("Error - OCI_NEED_DATA\n");

            break;

        case OCI_NO_DATA:

            (void)printf("Error - OCI_NODATA\n");

            break;

        case OCI_ERROR:

            (void)OCIErrorGet((dvoid*)errhp, (ub4)1, (text*)NULL, &errcode, errbuf, (ub4)sizeof(errbuf),

                              OCI_HTYPE_ERROR);

            (void)printf("Error - %.*s\n", 512, errbuf);

            break;

        case OCI_INVALID_HANDLE:

            (void)printf("Error - OCI_INVALID_HANDLE\n");

            break;

        case OCI_STILL_EXECUTING:

            (void)printf("Error - OCI_STILL_EXECUTE\n");

            break;

        case OCI_CONTINUE:

            (void)printf("Error - OCI_CONTINUE\n");

            break;

        default:

            break;

    }

}

// 单行绑定导入数据

sword testSingleBind()

{

    OCIStmt* stmthp = NULL;

    // 申请一个句柄

    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid**)0);



    OCIBind* bindp1 = NULL;

    OCIDefine* definep1 = NULL;

    ub4 intOut1[3] = {0, 1, 2};



    OraText* sql = "drop table if exists tbl_bind";

    // 类似JDBC的prepare statement

    //在 OCI（Oracle Call Interface）中，ub4 是 Oracle C 数据类型之一，它代表一个无符号的 4 字节（32位）整数。OCI 是 Oracle 数据库提供的编程接口，允许开发者使用 C 或 C++ 编写可以与 Oracle 数据库交互的程序。



    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    // 类似JDBC的执行

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT));



    sql = "create table tbl_bind(col1 int, col2 varchar(20))";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT));



    sql = "insert into tbl_bind values (:1, '11')";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIBindByPos(stmthp, &bindp1, errhp, (ub4)1, (dvoid*)intOut1, (sb4)sizeof(ub4), SQLT_INT, 0, 0, 0, 0, NULL, OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)3, (ub4)0, NULL, NULL, OCI_DEFAULT));



    ub4 rowcnt;

    OCI_TEST_CALL(OCIAttrGet((CONST dvoid*)stmthp, (ub4)OCI_HTYPE_STMT, (void*)&rowcnt, (ub4*)0, (ub4)OCI_ATTR_ROW_COUNT, errhp));

    if (rowcnt != 3) {

        return OCI_ERROR;

    }



    // 单行取数据

    sql = "select col1 + 99 from tbl_bind";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIDefineByPos(stmthp, &definep1, errhp, (ub4)1, (dvoid*)intOut1, (sb4)sizeof(ub4), SQLT_INT, 0, 0, 0, OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)3, (ub4)0, NULL, NULL, OCI_DEFAULT));

    if (intOut1[0] != 99 || intOut1[1] != 100 || intOut1[2] != 101) {

        return OCI_ERROR;

    }

      //释放句柄

    (void)OCIHandleFree((dvoid*)stmthp, (ub4)OCI_HTYPE_STMT);

    return OCI_SUCCESS;

}



// 批量绑定导入数据

sword testBatchBind()

{

    OCIStmt* stmthp = NULL;

    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid**)0);



    OCIBind* bindp = NULL;

    OCIDefine* definep = NULL;



    sb1 intIn1[4];

    sb2 ind1[4];

    ub2 rlen1[4];



    OraText* sql = "drop table if exists tbl_bind";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT));



    sql = "create table tbl_bind(col1 bigint)";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen((char*)sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT));



    intIn1[0] = 0;

    intIn1[1] = 1;

    intIn1[2] = 2;



    ind1[0] = 0;

    ind1[1] = 0;

    ind1[2] = 0;



    rlen1[0] = 1;

    rlen1[1] = 1;

    rlen1[2] = 1;



    OraText* sql1 = "insert into tbl_bind values (:1)";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql1, (ub4)strlen((char*)sql1), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIBindByPos(stmthp, &bindp, errhp, (ub4)1, (dvoid*)intIn1, (sb4)sizeof(sb1), SQLT_INT, ind1, rlen1, 0, 0, NULL, OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)3, (ub4)0, NULL, NULL, OCI_DEFAULT));



    // 批量取数据

    OraText* sql2 = "select col1 from tbl_bind order by rowid";

    OCI_TEST_CALL(OCIStmtPrepare(stmthp, errhp, sql2, (ub4)strlen((char*)sql2), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    OCI_TEST_CALL(OCIDefineByPos(stmthp, &definep, errhp, (ub4)1, (dvoid*)intIn1, (sb4)sizeof(sb1), SQLT_INT, ind1, rlen1, 0, OCI_DEFAULT));

    OCI_TEST_CALL(OCIStmtExecute(svchp, stmthp, errhp, (ub4)3, (ub4)0, NULL, NULL, OCI_DEFAULT));



    if (intIn1[0] != 0 || intIn1[1] != 1 || intIn1[2] != 2) {

        return OCI_ERROR;

    }



    if (ind1[0] != 0 || ind1[1] != 0 || ind1[2] != 0) {

        return OCI_ERROR;

    }



    if (rlen1[0] != 1 || rlen1[1] != 1 || rlen1[2] != 1) {

        return OCI_ERROR;

    }



    (void)OCIHandleFree((dvoid*)stmthp, (ub4)OCI_HTYPE_STMT);

    return OCI_SUCCESS;

}



// 连接数据库

sword testConnect()

{

    sword errcode = 0;



    errcode = OCIEnvCreate((OCIEnv**)&envhp, (ub4)OCI_THREADED, (dvoid*)0, (dvoid * (*)(dvoid*, size_t))0,

                     (dvoid * (*)(dvoid*, dvoid*, size_t))0, (void (*)(dvoid*, dvoid*))0, (size_t)0, (dvoid**)0);



    if (errcode != 0) {

        (void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode);

        return OCI_ERROR;

    }



    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid**)0);

    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid**)0);

    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, (size_t)0, (dvoid**)0);



    OCI_TEST_CALL(OCIServerAttach(srvhp, errhp, (text*)dbname, (sb4)strlen((char*)dbname), 0));

    (void)OCIAttrSet((dvoid*)svchp, OCI_HTYPE_SVCCTX, (dvoid*)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError*)errhp);



    (void)OCIHandleAlloc((dvoid*)envhp, (dvoid**)&authp, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid**)0);

    (void)OCIAttrSet((dvoid*)authp, (ub4)OCI_HTYPE_SESSION, (dvoid*)username, (ub4)strlen((char*)username), (ub4)OCI_ATTR_USERNAME, errhp);

    (void)OCIAttrSet((dvoid*)authp, (ub4)OCI_HTYPE_SESSION, (dvoid*)password, (ub4)strlen((char*)password), (ub4)OCI_ATTR_PASSWORD, errhp);



    OCI_TEST_CALL(OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT));

    (void)OCIAttrSet((dvoid*)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid*)authp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp);



    return OCI_SUCCESS;

}



// 关闭数据库连接

sword testDisconnect()

{

    OCI_TEST_CALL(OCISessionEnd(svchp, errhp, authp, (ub4)0));

    OCI_TEST_CALL(OCIServerDetach(srvhp, errhp, (ub4)OCI_DEFAULT));



    (void)OCIHandleFree((dvoid*)authp, (ub4)OCI_HTYPE_SESSION);

    (void)OCIHandleFree((dvoid*)srvhp, (ub4)OCI_HTYPE_SERVER);

    (void)OCIHandleFree((dvoid*)svchp, (ub4)OCI_HTYPE_SVCCTX);

    (void)OCIHandleFree((dvoid*)errhp, (ub4)OCI_HTYPE_ERROR);

    (void)OCIHandleFree((dvoid*)envhp, (ub4)OCI_HTYPE_ENV);



    return OCI_SUCCESS;

}

sword runTest()

{

    if (testConnect() != OCI_SUCCESS) {

        return 1;

    }

    if (testSingleBind() != OCI_SUCCESS) {

        return 2;

    }

    if (testBatchBind() != OCI_SUCCESS) {

        return 3;

    }

    if (testDisconnect() != OCI_SUCCESS) {

        return 5;

    }

    return 0;

}



int main(argc, argv)

int argc;

char* argv[];

{

    sword runResult = runTest();

    if (runResult == 0) {

        printf("test succeed!\n");

    } else {

        printf("test failed! failed test num : %d\n", runResult);

    }

    return runResult;

}

参考资料

https://www.yashandb.com/newsinfo/7488285.html?templateId=1718516