这一节我们用代码实现一个实际的例子,来演示连接池的用法。例子中我们仅使用单线程来演示用法,例子的步骤如下。
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()函数的用法。