在前面我们介绍连接池时,用了单线程编写了一个实例,这不能体现出连接池的优势,连接池更应该用在多线程的环境中。我们介绍了多线程的函数后,今天就用多线程改写一下原来的例子。
还是先创建连接池,然后创建并启动4个线程,每个线程赋值一个数字,然后通过数据库的dual来取得数字,打印出来,用dual来取数目的是为了使用连接池的会话。示例代码如下。
#include "stdio.h"
#include "stdlib.h"
#include "stdint.h"
#include "memory.h"
#include "string.h"
#include "time.h"
#include "errno.h"
#include "oci.h"
/* 定义全局变量 */
OCIEnv *envhp = NULL; /* OCI环境句柄 */
OCIError *errhp = NULL; /* 错误句柄 */
OCICPool *poolhp = NULL; /* 连接池句柄 */
sb4 pool_name_len; /* 连接池名称长度 */
char *pool_name = NULL; /* 连接池名称 */
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;
OraText errbuf[512];
/* 创建OCI环境变量,要使用OCI_THREADED模式 */
rc = OCIEnvCreate(
&envhp, /* envhpp */
OCI_THREADED, /* 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);
}
/* 创建连接池 */
conn_min = 3;
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);
}
return (0);
}
/* 销毁连接池函数 */
static int destroy_connection_pool(void)
{
sword rc;
sb4 ec;
OraText errbuf[512];
/* 销毁连接池 */
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);
}
}
/* 释放OCI环境句柄 */
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 void thread_run(void *arg)
{
sword rc;
sb4 ec;
int num;
int slen;
ub4 u4;
OCIError *errhp1 = NULL;
OCISvcCtx *svchp1 = NULL;
OCIStmt *stmthp = NULL;
OCIDefine *defp;
char sqltxt[1024];
OraText errbuf[512];
/* 创建本线程使用的错误句柄 */
rc = OCIHandleAlloc(
(void *)envhp,
(void **)&errhp1,
OCI_HTYPE_ERROR,
0,
(void **)NULL
);
if (rc != OCI_SUCCESS) {
fprintf(stderr, "OCIHandleAlloc() - allocate error handle error !\n");
return;
}
/* 获取会话,使用了OCILogon2()函数 */
rc = OCILogon2(envhp, errhp1, &svchp1,
(const OraText *)inputs.uname, (ub4)strlen(inputs.uname),
(const OraText *)inputs.upwd, (ub4)strlen(inputs.upwd),
(const OraText *)pool_name, (ub4)pool_name_len,
OCI_CPOOL
);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCILogon2() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
/* 分配语句句柄 */
rc = OCIHandleAlloc(
(void *)envhp,
(void **)&stmthp,
OCI_HTYPE_STMT,
0,
(void **)NULL
);
if (rc != OCI_SUCCESS) {
fprintf(stderr, "OCIHandleAlloc() - allocate statement handle error !\n");
goto thread_error;
}
/* 得到线程传入的参数 */
num = *((int *)arg);
/* 使用会话从dual中获取这个数字 */
sprintf(sqltxt, "select %d from dual", num);
slen = strlen(sqltxt);
/* 准备语句 */
rc = OCIStmtPrepare(stmthp, errhp1, (const OraText *)sqltxt, slen,
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIStmtPrepare() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
/* 定义输出变量 */
rc = OCIDefineByPos(stmthp,
&defp,
errhp1,
(ub4)1,
(void *)&u4,
(sb4)4,
(ub2)SQLT_INT,
(void *)NULL,
(ub2 *)NULL,
(ub2 *)NULL,
(ub4)OCI_DEFAULT
);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIDefineByPos() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
/* 执行查询语句 */
rc = OCIStmtExecute(svchp1, stmthp, errhp1, 0, 0, NULL, NULL, OCI_DEFAULT);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIStmtExecute() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
/* 获取结果集 */
rc = OCIStmtFetch2(stmthp, errhp1, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIStmtFetch2() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
/* 打印取到的数字 */
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");
goto thread_error;
}
stmthp = NULL;
/* 退出会话 */
rc = OCILogoff(svchp1, errhp1);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp1, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCILogoff() - [%d] %s\n", ec, errbuf);
goto thread_error;
}
svchp1 = NULL;
/* 释放错误句柄 */
rc = OCIHandleFree(
errhp1,
OCI_HTYPE_ERROR
);
if (rc != OCI_SUCCESS) {
fprintf(stderr, "OCIHandleFree() - free error handle error !\n");
}
errhp1 = NULL;
return;
thread_error:
if (stmthp != NULL) {
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
}
if (svchp1 != NULL) {
OCILogoff(svchp1, errhp1);
}
if (errhp1 != NULL) {
OCIHandleFree(errhp1, OCI_HTYPE_ERROR);
}
}
/* 定义线程的最大个数,和传入线程的数字数组 */
#define MAX_THREAD 4
int t_num[MAX_THREAD];
int main(int argc, char **argv)
{
int i;
sword rc;
sb4 ec;
OCIThreadId *tid[MAX_THREAD];
OCIThreadHandle *thp[MAX_THREAD];
OraText errbuf[512];
/* 解析命令行参数 */
if (parse_inputs(argc, argv) < 0)
return (-1);
/* 检查OCI程序运行的环境变量 */
if (check_ora_env() < 0)
return (-1);
/* 创建连接池 */
if (create_connection_pool() < 0)
goto error_exit;
/* 初始化OCI线程包 */
OCIThreadProcessInit();
/* 初始化线程包上下文 */
rc = OCIThreadInit(envhp, errhp);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadInit() - [%d] %s\n", ec, errbuf);
}
/* 初始化每个线程的ID和句柄 */
for (i=0; i<MAX_THREAD; i++) {
rc = OCIThreadIdInit(envhp, errhp, &tid[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadIdInit() - [%d] %s\n", ec, errbuf);
}
rc = OCIThreadHndInit(envhp, errhp, &thp[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadHndInit() - [%d] %s\n", ec, errbuf);
}
}
/* 创建和启动每个线程,传入参数 */
for (i=0; i<MAX_THREAD; i++) {
t_num[i] = i+1;
rc = OCIThreadCreate(envhp, errhp, thread_run,
(void *)&t_num[i], tid[i], thp[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadCreate() - [%d] %s\n", ec, errbuf);
}
}
for (i=0; i<MAX_THREAD; i++) {
/* 等待线程结束 */
rc = OCIThreadJoin(envhp, errhp, thp[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadJoin() - [%d] %s\n", ec, errbuf);
}
/* 关闭线程句柄 */
rc = OCIThreadClose(envhp, errhp, thp[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadClose() - [%d] %s\n", ec, errbuf);
}
/* 销毁线程ID */
rc = OCIThreadIdDestroy(envhp, errhp, &tid[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadIdDestroy() - [%d] %s\n", ec, errbuf);
}
/* 销毁线程句柄 */
rc = OCIThreadHndDestroy(envhp, errhp, &thp[i]);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadHndDestroy() - [%d] %s\n", ec, errbuf);
}
}
/* 结束线程包 */
rc = OCIThreadTerm(envhp, errhp);
if (rc != OCI_SUCCESS) {
OCIErrorGet(errhp, 1, NULL, &ec, errbuf, 512, OCI_HTYPE_ERROR);
fprintf(stderr, "OCIThreadTerm() - [%d] %s\n", ec, errbuf);
}
/* 销毁连接池 */
destroy_connection_pool();
return (0);
error_exit:
destroy_connection_pool();
return (-1);
}