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功能 */
);
---------------------------------------
阅读(4122) | 评论(0) | 转发(0) |
