OCI编程高级篇（二十）使用连接池的例子

访问www.tomcoding.com网站，学习Oracle内部数据结构，详细文档说明，下载Oracle的exp/imp，DUL，logminer，ASM工具的源代码，学习高技术含量的内容。
这一节我们用代码实现一个实际的例子，来演示连接池的用法。例子中我们仅使用单线程来演示用法，例子的步骤如下。
1. 创建一个OCI环境，分配错误句柄。
2. 创建一个连接池。
3. 从连接池中获取一个会话。
4. 通过这个会话从数据库中查询一个数字，SQL语句为select 10 from dual。
5. 释放会话。
6. 销毁连接池。
7. 释放OCI环境。
例子代码如下，关键处有详细注释。
 

#include "stdio.h"
#include "stdlib.h"
#include "stdint.h"
#include "memory.h"
#include "string.h"
#include "time.h"
#include "errno.h"
#include "oci.h"

/* global variable defination */
OCIEnv          *envhp  = NULL;         /* env handle */
OCIError        *errhp  = NULL;         /* error handle */
OCISvcCtx       *svchp  = NULL;         /* service context handle */
OCICPool        *poolhp = NULL;         /* connection pool handle */
OCIAuthInfo     *authp  = NULL;         /* authentication information handle */
OCIStmt         *stmthp = NULL;         /* statement handle */

text            errbuf[512];            /* error message text */
struct {
    char        uname[32];              /* 认证用户名称 */
    char        upwd[32];               /* 认证用户密码 */
} inputs;
static void usage(const char *prg){
    fprintf(stderr,
        "Usage: %s user/password\n\n"
        "  user      oracle user\n"
        "  password  user password\n"
        , prg
    );
}
/* 解析命令行中的认证用户和密码 */
static int parse_inputs(int argc, char *argv[]){
    char        *p;

    if (argc < 2) {
      usage(argv[0]);
      return (-1);
    }

    if ((p=strchr(argv[1], '/')) == NULL) {
      usage(argv[0]);
      return (-1);
    }

    *p++ = '\0';
    memset(&inputs, 0, sizeof(inputs));
    strncpy(inputs.uname, argv[1], 31);
    strncpy(inputs.upwd, p, 31);

    return (0);
}
static char ora_env[][16] =
{
  "ORACLE_HOME",
  "ORACLE_SID",
  ""
};
/* 检查Oracle运行的环境变量 */
static int check_ora_env(void){
    int         i;

    for (i=0; ora_env[i][0]; i++) {
        if (getenv(ora_env[i]) == NULL) {
            fprintf(stderr, "env %s not set.\n", ora_env[i]);
            return (-1);
        }
    }

    return (0);
}
/* 创建连接池函数 */
static int create_connection_pool(void){
    sword       rc;
    sb4         ec;
    ub4         conn_min;
    ub4         conn_max;
    ub4         conn_inc;
    sb4         pool_name_len;
    char        *pool_name;

    /* 创建环境句柄 */
    rc = OCIEnvCreate(
        &envhp,                 /* envhpp */
        OCI_DEFAULT,            /* mode */
        (void *)NULL,           /* ctxp */
        NULL,
        NULL,
        NULL,
        (size_t)0,
        (void **)NULL
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIEnvCreate() - allocate OCI env handle error !\n");
        return (-1);
    }

    /* 分配错误句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&errhp,
        OCI_HTYPE_ERROR,
        0,
        (void **)NULL
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate OCI error handle error !\n");
        return (-1);
    }

    /* 分配连接池句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&poolhp,
        OCI_HTYPE_CPOOL,
        (size_t)0,
        (void **)NULL
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate connection pool handle error !\n");
        return (-1);
    }

    /* 创建连接池，初始连接数为2，最大连接数为5，每次增长1个连接 */
    conn_min = 2;
    conn_max = 5;
    conn_inc = 1;
    rc = OCIConnectionPoolCreate(
        envhp, errhp, poolhp,
        (OraText **)&pool_name, &pool_name_len,
        (const OraText *)"", 0,
        (ub4)conn_min, (ub4)conn_max, (ub4)conn_inc,
        (OraText *)inputs.uname, strlen(inputs.uname),
        (OraText *)inputs.upwd, strlen(inputs.upwd),
        OCI_DEFAULT
    );

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIConnectionPoolCreate() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 显示一下连接池的名称 */
    fprintf(stdout, "connection pool name: [%d]%s\n", pool_name_len, pool_name);

    return (0);
}
/* 销毁连接池 */
static int destroy_connection_pool(void){
    sword       rc;
    sb4         ec;

    /* 销毁连接池 */
    if (poolhp != NULL) {
        rc = OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

        if (rc != OCI_SUCCESS) {
            OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
            fprintf(stderr, "OCIConnectionPoolDestroy() - [%d] %s\n", ec, errbuf);
            return (-1);
        }

        /* 释放连接池句柄 */
        rc = OCIHandleFree((void *)poolhp, OCI_HTYPE_CPOOL);

        if (rc != OCI_SUCCESS) {
            fprintf(stderr, "OCIHandleFree() - free connection pool handle error !\n");
            return (-1);
        }
    }

    /* 释放错误句柄 */
    if (errhp != NULL) {
        rc = OCIHandleFree(
            errhp,
            OCI_HTYPE_ERROR
        );

        if (rc != OCI_SUCCESS) {
            fprintf(stderr, "OCIHandleFree() - free error handle error !\n");
            return (-1);
        }
    }

    /* 释放环境句柄 */
    if (envhp != NULL) {
        rc = OCIHandleFree(
            envhp,
            OCI_HTYPE_ENV
        );

        if (rc != OCI_SUCCESS) {
            fprintf(stderr, "OCIHandleFree() - free env handle error !\n");
            return (-1);
        }
    }

    return (0);
}

 

/* 从数据库中查询数值 */
static int query_from_dual(void){
    sword       rc;
    sb4         ec;
    int         slen;
    ub4         u4;
    char        sqltxt[1024];
    OCIDefine   *defp;

    /* 分配认证信息句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&authp,
        OCI_HTYPE_AUTHINFO,
        (size_t)0,
        (void **)NULL
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate authentication handle error !\n");
        return (-1);
    }

    /* 设置认证用户名称 */
    rc = OCIAttrSet(
        (void *)authp,
        OCI_HTYPE_AUTHINFO,
        (dvoid *)inputs.uname,
        strlen(inputs.uname),
        OCI_ATTR_USERNAME,
        errhp
    );

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "Set User Attribute - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 设置认证用户密码 */
    rc = OCIAttrSet(
        (void *)authp,
        OCI_HTYPE_AUTHINFO,
        (void *)inputs.upwd,
        strlen(inputs.upwd),
        OCI_ATTR_PASSWORD,
        errhp
    );

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "Set Password Attribute - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 从连接池获取会话 */
    rc = OCISessionGet(envhp, errhp, &svchp, authp,
        (OraText *)pool_name, pool_name_len,
        NULL, 0, NULL, NULL, NULL,
        OCI_SESSGET_CPOOL
    );

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCISessionGet() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 分配OCI语句句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&stmthp,
        OCI_HTYPE_STMT,
        0,
        (void **)NULL
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate statement handle error !\n");
        return (-1);
    }

    /* 产生SQL语句文本 */
    strcpy(sqltxt, "select 10 from dual");
    slen = strlen(sqltxt);

    /* 准备语句 */
    rc = OCIStmtPrepare(stmthp, errhp, (const OraText *)sqltxt, slen,
          OCI_NTV_SYNTAX, OCI_DEFAULT);

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtPrepare() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 定义输出变量 */
    rc = OCIDefineByPos(stmthp,
        &defp,
        errhp,
        (ub4)1,
        (void *)&u4,
        (sb4)4,
        (ub2)SQLT_INT,
        (void *)NULL,
        (ub2 *)NULL,
        (ub2 *)NULL,
        (ub4)OCI_DEFAULT
    );

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIDefineByPos() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 执行查询语句 */
    rc = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT);

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtExecute() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 取回结果集 */
    rc = OCIStmtFetch2(stmthp, errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtFetch2() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 显示从数据库查询到的数值 */
    fprintf(stdout, "number from dual is: %d\n", u4);

    /* 释放语句句柄 */
    rc = OCIHandleFree(
        stmthp,
        OCI_HTYPE_STMT
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleFree() - free statement handle error !\n");
        return (-1);
    }

    /* 释放连接池会话 */
    rc = OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);

    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCISessionRelease() - [%d] %s\n", ec, errbuf);
        return (-1);
    }

    /* 释放认证信息句柄 */
    rc = OCIHandleFree(
        authp,
        OCI_HTYPE_AUTHINFO
    );

    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleFree() - free authentication handle error !\n");
        return (-1);
     }

    return (0);
}
/* 主函数 */
int main(int argc, char *argv[]){
    /* 解析命令行参数 */
    if (parse_inputs(argc, argv) < 0)
        return (-1);

    /* 检查Oracle运行环境变量是否设置 */
    if (check_ora_env() < 0)
        return (-1);

    /* 创建连接池 */
    if (create_connection_pool() < 0)
        goto error_exit;

    /* 从数据库中查询数值 */
    if (query_from_dual() < 0)
        fprintf(stderr, "query operation error !\n");

    /* 销毁连接池 */
    destroy_connection_pool();

    return (0);

error_exit:
    destroy_connection_pool();
    return (-1);
}
例子中使用了OCISessionGet()函数来获取连接池会话，要比使用OCILogon2()函数复杂一些，仔细看一下，Oracle提供的示例代码中没有演示OCISessionGet()函数的用法。
 
/* 从数据库中查询数值 */static int query_from_dual(void){
    sword       rc;
    sb4         ec;
    int         slen;
    ub4         u4;
    char        sqltxt[1024];
    OCIDefine   *defp;
 
    /* 分配认证信息句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&authp,
        OCI_HTYPE_AUTHINFO,
        (size_t)0,
        (void **)NULL
    );
 
    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate authentication handle error !\n");
        return (-1);
    }
 
    /* 设置认证用户名称 */
    rc = OCIAttrSet(
        (void *)authp,
        OCI_HTYPE_AUTHINFO,
        (dvoid *)inputs.uname,
        strlen(inputs.uname),
        OCI_ATTR_USERNAME,
        errhp
    );
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "Set User Attribute - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 设置认证用户密码 */
    rc = OCIAttrSet(
        (void *)authp,
        OCI_HTYPE_AUTHINFO,
        (void *)inputs.upwd,
        strlen(inputs.upwd),
        OCI_ATTR_PASSWORD,
        errhp
    );
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "Set Password Attribute - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 从连接池获取会话 */
    rc = OCISessionGet(envhp, errhp, &svchp, authp,
        (OraText *)pool_name, pool_name_len,
        NULL, 0, NULL, NULL, NULL,
        OCI_SESSGET_CPOOL
    );
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCISessionGet() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 分配OCI语句句柄 */
    rc = OCIHandleAlloc(
        (void *)envhp,
        (void **)&stmthp,
        OCI_HTYPE_STMT,
        0,
        (void **)NULL
    );
 
    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleAlloc() - allocate statement handle error !\n");
        return (-1);
    }
 
    /* 产生SQL语句文本 */
    strcpy(sqltxt, "select 10 from dual");
    slen = strlen(sqltxt);
 
    /* 准备语句 */
    rc = OCIStmtPrepare(stmthp, errhp, (const OraText *)sqltxt, slen,
          OCI_NTV_SYNTAX, OCI_DEFAULT);
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtPrepare() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 定义输出变量 */
    rc = OCIDefineByPos(stmthp,
        &defp,
        errhp,
        (ub4)1,
        (void *)&u4,
        (sb4)4,
        (ub2)SQLT_INT,
        (void *)NULL,
        (ub2 *)NULL,
        (ub2 *)NULL,
        (ub4)OCI_DEFAULT
    );
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIDefineByPos() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 执行查询语句 */
    rc = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT);
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtExecute() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 取回结果集 */
    rc = OCIStmtFetch2(stmthp, errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCIStmtFetch2() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 显示从数据库查询到的数值 */
    fprintf(stdout, "number from dual is: %d\n", u4);
 
    /* 释放语句句柄 */
    rc = OCIHandleFree(
        stmthp,
        OCI_HTYPE_STMT
    );
 
    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleFree() - free statement handle error !\n");
        return (-1);
    }
 
    /* 释放连接池会话 */
    rc = OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
 
    if (rc != OCI_SUCCESS) {
        OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
        fprintf(stderr, "OCISessionRelease() - [%d] %s\n", ec, errbuf);
        return (-1);
    }
 
    /* 释放认证信息句柄 */
    rc = OCIHandleFree(
        authp,
        OCI_HTYPE_AUTHINFO
    );
 
    if (rc != OCI_SUCCESS) {
        fprintf(stderr, "OCIHandleFree() - free authentication handle error !\n");
        return (-1);
     }
 
    return (0);
}
/* 主函数 */int main(int argc, char *argv[]){
    /* 解析命令行参数 */
    if (parse_inputs(argc, argv) < 0)
        return (-1);
 
    /* 检查Oracle运行环境变量是否设置 */
    if (check_ora_env() < 0)
        return (-1);
 
    /* 创建连接池 */
    if (create_connection_pool() < 0)
        goto error_exit;
 
    /* 从数据库中查询数值 */
    if (query_from_dual() < 0)
        fprintf(stderr, "query operation error !\n");
 
    /* 销毁连接池 */
    destroy_connection_pool();
 
    return (0);
 
error_exit:
    destroy_connection_pool();
    return (-1);
}
posted @ 2025-08-14 22:02 汤姆花花阅读(12) 评论(0) 收藏举报
刷新页面返回顶部
汤姆花花

OCI编程高级篇（二十） 使用连接池的例子

公告

OCI编程高级篇（二十）使用连接池的例子
