文章存档
分类：
2006-11-07 16:48:22
oci程序结构:
1>. 初始化oci程序环境和线程.
2>. 分配需要的句柄和建立服务联结, 用户会话.
3>. 按自己的需要来书写sql语句, 准备与服务器交换数据.
4>. 执行sql语句, 准备一个新的会话或回到步骤3.
5>. 终止用户会话和服务联接.
6>. 释放句柄.
句柄:
句柄就是指向一块由oci函数分配的存储区, 他同我们长说的指针没什么区别. 你可以使用句柄来访问存储的内容或联结信息. 当然你不可以直接访问的, 因为你不知道你所指的这块存储的结构信息(也就是数据怎么存放的, 关键是数据的存放次序以及这些数据的含义是什么.)
要访问存储区的信息就必需用oci函数库了, 使用oci函数库可以让我们没有必要关心句柄下面的内容是什么, 你只需要知道他是一个指针, 但具体指向什么地方就不是开发人员关心的了. 不知道我说了这么一堆有没有说清楚(不懂就: ganhk(at)dsgdata.com).
ociattrget() and ociattrset()
这是大部分oci句柄修改自己属性的函数.
oci句柄的类型有:
----------------------------------------------------------------------
描叙 c中的数据类型 句柄类型
----------------------------------------------------------------------
oci环境句柄 ocienv oci_htype_env
oci错误 ocierror oci_htype_error
oci服务内容句柄 ocisvcctx oci_htype_svcctx
oci语句句柄 ocistmt oci_htype_stmt
oci邦定句柄 ocibind oci_htype_bind
oci定义句柄 ocidefine oci_htype_define
oci描叙句柄 ocidescribe oci_htype_describe
oci服务句柄 ociserver oci_htype_server
oci用户会话句柄 ocisession oci_htype_session
oci授权信息句柄 ociauthinfo oci_htype_authinfo
oci联接池句柄 ocicpool oci_htype_cpool
oci会话池句柄 ocispool oci_htype_spool
oci事物句柄 ocitrans oci_htype_trans
oci复杂对象句柄 ocicomplexobject oci_htype_complexobject
oci线程句柄 ocithreadhandle n/a
oci订阅句柄 ocisubscription oci_htype_subscription
oci直接路径内容句柄 ocidirpathctx oci_htype_dirpath_ctx
oci直接路径功能内容句柄 ocidirpathfuncctx oci_htype_dirpath_fn_ctx
oci直接路径列数组句柄 ocidirpathcolarray oci_htype_dirpath_column_array
oci直接路径流句柄 ocidirpathstream oci_htype_dirpath_stream
oci进程句柄 ociprocess oci_htype_process
-----------------------------------------------------------------------------
句柄的层次性:
环境句柄(ocienv) |--- 用户会话句柄 (ocisession)
|--- 直接路径内容句柄 (ocidirpathctx)
|--- 线程句柄 (ocithreadhandle)
|--- 复杂对象句柄 (ocicomplexobject)
|--- 订阅句柄 (ocisubscription)
|--- 描叙句柄 (ocidescribe)
|--- 语句句柄 (ocistmt)
|--- 服务内容句柄 (ocisvcctx)
|--- 错误句柄 (ocierror)
|--- 服务器句柄 (ociserver)
|--- 联结池句柄 (ocicpool)
所有用户分配句柄和初始化函数ocihandlealloc(), 有点像malloc(). 线程句柄分配空间和初始化函数ocithreadhndinit(). 所有句柄的空间释放函数ocihandlefree(), 有点象free().
环境句柄(ocienv):
ocienv的创建函数ocienvcreate() 或 ocienvnlscreate().
环境句柄是所有句柄的基础, 他指向一块内存区, 其他的句柄在该句柄的基础上进行别的句柄空间的分配和操作. 当在多线程的程序中如果只有一个环境句柄时, 注意多线程操作同一个环境时的锁问题!
错误句柄(ocierror)
错误句柄几乎在所有的程序中都要使用, 他指向一块存放错误信息的地方, 当执行完一个oci函数后如果出错就可以通过ocierrorget()来提起错误信息.
#################################
oci中的数据类型($oracle_home/rdbms/demo/oratypes.h)
--------------------------------
typedef unsigned char ub1;
typedef signed char sb1;
typedef char eb1;
typedef sb1 b1;
---------------------------------
typedef unsigned short ub2;
typedef signed short sb2;
typedef short eb2;
typedef sb2 b2;
---------------------------------
typedef unsigned int ub4;
typedef signed int sb4;
typedef int eb4;
typedef sb4 b4;
typedef eb4 deword;
typedef ub4 duword;
typedef sb4 dsword;
typedef dsword dword;
typedef ub4 dsize_t;
---------------------------------
typedef unsigned long ub8;
typedef signed long sb8;
---------------------------------
typedef unsigned char text;
typedef unsigned char oratext;
typedef unsigned short utext;
typedef int boolean;
---------------------------------
typedef int eword;
typedef unsigned int uword;
typedef signed int sword;
---------------------------------
typedef unsigned long ubig_ora;
typedef signed long sbig_ora;
---------------------------------
#define const const
#define dvoid void
############################################
eg1:
该示例是仿照$oracle_home/rdbms/demo/cdemocp.c文件修改的.
测试环境:
os: redhat 9.0
kernel: 2.4.20-8
oracle: 9.2.0.4.0
------------
makefile
------------
objs=cdemocp.o
obj_exe=cdemocp
all: ${obj_exe}
${obj_exe}: ${objs}
/usr/bin/gcc -l${oracle_home}/lib/ -l${oracle_home}/rdbms/lib -o $@ $< -lclntsh -ldl -lm
.suffixes: .cc .c .o
.c.o:
/usr/bin/gcc -i. -i/usr/include \
-i${oracle_home}/rdbms/demo -i${oracle_home}/rdbms/public \
-i${oracle_home}/rdbms/plsql/public -i${oracle_home}/network/public \
-i${oracle_home}/precomp/public -c $<
clean:
rm -f *.o
rm -f ${obj_exe}
------------
cdemocp.c
------------
#include
#include
#include
#include
static void check_error(ocierror *errhp, sword status)
{
text errbuf[512];
sb4 errcode;
switch (status)
{
case oci_success:
break;
case oci_success_with_info:
fprintf(stderr, "error - oci_success_with_info\n");
break;
case oci_need_data:
fprintf(stderr, "error - oci_need_data\n");
break;
case oci_no_data:
fprintf(stderr, "error - oci_no_data\n");
break;
case oci_error:
errcode = 0;
ocierrorget((dword *)errhp, (ub4)1, (text *)null, &errcode,
errbuf, (ub4)sizeof(errbuf), oci_htype_error);
fprintf(stderr, "error - %s\n", errbuf);
break;
case oci_invalid_handle:
fprintf(stderr, "error - oci_invalid_handle\n");
break;
case oci_still_executing:
fprintf(stderr, "error - oci_still_executing\n");
break;
case oci_continue:
fprintf(stderr, "error - oci_continue\n");
break;
default:
fprintf(stderr, "error - unknown error code\n");
break;
}
}
/* execute select */
static void query_rows(ocienv *envhp, ocierror *errhp,
const oratext *username, const oratext *password, const oratext *database)
{
int i;
sword lstat, status;
text sqlstr[256];
float emp_sal;
ub4 emp_no, emp_dept;
text emp_name[10], emp_job[9];
ocistmt *stmthp = (ocistmt *)0;
ocisvcctx *svchp = (ocisvcctx *)0;
ocidefine *defhp1 = (ocidefine *)0,
*defhp2 = (ocidefine *)0,
*defhp3 = (ocidefine *)0,
*defhp4 = (ocidefine *)0,
*defhp5 = (ocidefine *)0;
/* login oracle */
lstat = ocilogon(envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const char *)username),
(const oratext *)password, (ub4)strlen((const char *)password),
(const oratext *)database, (ub4)strlen((const char *)database));
if (lstat)
{
check_error(errhp, lstat);
return;
}
sprintf(sqlstr, "select empno, ename, job, sal, deptno from emp order by empno");
ocihandlealloc(envhp, (dvoid **)&stmthp, oci_htype_stmt,
(size_t)0, (dvoid **)0);
check_error(errhp, ocistmtprepare(stmthp, errhp,
(text *)sqlstr, (ub4)strlen((const char *)sqlstr), oci_ntv_syntax, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp1, errhp, (ub4)1,
(dvoid *)&emp_no, (sb4)sizeof(emp_no), (ub2)sqlt_int,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp2, errhp, (ub4)2,
(dvoid *)&emp_name, (sb4)sizeof(emp_name), (ub2)sqlt_str,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp3, errhp, (ub4)3,
(dvoid *)&emp_job, (sb4)sizeof(emp_job), (ub2)sqlt_str,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp4, errhp, (ub4)4,
(dvoid *)&emp_sal, (sb4)sizeof(emp_sal), (ub2)sqlt_flt,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp5, errhp, (ub4)5,
(dvoid *)&emp_dept, (sb4)sizeof(emp_dept), (ub2)sqlt_int,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
if (lstat = ocistmtexecute(svchp, stmthp, errhp, (ub4)0,
(ub4)0, (ocisnapshot *)0, (ocisnapshot *)0, oci_default))
{
check_error(errhp, lstat);
return;
}
status = 0;
status = ocistmtfetch(stmthp, errhp, 1, oci_fetch_next, oci_default);
while (status != oci_no_data)
{
printf("emp_no: %u \t emp_name: %s \t job: %s \tsalary: %7.2f \tdept_no: %u\n",
emp_no, emp_name, emp_job, emp_sal, emp_dept);
status = ocistmtfetch(stmthp, errhp, 1, oci_fetch_next, oci_default);
}
check_error(errhp, ocihandlefree((dvoid *)stmthp, oci_htype_stmt));
check_error(errhp, ocilogoff((dvoid *)svchp, errhp));
}
static void insert_rows(ocienv *envhp, ocierror *errhp,
const oratext *username, const oratext *password,
const oratext *database, text *sqlstr)
{
sword lstat;
ocisvcctx *svchp = (ocisvcctx *)0;
ocistmt *stmthp = (ocistmt *)0;
if (lstat = ocilogon(envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const char *)username),
(const oratext *)password, (ub4)strlen((const char *)password),
(const oratext *)database, (ub4)strlen((const char *)database)))
{
check_error(errhp, lstat);
return;
}
ocihandlealloc(envhp, (dvoid **)&stmthp, oci_htype_stmt,
(size_t)0, (dvoid **)0);
check_error(errhp, ocistmtprepare(stmthp, errhp,
(const oratext *)sqlstr, (ub4)strlen((const char *)sqlstr),
oci_ntv_syntax, oci_default));
check_error(errhp, ocistmtexecute(svchp, stmthp, errhp,
(ub4)1, (ub4)0, (ocisnapshot *)0, (ocisnapshot *)0, oci_default));
check_error(errhp, ocitranscommit(svchp, errhp, (ub4)0));
check_error(errhp, ocihandlefree((dvoid *)stmthp, oci_htype_stmt));
check_error(errhp, ocilogoff((dvoid *)svchp, errhp));
}
int main(int argc, char **argv)
{
const oratext *database = (oratext *)"";
const oratext *username = (oratext *)"scott";
const oratext *password = (oratext *)"tiger";
ocienv *envhp;
ocierror *errhp;
text sqlstr[256];
ub4 conmax, conincr, conmin;
ocienvcreate(&envhp, oci_threaded, (dvoid *)0, null, null, null, 0, (dvoid **)0);
ocihandlealloc((dvoid *)envhp, (dvoid *)&errhp, oci_htype_error,
(size_t)0, (dvoid **)0);
conmax = 3;
conmin = 1;
conincr= 1;
sprintf(sqlstr, "insert into emp(empno, ename, job, sal, deptno) values(30, 'gan', 'dba', 9000, 10)");
printf("\ninsert values to the table: \n%s\n", sqlstr);
insert_rows(envhp, errhp, username, password, database, sqlstr);
printf("\ndisplay the table information.\n");
query_rows(envhp, errhp, username, password, database);
check_error(errhp, ocihandlefree((dvoid *)errhp, oci_htype_error));
return (0);
}
##################################
说明:
in 该参数提供输入功能; out 该参数将会返回值或该参数所指的内容将会被修改.
功能: 为用户创建一个简单的会话联接.
sword ocilogon ( ocienv *envhp, /* in 环境句柄 */
ocierror *errhp, /* in/out 错误句柄 */
ocisvcctx **svchp, /* in/out 服务内容句柄 */
const oratext *username, /* in oracle用户名 */
ub4 uname_len, /* in 用户名长度 */
const oratext *password, /* in 该用户密码 */
ub4 passwd_len, /* in 密码长度 */
const oratext *dbname, /* in 数据库sid, 本地可以用"", 远程数据库见tnsnames.ora文件 */
ub4 dbname_len /* in sid的长度 */
);
创建会话的目的关键是返回了一个svchp句柄, 在接下来的操作中将会很有用. 当然ocilogoff()的操作就与logon()的操作相反了.
---------------------------
功能: 关闭会话
sword ocilogoff ( ocisvcctx *svchp, /* in 服务内容句柄 */
ocierror *errhp /* in/out */
);
---------------------------
功能: 创建一个会话, 该会话可能是已经建立了的联接池中的一个虚联接.
sword ocilogon2( ocienv *envhp,
ocierror *errhp,
ocisvcctx **svchp,
const oratext *username,
ub4 uname_len,
const oratext *password,
ub4 passwd_len,
const oratext *dbname, /* in 对与联接池来说该处应该为联接池名 */
ub4 dbname_len, /* in 对与联接池来说该处应该为联接池名长度 */
ub4 mode /* in 见下面 */
);
mode可以为:
oci_default 设置为这个与用ocilogon()函数功能一样
oci_logon2_stmtcache 允许sql语句缓存
对于联接池合法值有:
oci_logon2_cpool
oci_cpool
如:$oracle_home/rdbms/demo/cdemocp.c中的语句:
/* create the connection pool for multi-thread */
lstat = ociconnectionpoolcreate (envhp, errhp, poolhp,
&poolname, &poolnamelen, database, (sub4)strlen((const char *)database),
conmin, conmax, conincr,
appusername, (sb4)strlen((const char *)appusername), apppassword, (sb4)strlen((const char *)apppassword),
oci_default);
/* logon in connection pool mode */
if (lstat = ocilogon2 (envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const signed char *)username),
(const oratext *)password, (ub4)strlen((const signed char *)password),
(const oratext *)poolname, (ub4)poolnamelen, oci_cpool))
------------------------------------------
创建一个联接池.
sword ociconnectionpoolcreate (
ocienv *envhp,
ocierror *errhp,
ocicpool *poolhp, /* in 已经分配了空间的联接池句柄 */
oratext **poolname, /* out 联接池名 */
sb4 **poolnamelen, /* out 联接池名的长度 */
const oratext *dblink, /* in 数据库联接sid */
sb4 dblinklen, /* in 联接字符的长度 */
ub4 connmin, /* in 联接池中最小联接数 */
ub4 connmax, /* in 联接池中最大联接数 */
ub4 connincr, /* in 联接池中联接数每次增加数 */
const oratext *poolusername, /* in 创建联接池用户名 */
ub4 pooluserlen, /* in 用户名长度 */
const oratext *poolpassword, /* in 密码 */
ub4 poolpasslen, /* in 密码长度 */
ub4 mode /* in 见下面 */
);
mode可以为:
oci_default 通常就使用该参数
oci_cpool_reinitialize 可以动态的改变联接池属性(如: connmin, connmax, connincr)
与创建联接池相反的函数:ociconnectionpooldestroy();
sword ociconnectionpooldestroy (
ocicpool *poolhp,
ocierror *errhp,
ub4 mode /* 目前该函数只支持oci_default功能 */
);
---------------------------------------
1>. 初始化oci程序环境和线程.
2>. 分配需要的句柄和建立服务联结, 用户会话.
3>. 按自己的需要来书写sql语句, 准备与服务器交换数据.
4>. 执行sql语句, 准备一个新的会话或回到步骤3.
5>. 终止用户会话和服务联接.
6>. 释放句柄.
句柄:
句柄就是指向一块由oci函数分配的存储区, 他同我们长说的指针没什么区别. 你可以使用句柄来访问存储的内容或联结信息. 当然你不可以直接访问的, 因为你不知道你所指的这块存储的结构信息(也就是数据怎么存放的, 关键是数据的存放次序以及这些数据的含义是什么.)
要访问存储区的信息就必需用oci函数库了, 使用oci函数库可以让我们没有必要关心句柄下面的内容是什么, 你只需要知道他是一个指针, 但具体指向什么地方就不是开发人员关心的了. 不知道我说了这么一堆有没有说清楚(不懂就: ganhk(at)dsgdata.com).
ociattrget() and ociattrset()
这是大部分oci句柄修改自己属性的函数.
oci句柄的类型有:
----------------------------------------------------------------------
描叙 c中的数据类型 句柄类型
----------------------------------------------------------------------
oci环境句柄 ocienv oci_htype_env
oci错误 ocierror oci_htype_error
oci服务内容句柄 ocisvcctx oci_htype_svcctx
oci语句句柄 ocistmt oci_htype_stmt
oci邦定句柄 ocibind oci_htype_bind
oci定义句柄 ocidefine oci_htype_define
oci描叙句柄 ocidescribe oci_htype_describe
oci服务句柄 ociserver oci_htype_server
oci用户会话句柄 ocisession oci_htype_session
oci授权信息句柄 ociauthinfo oci_htype_authinfo
oci联接池句柄 ocicpool oci_htype_cpool
oci会话池句柄 ocispool oci_htype_spool
oci事物句柄 ocitrans oci_htype_trans
oci复杂对象句柄 ocicomplexobject oci_htype_complexobject
oci线程句柄 ocithreadhandle n/a
oci订阅句柄 ocisubscription oci_htype_subscription
oci直接路径内容句柄 ocidirpathctx oci_htype_dirpath_ctx
oci直接路径功能内容句柄 ocidirpathfuncctx oci_htype_dirpath_fn_ctx
oci直接路径列数组句柄 ocidirpathcolarray oci_htype_dirpath_column_array
oci直接路径流句柄 ocidirpathstream oci_htype_dirpath_stream
oci进程句柄 ociprocess oci_htype_process
-----------------------------------------------------------------------------
句柄的层次性:
环境句柄(ocienv) |--- 用户会话句柄 (ocisession)
|--- 直接路径内容句柄 (ocidirpathctx)
|--- 线程句柄 (ocithreadhandle)
|--- 复杂对象句柄 (ocicomplexobject)
|--- 订阅句柄 (ocisubscription)
|--- 描叙句柄 (ocidescribe)
|--- 语句句柄 (ocistmt)
|--- 服务内容句柄 (ocisvcctx)
|--- 错误句柄 (ocierror)
|--- 服务器句柄 (ociserver)
|--- 联结池句柄 (ocicpool)
所有用户分配句柄和初始化函数ocihandlealloc(), 有点像malloc(). 线程句柄分配空间和初始化函数ocithreadhndinit(). 所有句柄的空间释放函数ocihandlefree(), 有点象free().
环境句柄(ocienv):
ocienv的创建函数ocienvcreate() 或 ocienvnlscreate().
环境句柄是所有句柄的基础, 他指向一块内存区, 其他的句柄在该句柄的基础上进行别的句柄空间的分配和操作. 当在多线程的程序中如果只有一个环境句柄时, 注意多线程操作同一个环境时的锁问题!
错误句柄(ocierror)
错误句柄几乎在所有的程序中都要使用, 他指向一块存放错误信息的地方, 当执行完一个oci函数后如果出错就可以通过ocierrorget()来提起错误信息.
#################################
oci中的数据类型($oracle_home/rdbms/demo/oratypes.h)
--------------------------------
typedef unsigned char ub1;
typedef signed char sb1;
typedef char eb1;
typedef sb1 b1;
---------------------------------
typedef unsigned short ub2;
typedef signed short sb2;
typedef short eb2;
typedef sb2 b2;
---------------------------------
typedef unsigned int ub4;
typedef signed int sb4;
typedef int eb4;
typedef sb4 b4;
typedef eb4 deword;
typedef ub4 duword;
typedef sb4 dsword;
typedef dsword dword;
typedef ub4 dsize_t;
---------------------------------
typedef unsigned long ub8;
typedef signed long sb8;
---------------------------------
typedef unsigned char text;
typedef unsigned char oratext;
typedef unsigned short utext;
typedef int boolean;
---------------------------------
typedef int eword;
typedef unsigned int uword;
typedef signed int sword;
---------------------------------
typedef unsigned long ubig_ora;
typedef signed long sbig_ora;
---------------------------------
#define const const
#define dvoid void
############################################
eg1:
该示例是仿照$oracle_home/rdbms/demo/cdemocp.c文件修改的.
测试环境:
os: redhat 9.0
kernel: 2.4.20-8
oracle: 9.2.0.4.0
------------
makefile
------------
objs=cdemocp.o
obj_exe=cdemocp
all: ${obj_exe}
${obj_exe}: ${objs}
/usr/bin/gcc -l${oracle_home}/lib/ -l${oracle_home}/rdbms/lib -o $@ $< -lclntsh -ldl -lm
.suffixes: .cc .c .o
.c.o:
/usr/bin/gcc -i. -i/usr/include \
-i${oracle_home}/rdbms/demo -i${oracle_home}/rdbms/public \
-i${oracle_home}/rdbms/plsql/public -i${oracle_home}/network/public \
-i${oracle_home}/precomp/public -c $<
clean:
rm -f *.o
rm -f ${obj_exe}
------------
cdemocp.c
------------
#include
#include
#include
#include
static void check_error(ocierror *errhp, sword status)
{
text errbuf[512];
sb4 errcode;
switch (status)
{
case oci_success:
break;
case oci_success_with_info:
fprintf(stderr, "error - oci_success_with_info\n");
break;
case oci_need_data:
fprintf(stderr, "error - oci_need_data\n");
break;
case oci_no_data:
fprintf(stderr, "error - oci_no_data\n");
break;
case oci_error:
errcode = 0;
ocierrorget((dword *)errhp, (ub4)1, (text *)null, &errcode,
errbuf, (ub4)sizeof(errbuf), oci_htype_error);
fprintf(stderr, "error - %s\n", errbuf);
break;
case oci_invalid_handle:
fprintf(stderr, "error - oci_invalid_handle\n");
break;
case oci_still_executing:
fprintf(stderr, "error - oci_still_executing\n");
break;
case oci_continue:
fprintf(stderr, "error - oci_continue\n");
break;
default:
fprintf(stderr, "error - unknown error code\n");
break;
}
}
/* execute select */
static void query_rows(ocienv *envhp, ocierror *errhp,
const oratext *username, const oratext *password, const oratext *database)
{
int i;
sword lstat, status;
text sqlstr[256];
float emp_sal;
ub4 emp_no, emp_dept;
text emp_name[10], emp_job[9];
ocistmt *stmthp = (ocistmt *)0;
ocisvcctx *svchp = (ocisvcctx *)0;
ocidefine *defhp1 = (ocidefine *)0,
*defhp2 = (ocidefine *)0,
*defhp3 = (ocidefine *)0,
*defhp4 = (ocidefine *)0,
*defhp5 = (ocidefine *)0;
/* login oracle */
lstat = ocilogon(envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const char *)username),
(const oratext *)password, (ub4)strlen((const char *)password),
(const oratext *)database, (ub4)strlen((const char *)database));
if (lstat)
{
check_error(errhp, lstat);
return;
}
sprintf(sqlstr, "select empno, ename, job, sal, deptno from emp order by empno");
ocihandlealloc(envhp, (dvoid **)&stmthp, oci_htype_stmt,
(size_t)0, (dvoid **)0);
check_error(errhp, ocistmtprepare(stmthp, errhp,
(text *)sqlstr, (ub4)strlen((const char *)sqlstr), oci_ntv_syntax, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp1, errhp, (ub4)1,
(dvoid *)&emp_no, (sb4)sizeof(emp_no), (ub2)sqlt_int,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp2, errhp, (ub4)2,
(dvoid *)&emp_name, (sb4)sizeof(emp_name), (ub2)sqlt_str,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp3, errhp, (ub4)3,
(dvoid *)&emp_job, (sb4)sizeof(emp_job), (ub2)sqlt_str,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp4, errhp, (ub4)4,
(dvoid *)&emp_sal, (sb4)sizeof(emp_sal), (ub2)sqlt_flt,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
check_error(errhp, ocidefinebypos(stmthp, &defhp5, errhp, (ub4)5,
(dvoid *)&emp_dept, (sb4)sizeof(emp_dept), (ub2)sqlt_int,
(dvoid *)0, (ub2 *)0, (ub2 *)0, oci_default));
if (lstat = ocistmtexecute(svchp, stmthp, errhp, (ub4)0,
(ub4)0, (ocisnapshot *)0, (ocisnapshot *)0, oci_default))
{
check_error(errhp, lstat);
return;
}
status = 0;
status = ocistmtfetch(stmthp, errhp, 1, oci_fetch_next, oci_default);
while (status != oci_no_data)
{
printf("emp_no: %u \t emp_name: %s \t job: %s \tsalary: %7.2f \tdept_no: %u\n",
emp_no, emp_name, emp_job, emp_sal, emp_dept);
status = ocistmtfetch(stmthp, errhp, 1, oci_fetch_next, oci_default);
}
check_error(errhp, ocihandlefree((dvoid *)stmthp, oci_htype_stmt));
check_error(errhp, ocilogoff((dvoid *)svchp, errhp));
}
static void insert_rows(ocienv *envhp, ocierror *errhp,
const oratext *username, const oratext *password,
const oratext *database, text *sqlstr)
{
sword lstat;
ocisvcctx *svchp = (ocisvcctx *)0;
ocistmt *stmthp = (ocistmt *)0;
if (lstat = ocilogon(envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const char *)username),
(const oratext *)password, (ub4)strlen((const char *)password),
(const oratext *)database, (ub4)strlen((const char *)database)))
{
check_error(errhp, lstat);
return;
}
ocihandlealloc(envhp, (dvoid **)&stmthp, oci_htype_stmt,
(size_t)0, (dvoid **)0);
check_error(errhp, ocistmtprepare(stmthp, errhp,
(const oratext *)sqlstr, (ub4)strlen((const char *)sqlstr),
oci_ntv_syntax, oci_default));
check_error(errhp, ocistmtexecute(svchp, stmthp, errhp,
(ub4)1, (ub4)0, (ocisnapshot *)0, (ocisnapshot *)0, oci_default));
check_error(errhp, ocitranscommit(svchp, errhp, (ub4)0));
check_error(errhp, ocihandlefree((dvoid *)stmthp, oci_htype_stmt));
check_error(errhp, ocilogoff((dvoid *)svchp, errhp));
}
int main(int argc, char **argv)
{
const oratext *database = (oratext *)"";
const oratext *username = (oratext *)"scott";
const oratext *password = (oratext *)"tiger";
ocienv *envhp;
ocierror *errhp;
text sqlstr[256];
ub4 conmax, conincr, conmin;
ocienvcreate(&envhp, oci_threaded, (dvoid *)0, null, null, null, 0, (dvoid **)0);
ocihandlealloc((dvoid *)envhp, (dvoid *)&errhp, oci_htype_error,
(size_t)0, (dvoid **)0);
conmax = 3;
conmin = 1;
conincr= 1;
sprintf(sqlstr, "insert into emp(empno, ename, job, sal, deptno) values(30, 'gan', 'dba', 9000, 10)");
printf("\ninsert values to the table: \n%s\n", sqlstr);
insert_rows(envhp, errhp, username, password, database, sqlstr);
printf("\ndisplay the table information.\n");
query_rows(envhp, errhp, username, password, database);
check_error(errhp, ocihandlefree((dvoid *)errhp, oci_htype_error));
return (0);
}
##################################
说明:
in 该参数提供输入功能; out 该参数将会返回值或该参数所指的内容将会被修改.
功能: 为用户创建一个简单的会话联接.
sword ocilogon ( ocienv *envhp, /* in 环境句柄 */
ocierror *errhp, /* in/out 错误句柄 */
ocisvcctx **svchp, /* in/out 服务内容句柄 */
const oratext *username, /* in oracle用户名 */
ub4 uname_len, /* in 用户名长度 */
const oratext *password, /* in 该用户密码 */
ub4 passwd_len, /* in 密码长度 */
const oratext *dbname, /* in 数据库sid, 本地可以用"", 远程数据库见tnsnames.ora文件 */
ub4 dbname_len /* in sid的长度 */
);
创建会话的目的关键是返回了一个svchp句柄, 在接下来的操作中将会很有用. 当然ocilogoff()的操作就与logon()的操作相反了.
---------------------------
功能: 关闭会话
sword ocilogoff ( ocisvcctx *svchp, /* in 服务内容句柄 */
ocierror *errhp /* in/out */
);
---------------------------
功能: 创建一个会话, 该会话可能是已经建立了的联接池中的一个虚联接.
sword ocilogon2( ocienv *envhp,
ocierror *errhp,
ocisvcctx **svchp,
const oratext *username,
ub4 uname_len,
const oratext *password,
ub4 passwd_len,
const oratext *dbname, /* in 对与联接池来说该处应该为联接池名 */
ub4 dbname_len, /* in 对与联接池来说该处应该为联接池名长度 */
ub4 mode /* in 见下面 */
);
mode可以为:
oci_default 设置为这个与用ocilogon()函数功能一样
oci_logon2_stmtcache 允许sql语句缓存
对于联接池合法值有:
oci_logon2_cpool
oci_cpool
如:$oracle_home/rdbms/demo/cdemocp.c中的语句:
/* create the connection pool for multi-thread */
lstat = ociconnectionpoolcreate (envhp, errhp, poolhp,
&poolname, &poolnamelen, database, (sub4)strlen((const char *)database),
conmin, conmax, conincr,
appusername, (sb4)strlen((const char *)appusername), apppassword, (sb4)strlen((const char *)apppassword),
oci_default);
/* logon in connection pool mode */
if (lstat = ocilogon2 (envhp, errhp, &svchp,
(const oratext *)username, (ub4)strlen((const signed char *)username),
(const oratext *)password, (ub4)strlen((const signed char *)password),
(const oratext *)poolname, (ub4)poolnamelen, oci_cpool))
------------------------------------------
创建一个联接池.
sword ociconnectionpoolcreate (
ocienv *envhp,
ocierror *errhp,
ocicpool *poolhp, /* in 已经分配了空间的联接池句柄 */
oratext **poolname, /* out 联接池名 */
sb4 **poolnamelen, /* out 联接池名的长度 */
const oratext *dblink, /* in 数据库联接sid */
sb4 dblinklen, /* in 联接字符的长度 */
ub4 connmin, /* in 联接池中最小联接数 */
ub4 connmax, /* in 联接池中最大联接数 */
ub4 connincr, /* in 联接池中联接数每次增加数 */
const oratext *poolusername, /* in 创建联接池用户名 */
ub4 pooluserlen, /* in 用户名长度 */
const oratext *poolpassword, /* in 密码 */
ub4 poolpasslen, /* in 密码长度 */
ub4 mode /* in 见下面 */
);
mode可以为:
oci_default 通常就使用该参数
oci_cpool_reinitialize 可以动态的改变联接池属性(如: connmin, connmax, connincr)
与创建联接池相反的函数:ociconnectionpooldestroy();
sword ociconnectionpooldestroy (
ocicpool *poolhp,
ocierror *errhp,
ub4 mode /* 目前该函数只支持oci_default功能 */
);
---------------------------------------
给主人留下些什么吧！~~