TDSQL PostgreSQL版使用 OCI 连接 TDSQ

本文介绍如何使用 OCI 示例程序连接及操作数据库。
获取数据库连接信息
联系数据库管理员获取数据库连接命令：
psql -h 10.211.55.30 -p 11345 -U tbase -d tdsql
参数说明：
-h 数据库 IP，通常是 CN 的 IP。
-p 数据库端口。
-U 数据库用户。
-d 需要访问的数据库名称。
安装驱动依赖
1. 下载驱动：请单击 TDSQL-PG OCI 驱动包下载。
2. 安装
tar -xvf tdsql_psqloci_tlinux4_x86_64.tar.gz
解压 TDSQL OCI 驱动包，假设解压路径为$PG_OCI_PATH
安装包文件说明：
include 依赖的头文件，OCI 函数声明。
lib 依赖库，OCI 函数实现。
oci_test.c demo，提供参考。
pg_service.conf 数据库连接信息配置（数据源设置）
README demo 操作说明
run.sh demo 执行脚本
3. 配置相关环境信息
3.1 配置连接文件，编辑 pg_service.conf：
[db_test] #数据源名称
#数据库服务IP
hostaddr=10.211.55.30
#数据库服务端口号
port=11345
#需要连接的数据库
dbname=tdsql
注意：
OCI 应用程序连接数据库进行 dbname 设置时，使用的是 pg_service.conf 中的数据源名称而不是实际访问的数据库名称。在 OCI 应用程序中配置数据源、用户名和密码。在 pg_service.conf 配置数据库 IP、端口、实际访问的数据库名。
3.2 配置环境变量，编辑 ~/.bashrc。
export LD_LIBRARY_PATH=$PG_OCI_PATH/lib:$LD_LIBRARY_PATH
export PGSERVICEFILE=$PG_OCI_PATH/pg_service.conf
说明：
PGSERVICEFILE：pg_services.conf 文件环境变量，依据实际路径填写。
3.3 环境变量生效
source ~/.bashrc
校验驱动库依赖完整性：
ldd $PG_OCI_PATH/lib/liboci.so
链接不出现 not found 则为成功。
编写 OCI 测试程序
示例程序：oci_test.c，已提供在$PG_OCI_PATH路径中，修改用户名、密码、数据源信息即可。
#include "oci.h"
#include "oratypes.h"
#include "xa.h"
#include <unistd.h>
#include <stdbool.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/signal.h>
#include <unistd.h>
#include <pthread.h>
﻿
#define MAX_SIZE 1024
#define MAX_STR_LENTH 256
#define MAX_COUNT 3
﻿
typedef struct __OCI_GLOBAL_CONTEXT_TYPE
{
    OCIEnv*     envhp;
    OCIServer*  srvhp;
    OCISvcCtx*  svchp;
    OCIError*   errhp;
    OCISession* authp;
    OCIStmt*    stmthp;
} OCI_GLOBAL_CONTEXT;
﻿
//准备阶段测试
sword oci_alloc_connect(bool useLogon, OCI_GLOBAL_CONTEXT* ctxptr, char* uname, char* passwd, char* dbname);
sword oci_free_disconnect(bool useLogon, OCI_GLOBAL_CONTEXT* ctxptr);
void oci_checkerr(OCIError* errhp, sword status);
sword oci_exec_sample_sql(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode);
﻿
sword case0_create(OCI_GLOBAL_CONTEXT *ctxptr);
sword case1_select(OCI_GLOBAL_CONTEXT *ctxptr);
sword case2_update(OCI_GLOBAL_CONTEXT *ctxptr);
sword case3_insert(OCI_GLOBAL_CONTEXT *ctxptr);
sword case4_delete(OCI_GLOBAL_CONTEXT *ctxptr);
sword case5_vacuum(OCI_GLOBAL_CONTEXT *ctxptr);
﻿
//根据位置绑定参数和定义结果集测试
sword fetch_by_define(void* ptr, char* sql);
void test();
﻿
sword oci_free_handle(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode);
sword oci_alloc_handle(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode);
﻿
/*
测试函数：OCIInitialize()、OCIEnvInit()、OCIAttrSet()、OCIStmtPrepare()、OCIStmtExecute()、OCITransCommit()、OCIDescriptorAlloc()、OCIDefineByPos()、OCIStmtFetch()、OCIErrorGet()
    1、OCIInitialize()初始化 OCI 应用环境
    2、OCIEnvInit()初始化环境句柄
    3、OCIDescriptorAlloc()分配句柄
    4、OCIAttrSet()设置会话句柄用户名和密码
    5、OCIAttrSet()设置环境句柄属性
    6、创建并开始一个用户两个会话
    7、断开一个会话
    8、OCIErrorGet()读取错误信息
    9、创建表并执行以下操作
    a.	OCIStmtPrepare()准备 sql 语句
    b.	OCIStmtExecute()执行 sql 语句
    c.	OCITransCommit ()提交事务
    d.	OCIDefineByPos ()定义输出变量
    e.	OCIStmtFetch()获取数据
    10、结束释放资源，断开数据库连接，释放句柄
*/
﻿
/* 需要修改用户名、密码及在 pg_service.conf 中配置的数据源名称，这3个变量均不能为空*/
char*       uname     = "tbase";
char*       passwd    = "1234@abcd";
char*       dbname    = "db_test";
﻿
int main(int argc, char* argv[])
{
    test();
    return 0;
﻿
}
﻿
void test()
{
﻿
    OCI_GLOBAL_CONTEXT ctxptr = {NULL};  
    sword              err    = OCI_SUCCESS;
﻿
    do {
        err = oci_alloc_connect(true, &ctxptr, uname, passwd, dbname);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
        err = case0_create(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
        err = case3_insert(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
        err = case1_select(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
        oci_alloc_handle(&ctxptr, NULL, OCI_COMMIT_ON_SUCCESS);
        err = case2_update(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
        err = case5_vacuum(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
        err = case4_delete(&ctxptr);
        if (err != OCI_SUCCESS) {
            break;
        }
        oci_free_handle(&ctxptr, NULL, OCI_COMMIT_ON_SUCCESS);
﻿
        oci_free_disconnect(false, &ctxptr);
﻿
    } while (0);
}
﻿
sword case0_create(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
        // 删除表重建
        sql = "drop table if exists case1_bindbypos_define;";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
        sql = "create table case1_bindbypos_define(id int, name character varying(255), created character varying(12), attr text);";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
    } while (0);
    return err;
}
﻿
sword case1_select(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
﻿
        //获取结果集
        sql = "select * from case1_bindbypos_define where id >10";
        err = fetch_by_define((void*)ctxptr, sql);
        if (err != OCI_SUCCESS) {
            break;
        }
﻿
    } while (0);
    return err;
}
﻿
sword case2_update(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
﻿
        // 更新数据
        sql = "update case1_bindbypos_define set name='YYYYYYYYY' where id=100;";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
    } while (0);
    return err;
}
﻿
sword case3_insert(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
        // 插入数据
        sql = "insert into case1_bindbypos_define values(generate_series(1, 10000), 'AAAAAAAAAAAAAAA', 'BBBBBBBBB', 'CCCCCCCCCCCCCC');";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
    } while (0);
    return err;
}
﻿
sword case4_delete(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
﻿
        //删除数据
        sql = "delete from case1_bindbypos_define where id < 10001;";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
    } while (0);
    return err;
}
﻿
sword case5_vacuum(OCI_GLOBAL_CONTEXT *ctxptr)
{
    char*   sql = NULL;
    sword   err = OCI_ERROR;
﻿
    do {
        sql = "vacuum full case1_bindbypos_define";
        err = oci_exec_sample_sql(ctxptr, sql, OCI_COMMIT_ON_SUCCESS);
﻿
    } while (0);
    return err;
}
﻿
sword fetch_by_define(void* ptr, char* sql)
{
#define max_one_size 5
    sword               status  = OCI_SUCCESS;
    OCI_GLOBAL_CONTEXT* ctxptr  = (OCI_GLOBAL_CONTEXT*)ptr;
    OCIDefine*          dnfp[4] = {NULL};
﻿
    int ret = -1;
    int j = 0;
﻿
    do {
        int  id[max_one_size]       = {0};
        char name[max_one_size][15] = {{'\\0'}};
        char date[max_one_size][12] = {{'\\0'}};
        char attr[max_one_size][20] = {{'\\0'}};
﻿
        // 1 申请执行语句
        if (ctxptr->stmthp == NULL) {
            if ((status = OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->stmthp, (ub4)OCI_HTYPE_STMT,
                                         (CONST size_t)0, (dvoid**)0)) != OCI_SUCCESS) {
                oci_checkerr(ctxptr->errhp, status);
                break;
            }
        }
﻿
        // 3 query sql
        status = OCIStmtPrepare(ctxptr->stmthp, ctxptr->errhp, (text*)sql, (ub4)strlen((char*)sql), OCI_NTV_SYNTAX,
                                OCI_DEFAULT);
        if (status != OCI_SUCCESS) {
            oci_checkerr(ctxptr->errhp, status);
            break;
        }
        //将结果集内存按查询位置绑定到对应列
        if (OCIDefineByPos(ctxptr->stmthp, &dnfp[0], ctxptr->errhp, 1, (void*)&id[0], sizeof(int), SQLT_INT, (void*)0,
                           (ub2*)0, (ub2*)0, OCI_DEFAULT) != OCI_SUCCESS) {
            break;
        }
        printf("SUCCESS: OCIDefineByPos ()定义输出变量\\n");
﻿
        if (OCIDefineByPos(ctxptr->stmthp, &dnfp[1], ctxptr->errhp, 2, (void*)name[0], 15, SQLT_STR, (void*)0, (ub2*)0,
                           (ub2*)0, OCI_DEFAULT) != OCI_SUCCESS) {
            break;
        }
﻿
        if (OCIDefineByPos(ctxptr->stmthp, &dnfp[2], ctxptr->errhp, 3, (void*)date[0], 12, SQLT_STR, (void*)0, (ub2*)0,
                           (ub2*)0, OCI_DEFAULT) != OCI_SUCCESS) {
            break;
        }
﻿
        if (OCIDefineByPos(ctxptr->stmthp, &dnfp[3], ctxptr->errhp, 4, (void*)attr[0], 20, SQLT_STR, (void*)0, (ub2*)0,
                           (ub2*)0, OCI_DEFAULT) != OCI_SUCCESS) {
            break;
        }
        //因为结果集中的整个列都在一块内存，需要告知分割大小
        if (OCIDefineArrayOfStruct(dnfp[0], ctxptr->errhp, sizeof(int), 0, 0, 0) != OCI_SUCCESS) {
            break;
        }
        if (OCIDefineArrayOfStruct(dnfp[1], ctxptr->errhp, 15, 0, 0, 0) != OCI_SUCCESS) {
            break;
        }
        if (OCIDefineArrayOfStruct(dnfp[2], ctxptr->errhp, 12, 0, 0, 0) != OCI_SUCCESS) {
            break;
        }
        if (OCIDefineArrayOfStruct(dnfp[3], ctxptr->errhp, 20, 0, 0, 0) != OCI_SUCCESS) {
            break;
        }
        //SQL执行
        status = OCIStmtExecute(ctxptr->svchp, ctxptr->stmthp, ctxptr->errhp, (ub4)max_one_size, (ub4)0, NULL, NULL,
                                OCI_DEFAULT);
        if (status != OCI_SUCCESS) {
            oci_checkerr(ctxptr->errhp, status);
            break;
        }
﻿
        // fetch data
        status = OCIStmtFetch(ctxptr->stmthp, ctxptr->errhp, (ub4)max_one_size, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT);
        printf("SUCCESS: OCIStmtFetch()获取数据\\n");
        if (status == OCI_SUCCESS) {
            for (j = 0; j < max_one_size; j++) {
                printf("%d %d %s %s %s\\n", status, id[j], name[j], date[j], attr[j]);
            }
        }
        else if (status == OCI_NO_DATA) {
            ub4 ret_val = 0, ret_size = 0;
﻿
            //获取属性值之后会通过循环打印变量值校验
            if ((OCIAttrGet((void*)ctxptr->stmthp, (ub4)OCI_HTYPE_STMT, (void*)&ret_val, (ub4*)&ret_size,
                            (ub4)OCI_ATTR_ROW_COUNT, ctxptr->errhp)) != OCI_SUCCESS) {
                printf("DCIAttrGet fail\\n");
                break;
            }
            printf("SUCCESS: OCIAttrGet()\\n");
            for (j = 0; j < ret_val; j++) {
                printf("%d %d %s %s %s\\n", status, id[j], name[j], date[j], attr[j]);
            }
        }
﻿
﻿
        ret = 0;
    } while (0);
    //释放stmt句柄
    if (ctxptr->stmthp != NULL) {
        status = OCIHandleFree((dvoid*)ctxptr->stmthp, (ub4)OCI_HTYPE_STMT);
        if (status != OCI_SUCCESS) {
            printf("OCIHandleFree OCI_HTYPE_STMT failed\\n");
            return OCI_ERROR;
        }
        ctxptr->stmthp = NULL;
    }
﻿
    if (ret == -1) {
        return OCI_ERROR;
    }
    return OCI_SUCCESS;
}
﻿
sword oci_alloc_connect(bool useLogon, OCI_GLOBAL_CONTEXT* ctxptr, char* uname, char* passwd, char* dbname)
{
    sword err = OCI_SUCCESS;
    do{
        //初始化，属于准备过程，无实际运行结果
        err = OCIInitialize((ub4)OCI_DEFAULT, (dvoid*)0, (dvoid * (*)(dvoid*, size_t))0,
                            (dvoid * (*)(dvoid*, dvoid*, size_t))0, (void (*)(dvoid*, dvoid*))0);
        if (err != OCI_SUCCESS) {
            printf("FAILED: OCIInitialize()\\n");
            break;
        }
﻿
        printf("SUCCESS: OCIInitialize()初始化OCI应用环境\\n");
﻿
        //申请env句柄，属于准备过程，无实际运行结果
        err = OCIEnvInit((OCIEnv**)&ctxptr->envhp, OCI_DEFAULT, 0, NULL);
        if (err != OCI_SUCCESS) {
            printf("FAILED: OCIEnvInit()\\n");
            break;
        }
        printf("SUCCESS: OCIEnvInit()初始化环境句柄\\n");
﻿
﻿
        //申请错误句柄，属于准备过程，无实际运行结果
        if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->errhp, (ub4)OCI_HTYPE_ERROR, (size_t)0,
                            (dvoid**)0)) {
            printf("FAILED: OCIHandleAlloc() on ctxptr->errhp\\n");
            break;
        }
﻿
        //申请 srv 句柄，属于准备过程，无实际运行结果
        if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->srvhp, (ub4)OCI_HTYPE_SERVER, (size_t)0,
                            (dvoid**)0)) {
            printf("FAILED: OCIHandleAlloc() on ctxptr->srvhp\\n");
            break;
        }
﻿
        //申请 svch 句柄，属于准备过程，无实际运行结果
        if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->svchp, (ub4)OCI_HTYPE_SVCCTX, (size_t)0,
                            (dvoid**)0)) {
            printf("FAILED: OCIHandleAlloc() on ctxptr->svchp\\n");
            break;
        }
﻿
        //申请 auth 句柄，属于准备过程，无实际运行结果
        if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->authp, (ub4)OCI_HTYPE_SESSION, (size_t)0,
                            (dvoid**)0)) {
            printf("FAILED: OCIHandleAlloc() on ctxptr->authp\\n");
            break;
        }
        printf("SUCCESS: OCIHandleAlloc()分配句柄\\n");
﻿
        //申请执行语句
        if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->stmthp, (ub4)OCI_HTYPE_STMT,
                                        (CONST size_t)0, (dvoid**)0)) {
            printf("FAILED: OCIHandleAlloc() on ctxptr->stmthp\\n");
            break;
        }
﻿
﻿
        //非测试接口，属于辅助接口
        if (OCIServerAttach(ctxptr->srvhp, ctxptr->errhp, (text*)dbname, (sb4)strlen((char*)dbname),
                            (ub4)OCI_DEFAULT)) {
            printf("FAILED: OCIServerAttach()\\n");
            break;
        }
﻿
        //将 srvh 属性设置到 svch 句柄，属于准备过程，无实际运行结果
        if (OCIAttrSet((dvoid*)ctxptr->svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid*)ctxptr->srvhp, (ub4)0,
                        (ub4)OCI_ATTR_SERVER, ctxptr->errhp)) {
            printf("FAILED: OCIAttrSet() server attribute\\n");
            break;
        }
        printf("SUCCESS: OCIAttrSet()设置环境句柄属性\\n"); //注意，OCIHandleAlloc申请其他句柄过程中已经将其保存到环境句柄，OCIAttrSet将srvhp句柄属性保存到svchp过程相当于也同时保存到env
﻿
        //将 uname 设置到 auth 句柄，属于准备过程，无实际运行结果
        if (OCIAttrSet((dvoid*)ctxptr->authp, (ub4)OCI_HTYPE_SESSION, (dvoid*)uname, (ub4)strlen((char*)uname),
                        (ub4)OCI_ATTR_USERNAME, ctxptr->errhp)) {
            printf("FAILED: OCIAttrSet() userid\\n");
            break;
        }
        printf("SUCCESS: OCIAttrSet()设置会话句柄用户名\\n");
﻿
        //将 passwd 设置到 auth 句柄，属于准备过程，无实际运行结果
        if (OCIAttrSet((dvoid*)ctxptr->authp, (ub4)OCI_HTYPE_SESSION, (dvoid*)passwd, (ub4)strlen((char*)passwd),
                        (ub4)OCI_ATTR_PASSWORD, ctxptr->errhp)) {
            printf("FAILED: OCIAttrSet() passwd\\n");
            break;
        } 
        printf("SUCCESS: OCIAttrSet()设置会话句柄密码\\n");
﻿
        //开启一个会话，后续代码中有数据库操作，可以证明该接口生效
        if (OCISessionBegin((dvoid*)ctxptr->svchp, ctxptr->errhp, ctxptr->authp, (ub4)OCI_CRED_RDBMS,
                            (ub4)OCI_DEFAULT)) {
            text sqlstate[6] = "", msg[256] = "";
            sb4  errcode = 0;
            OCIErrorGet(ctxptr->errhp, 1, sqlstate, &errcode, msg, 256, OCI_HTYPE_ERROR);
            printf("SUCCESS: OCIErrorGet()读取错误信息\\n");
            printf("initialize failed: %d %s\\n", errcode, msg);
            break;
        }
        printf("SUCCESS: 创建并开始一个用户会话\\n");
﻿
        //将 auth 设置到 svchp 句柄，属于准备过程，无实际运行结果
        if (OCIAttrSet((dvoid*)ctxptr->svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid*)ctxptr->authp, (ub4)0,
                        (ub4)OCI_ATTR_SESSION, ctxptr->errhp)) {
            printf("FAILED: OCIAttrSet() session\\n");
            break;
        }
﻿
        return OCI_SUCCESS;
    } while (0);
    return OCI_ERROR;
}
﻿
void oci_checkerr(OCIError* errhp, sword status)
{
    text errbuf[512] = {'\\0'};
    sb4  errcode;
﻿
    if (status == OCI_SUCCESS) {
        return;
    }
﻿
    switch (status) {
        case OCI_SUCCESS_WITH_INFO: {
            printf("Error - OCI_SUCCESS_WITH_INFO\\n");
            OCIErrorGet((void*)errhp, (ub4)1, (text*)NULL, &errcode, errbuf, (ub4)sizeof(errbuf), (ub4)OCI_HTYPE_ERROR);
            printf("Error %d - %s\\n", errcode, errbuf);
            break;
        }
        case OCI_NEED_DATA: {
            printf("Error - OCI_NEED_DATA\\n");
            break;
        }
        case OCI_NO_DATA: {
            printf("Error - OCI_NO_DATA\\n");
            break;
        }
        case OCI_ERROR: {
            OCIErrorGet((void*)errhp, (ub4)1, (text*)NULL, &errcode, errbuf, (ub4)sizeof(errbuf), (ub4)OCI_HTYPE_ERROR);
            printf("OCI_ERROR %d - %s\\n", errcode, errbuf);
            break;
        }
        case OCI_INVALID_HANDLE: {
            printf("Error - OCI_INVALID_HANDLE\\n");
            break;
        }
        case OCI_STILL_EXECUTING: {
            printf("Error - OCI_STILL_EXECUTING\\n");
            break;
        }
        case OCI_CONTINUE: {
            printf("Error - OCI_CONTINUE\\n");
            break;
        }
        default: {
            printf("Error - %d\\n", status);
            break;
        }
    }
}
//退出，并释放对应的句柄资源
sword oci_free_disconnect(bool useLogon, OCI_GLOBAL_CONTEXT* ctxptr)
{
    sword err = OCI_SUCCESS;
﻿
    printf("SUCCESS: 结束释放资源，断开数据库连接，释放句柄\\n");
    //结束会话
    err = OCILogoff(ctxptr->svchp, ctxptr->errhp);
    if (err != OCI_SUCCESS) {
        printf("FAILED: OCILogoff()\\n");
    }
﻿
    //句柄释放
    if (ctxptr->svchp != NULL) {
        err = OCIHandleFree((dvoid*)ctxptr->svchp, (ub4)OCI_HTYPE_SVCCTX);
        if (err != OCI_SUCCESS) {
            printf("FAILED: OCIHandleFree() on ctxptr->svchp\\n");
        }
        ctxptr->svchp = NULL;
    }
    if (ctxptr->errhp) {
        err           = OCIHandleFree((dvoid*)ctxptr->errhp, (ub4)OCI_HTYPE_ERROR);
        ctxptr->errhp = NULL;
    }
﻿
    if (ctxptr->envhp) {
        err           = OCIHandleFree((dvoid*)ctxptr->envhp, (ub4)OCI_HTYPE_ENV);
        ctxptr->envhp = NULL;
    }
﻿
    return err;
}
﻿
sword oci_exec_sample_sql(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode)
{
    sword err      = OCI_SUCCESS;
    bool  new_stmt = false;
﻿
    do {
        //SQL 准备，无实际运行结果
        err = OCIStmtPrepare(ctxptr->stmthp, ctxptr->errhp, (const text*)sql, (ub4)strlen((char*)sql),
                             (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
        if (err != OCI_SUCCESS) {
            text sqlstate[6] = "", msg[256] = "";
            sb4  errcode = 0;
﻿
            OCIErrorGet(ctxptr->errhp, 1, sqlstate, &errcode, msg, 256, OCI_HTYPE_ERROR);
            printf("OCIStmtPrepare failed: %d %s\\n", errcode, msg);
            break;
        }
﻿
        //SQL 执行，并未提交，需要执行 OCITransCommit 后方可验证
        err = OCIStmtExecute(ctxptr->svchp, ctxptr->stmthp, ctxptr->errhp, 1, 0, (OCISnapshot*)0, (OCISnapshot*)0, mode);
        if (err != OCI_SUCCESS) {
            text sqlstate[6] = "", msg[256] = "";
            sb4  errcode = 0;
﻿
            OCIErrorGet(ctxptr->errhp, 1, sqlstate, &errcode, msg, 256, OCI_HTYPE_ERROR);
            printf("OCIStmtExecute failed: %d %s\\n", errcode, msg);
            break;
        }
﻿
        //提交
        //具体，SQL 命令执行：\\d case1_bindbypos_define，可查看表已经存在
        if (mode == OCI_DEFAULT) {
            err = OCITransCommit(ctxptr->svchp, ctxptr->errhp, (ub4)OCI_DEFAULT);
            if (err != OCI_SUCCESS) {
                printf("OCITransCommit failed\\n");
                break;
            }
        }
    } while (0);
﻿
    if (new_stmt && ctxptr->stmthp != NULL) {
        //释放句柄，无实际运行结果
        err = OCIHandleFree((dvoid*)ctxptr->stmthp, (ub4)OCI_HTYPE_STMT);
        if (err != OCI_SUCCESS) {
            printf("OCIHandleFree failed\\n");
        }
        ctxptr->stmthp = NULL;
    }
﻿
    return err;
}
﻿
sword oci_alloc_handle(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode)
{
    sword err      = OCI_SUCCESS;
    bool  new_stmt = false;
﻿
    do {
        //申请执行语句
        if (ctxptr->stmthp == NULL) {
            if (OCIHandleAlloc((dvoid*)ctxptr->envhp, (dvoid**)&ctxptr->stmthp, (ub4)OCI_HTYPE_STMT, (CONST size_t)0,
                               (dvoid**)0)) {
                printf("FAILED: alloc statement handle\\n");
                break;
            }
            else {
                new_stmt = true;
            }
        }
    } while (0);
﻿
    return err;
}
﻿
sword oci_free_handle(OCI_GLOBAL_CONTEXT* ctxptr, char* sql, ub4 mode)
{
    sword err      = OCI_SUCCESS;
﻿
    if ( ctxptr->stmthp != NULL) {
        //释放句柄，无实际运行结果
        err = OCIHandleFree((dvoid*)ctxptr->stmthp, (ub4)OCI_HTYPE_STMT);
        if (err != OCI_SUCCESS) {
            printf("OCIHandleFree failed\\n");
        }
        ctxptr->stmthp = NULL;
    }
﻿
    return err;
}
﻿
1. 修改示例代码中 uname、passwd、dbname 三个变量为用户名、密码、在 pg_service.conf 中配置的数据源名称（username,password,db_name）。
2. 运行：
cd $PG_OCI_PATH
./run.sh
结果：
SUCCESS: OCIInitialize()初始化OCI应用环境
SUCCESS: OCIEnvInit()初始化环境句柄
SUCCESS: OCIHandleAlloc()分配句柄
SUCCESS: OCIAttrSet()设置环境句柄属性
SUCCESS: OCIAttrSet()设置会话句柄用户名
SUCCESS: OCIAttrSet()设置会话句柄密码
SUCCESS: 创建并开始一个用户会话
SUCCESS: OCIDefineByPos ()定义输出变量
SUCCESS: OCIStmtFetch()获取数据
0 11 AAAAAAAAAAAAAA BBBBBBBBB CCCCCCCCCCCCCC
0 12 AAAAAAAAAAAAAA BBBBBBBBB CCCCCCCCCCCCCC
0 13 AAAAAAAAAAAAAA BBBBBBBBB CCCCCCCCCCCCCC
0 14 AAAAAAAAAAAAAA BBBBBBBBB CCCCCCCCCCCCCC
0 15 AAAAAAAAAAAAAA BBBBBBBBB CCCCCCCCCCCCCC
SUCCESS: 结束释放资源，断开数据库连接，释放句柄
使用 OCI 连接 TDSQL PG

获取数据库连接信息

安装驱动依赖

编写 OCI 测试程序
