OCI编程历程

    前几天和哥们聊天，谈到连接数据库及一些数据库调用接口的开发问题。那哥们直接来了一句：“那东西没什么搞头，就调用些函数，然后做些错误处理”……… 我很是郁闷，回想起来实习时第一个能拿得出手的程序就是对OCI10封装库，当时为了测试效果一个人在机房里呆了一个多月，每天不停的拔网线。后来还把这个封装库写在简历中项目经历的第二项。虽然现在看起来封装得有点幼稚，但还不至于一文不值。把我憋个够！想想还是把它写下来吧。
    PS：本篇主要对OCI编程进行一些入门介绍，还涉及一些非阻塞和即时客户端配置的内容，所以高手请绕行。本人水平有限可能存在很多错误。

 

一、问题

    为什么要使用OCI？可以使用ODBC对oracle数据库进行连接啊。当然这在widows系统上一点问题都没有，但如果程序需要跨多个不同的平台使用ODBC就有一定的问题。不是还有unixODBC和iODBC吗？……在unix平台上使用MS推出的东西进行开发还是让我这种转牛角尖的菜鸟程序员难以接受。

    实习时所在的部门一直使用OCI来访问oracle数据库，所有的版本是oci7.3。对！没看错一直使用的就是这个96年的oci版本。确实太老了！这么多年没有换版本主要是没有人再去维护它，还有勉强可用；另一方面，就像我那哥们讲的那样，大家都觉得这东西没什么可以做的了。

    一切事物都逃不过产生、发展和消亡的命运。而在事物消亡的过程中总能找到几个标志性的事件来见证这个过程。部门里使用的oci7.3的接口库也逃不出这个规律：有一次在给某大领导演示公司产品前十几分钟，所有的演示用工作站都开好，静等领导的到来。这时几个同事在搬动机器的时候不小心把系统中唯一的数据库服务器的网线碰掉了(单点运行就是不靠谱)，导致所有数据库连接的工作站都阻塞了，演示用机像死机似地没有一点反应。这下子把大家忙坏了，赶紧把数据库服务器的网线插上，重启工作站的对应进程。还好在领导来到的时候，所有演示用机都重新准备好了。这件事加速原有的oci7.3接口库退出历史的舞台。大概在08年10月，老大给我分配了改造这个oci接口的任务，要求：1.使用新的oci版本，本来想用oci9，但后来发现oci9的一些版本不支持某些特殊的功能(本文后面会提到)，所以后面选用03年出的oci10；2.接口函数尽量兼容原有版本，这样别人更换版本的时候不需要修改太多。当然还要跨平台、调用过程方便简洁等等等等；3.执行查询语句时，每次获取N行(可以由调用者设定)放入调用者指定的缓冲区直至取完，当不满N行时取实际的行数放入缓冲区，并返回实际取到的行数；4.与数据库的连接断开后能返回错误，并提够重连机制。这个要求也算是在服务器短时间内不会发生变化的情况下，一种用客户端来保证稳定性的妥协做法吧。因为能oci接口如果能检测连接断开，那么调用者就可以采取一些处理，比如在本地缓存数据库的操作，或者向使用者发出连接断开的提示等。5.用业余时间完成。

 

 

二、OCI编程的一般过程

    与OCI7.3中使用的宿主语言定义变量存储空间(很拗口,不用管它)的方式不同，OCI9以后已不再使用原有的变量结构来初始化及维护数据库的信息，而改用句柄的形式来和Oracle数据库进行交互。将常用的句柄定义在一个结构内，方便程序维护：

typedef struct _OCI_HANDLE
  {
	OCIEnv     *phEnv;        //环境句柄，要使用oracle数据库，必须首先获得环境句柄
	OCISvcCtx  *phService;    //oracle的服务句柄，也可以说是连接句柄。
	OCIError   *phErr;        //oracle的错误句柄，可以获取错误信息
	OCIStmt    *phStmt;       //oracle的语句描述句柄
	OCIServer  *phServer;     //Oracle的服务器句柄
	OCISession *phSession;    //Oracle会话句柄
  }OCIHANDLE, *LPOCIHANDLE;

    OCI9编程的一般步骤有：初始化环境句柄、生成其他各类句柄、建立数据库连接进行登录、执行SQL语句，对返回的结果进行处理、终止用户会话，断开连接，释放各种句柄。  

     上图给出OCI初始化的一个过程，OCI能初始化成功的前提当然是系统中已经安装或设置了Oracle的client端(在本文第四节有介绍)。其中步骤(3)到(7)都分别调用OCIHandleAlloc()函数进行分配，顺序可以不同，它们都只依赖环境句柄；步骤(1)和(2)可以使用OCIEnvCreate()函数替换掉，这两种的初始化OCI环境的方法在不同的使用条件下是不同的，一般建议使用OCIEnvCreate()代替OCIInitialize()和OCIEnvInit()，因为OCIInitialize()和OCIEnvInit()主要是为了backwards-compatible。而如果是编写DLL更是应该使用OCIEnvCreate()函数，user‘guide是这样说的：

    If you are writing a DLL or a shared library using OCI library then this call should definitely be used instead of OCIInitialize() and OCIEnvInit() call.

    OCI各句柄初始化完毕后，接下来就是连接数据库，如下图：

    数据库连接好后可以执行SQL语句：一条SQL语句在OCI应用程序中的执行步骤一般如下：(1)准备SQL语句。(2)在SQL语句中绑定需要输入到SQL语句中的变量。(3)执行SQL语句。(4)获取SQL中的输出描述。(5)定义输出变量。(6)获取数据。具体过程及过程中调用的函数如下图所示。对于SQL中的定义语句（如CREATE，DROP）和控制语句（如GRANT，REVOKE），由于没有数据的输入输出，只需要图2中第一步和第三步即可。操作语句（如INSERT，DELETE，UPDATE）则需要执行前三步。而查询语句（如SELECT）不仅可能有数据输入，而且也有数据的输出，因此需要执行六个步骤。

     OCI编程的一般过程还是很清晰的，流程图都是一条线………

 

 

三、单次查询返回多行结果的实现

    设计的时候老大要求要像原有接口库那样一次查询返回多行，然后再在本地进行处理，以减少对数据库的访问。这本来是一个很正常的要求，但后面看了好些开源的OCI封装，发现它们的demo里都没有给出如何fetch多行……… 比如写的比较好的ocilib，demo中就没有给出(至少是以前的版本没给出,现在就不知道了)，在写完这个OCI接口库大概半年后，再看ocilib的代码OCIDefineByPos()函数时，发现倒数第三第四个参数都是指针，说明可以fetch多行。进而发现ocilib可以通过OCI_SetFetchSize()函数来设置查询返回的行数。但为什么不在demo里给一个示例呢？就连该版本的文档里也没有这个函数的说明。很奇怪！没办法还是自己动手丰衣足食，使劲啃user’guide。

    需要fetch多行，首先要考虑执行select语句后，接收到数据放在什么地方？当然是放在缓冲区里了，在OCI里通过不同的变量函数绑定来告诉oracle client把从数据库取到的数据存放在什么地方。这里使用OCIDefineByPos()函数。下面以每次取100行为例给出具体步骤：

  1. 分配足够大的缓冲区m_pData = new unsigned char[m_DataLen * 100]。m_DataLen表示数据库表中每一行的长度(各个列长度之和)，这样需要使用unsigned作Buffer，因为如果使用有符号char则取带时间的表会有问题。

  2. 根据各列的长度来定义各列在缓冲区中的位置：

  OCIDefineByPos(m_hOCI.phStmt,　　　　　　　 //语句句柄
		&(m_vecColInfo[i].phdefine),//定义句柄
		m_hOCI.phErr, 　　
		i+1, 　　　　　　　　　　　　　//列序号
		(ub1 *)(&(m_pData[pos])),　//各列的位置pos等于当前列之前各列长度和乘以100
		m_vecColInfo[i].collen,　 　//对应列的长度 SQLT_STR, 
		(m_vecColInfo[i].indp), 　　//指示器，因为每次最多要取100行，所以indp应设为维数为100的数组。
		(m_vecColInfo[i].rlenp), 　//返回数据的真实长度，这里也应把rlenp设为维数100的数组 
		0, 
		OCI_DEFAULT));

    m_vecColInfo为保存各列信息的vector。值得注意的是OCIDefineByPos()的第八和第九个参数：第八个参数是指示器参数，在OCIStmtFectch后只是所取的对应数据是否完整(0表示完整)，由于要取100行，则在m_vecColInfo中每一个列元素对应的结构中都应定义indp[100]的数组。第九个参数用于返回所取数据的实际长度，因此也需要在一个列元素的结构体中定义rlenp[100]的数组。还有一个需要注意的是第四个参数里的pos，pos用于指定该列保存在Buffer中的起始位置。如下表是数据库中某表，执行select查询该表前100行后，数据在缓冲区m_pData中保存数据的形式如图4：

CarKey	MakeKey	ModelKey	ColorKey	Year
1	1	1	2	2003
2	2	1	3	2005
3	2	1	2	2005
…….	…….	……..	……..	………
100	2	1	1	2006

     上图可以看到OCI在fetch多行时，先将第一列的100行数据放入m_pData中，然后以列为单位每次取100行放入m_pData。因此pos变量的赋值应写为：pos +=  100 * (m_vecColInfo[i-1].collen); 其中collen代表该列的长度。

3. 获取数据：

  OCIStmtFetch(m_hOCI.phStmt,
	　　m_hOCI.phErr,
	　　100,//每次取100行的数据
	　　OCI_FETCH_NEXT, 
	　　OCI_DEFAULT);

    第三个参数设置为100后，执行OCIStmtFetch完毕后数据就填充到缓冲区中。这里需要注意的是最后一个fetch，因为最后一次fetch时数据库表中往往已经不足100行，所以每次执行OCIStmtFetch()函数完毕要需要检查其返回值，当返回值为OCI_NO_DATA时使用：

  OCIAttrGet(m_hOCI.phStmt,
	    OCI_HTYPE_STMT, 
	    (dvoid *) &row_fetched, 
	    (ub4 *) NULL, 
	    (ub4) OCI_ATTR_ROWS_FETCHED,
	    m_hOCI.phErr);

    row_fetched将返回剩下的行数，倒数第二个参数为OCI_ATTR_ROWS_FETCHED，在oci.h中是这样定义的：

#define OCI_ATTR_ROWS_FETCHED  197 /* rows fetched in last call */

    在oci10中这里没有任何问题，但在一些较早的oci9版本中找不到OCI_ATTR_ROWS_FETCHED的定义……… 也就是说无法fetch多行？！迫不得已只能用oci10。可能ocilib在demo中没有fetch多行的示例也是出于这个考虑吧。

 

 

四、Oracle即时客户端(instantclient)的配置

    过去使用OCI需要安装oracle的客户端，Oracle的普通客户端一般都很庞大，Windows平台下的客户端就有700M。Oracle公司在10g版本后推出了大小只有30M的InstantClient（即时客户端）作为oracle的访问客户端。不需要安装就可以访问Oracle的服务器。

  Windows平台下instantclient的配置和使用：

    下面以C:\Oracle为例介绍具体的配置过程。
  1.将instantclient的basic包及sqlplus包中所有文件解压至C:\Oracle。 
  2.配置系统的环境变量：

    • 将 C:\Oracle 添加到 PATH 中（位于其他 Oracle 目录之前）。例如，在 Windows 2000 上，依次单击“开始”->“设置”->“控制面板”->“系统”->“高级”->“环境变量”，编辑系统变量列表中的 PATH。WindowXP上，右击“我的电脑”->“高级”->“环境变量”。
    • 添加用户环境变量 TNS_ADMIN 设置为C:\Oracle。
    • 设置必要的 Oracle 全球化语言环境变量， 添加用户环境变量NLS_LANG 中文对应的字符集是 SIMPLIFIED CHINESE_CHINA.ZHS16GBK

  3. 一共设置以下三个环境变量（以解压缩目录C:\Oracle为例）环境变量名 变量值

path C:\Oracle
TNS_ADMIN C:\Oracle
ORACLE_HOME C:\Oracle (可选)
NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK

  4. tnsnames.ora和sqlnet.ora文件，这两个文件可以在所要访问的Oracle数据库服务器的$ORACLE_HOME/network/admin目录下找到，把tnsnames.ora中的服务器主机名改为ip地址即可。需更改时注意备份原来的文件。
  5. 配置完毕后进入C:\Oracle运行sqlplus.exe登陆对应的数据库测试是否设置正确。在windows下使用instantclient时，需要将instantclient的sdk包中的include和lib加到工程中。

Unix平台下instantclient的配置和使用：

本例中使用solaris_x86_10.2.0.2为客户端
    1. 将instantclient_solaris_x86_10.2.0.2中的basic、sqlplus和sdk解压至同一目录，用chmod将该目录下的所有文件设为可读写，比如：chmod –R 777 ./*

    2. 配置环境变量：
      export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/ychellboy/instantclient_10_2/
      ORACLE_HOME=/home/ychellboy/instantclient_10_2/
      export TNS_ADMIN=/home/ychellboy/instantclient_10_2/
   可将上述命令写入一个XXX.sh文件。

    3. tnsnames.ora和sqlnet.ora文件，这两个文件可以在所要访问的Oracle数据库服务器的$ORACLE_HOME/network/admin目录下找到，把tnsnames.ora中的服务器主机名改为ip地址即可。
    4. 配置完毕后进入ORACLE_HOME指定的目录运行sqlplus.exe登陆对应的数据库测试是否设置正确。
    在solaris下使用instantclient时需将链接库定位至ORACLE_HOME下，链接libclntsh打头的库文件即可。

 

 

五、instantclient的Bug

    在开发时，最早下载InstantClient for win32 Version 10.2.0.5版，但怎么配置程序都无法初始化OCI的环境，错误发生在OCIInitialize()函数，换成OCIEnvCreate() 同样有错，设断点调试时报的错误是：弹出对话框“User breakpoint called from code at 0x.......”如下图所示；

    同时Debug窗口输出的错误是：“HEAP[testlib.exe]: Invalid Address specified to RtlFreeHeap( 00140000, 0014F390 )”，下图所示：

     都在还没初始化OCI怎么就溢出了？开始以为是instantclient没有正确配置，但sqlplus是可以使用的。然后怀疑是程序写错了，但使用普通的oracle9i的客户端程序又能正确运行。期间还把VC6换成VS2005，问题依然没有解决。在快要放弃的时候换了InstantClient for win32 Version 10.1.0.5版，一试就通，原来是被oracle耍了。我现在还保留着10.2.0.5版，有兴趣玩一下的站内联系我。

 

 

六、连接断开的错误处理

    据说自从OCI7.3后，OCI的连接可以设置成非阻塞模式。本文第二节可以看到到调用OCI函数和数据建立会话后可以使用OCIAttrSet()函数将会话设置为费阻塞模式，但我没在user‘guide里找到有专门讲那些函数会受到影响的章节……(可能我看得不够仔细)？不知道那些函数操作会受到连接断开的影响，那么即使设置了非阻塞模式也不知道在哪进行错误处理啊！没办法只能自己动手挨个函数试………
    经过多次断网测试后得出以下函数将受到连接断开的影响，注意在windows下拔开网线后不管设置成阻塞或非阻塞这些函数都能返回并给出错误码。

Windows下：

   1) OCIServerAttach：错误码12560，错误信息“TNS协议适配器错误”
   2) OCISessionBegin：错误码12571，错误信息“TNS包写入程序失败”
   3) OCIStmtExecute：如果在此函数执行前断网则返回错误码12571，错误信息“TNS包写入程序失败”，如果在此函数第一次执行(返回OCI_STILL_EXECUTING)到第二次执行之间断网则返回错误码03113，错误信息“通信信道的文件结束”
   4) OCIStmtFetch：错误码12571，错误信息“TNS包写入程序失败”
知道那些函数会受到断网影响后，处理起来就比较简单，即增加对这四个函数返回失败时的错误码判断，在断网时返回相应的错误返回给接口调用者，由调用者选择处理方法。这种通过错误码进行处理的方法并不是长久之计，因为如果Oracle改变所返回的错误码，或者是在不同widows版本下返回的错误码不一致则错误处理失效。不过windows并不是我们的主用系统所以就得过且过了^_^

Unix下：

    Unix下断网情况和windows的情况相差很大，在unix下OCIServerAttach连接建立成功后，将此会话设置为阻塞则真的阻塞！windows下即使设置成阻塞，断网发生时照样返回错误信息。unix下设置为非阻塞则情况又不一样。下面的函数带①表示会话设置为阻塞，断网后会出现的情况。带②则表示会话设置为非阻塞，断网后会出现的情况。①②则表示阻塞、非阻塞情况都一样。
     1）OCIStmtExecute() ===》① 无返回死等，检测不出断网，网络恢复后可正确执行(因为OCI用TCP做连接，可能也会有超时的时间) ② 执行时返回OCI_STILL_EXECUTING
     2）OCIReset() ===》①② 阻塞死等
     3）OCITransCommit() ===》①② 阻塞
     4）OCIStmtFetch() ===》① 阻塞 ② 返回OCI_STILL_EXECUTING
     5）OCISessionEnd() ===》①② 返回错误ORA-03127
     6）OCIServerDetach() ===》①② 无影响可返回
     7）OCIServerAttach() ===》①② 阻塞3~5分钟后返回错误ORA-12170
   可以看到OCIServerAttach()和OCITransCommit()函数是一定阻塞的，不管会话有没有设置成非阻塞模式。我想OCIServerAttach()函数里应该会调用到connect()，为什么Oracle就不做一个非阻塞的connect呢？搞不懂。如果要进行断网的保护，那么在实现该OCI接口库时，使用到这两个函数的接口函数就应该放到子线程里进行调用。在主线程里timewait子线程，一旦子线程操作超时就把它Terminate或cancel掉，然后返回连接断开的错误给调用者。这样干似乎很危险啊，查看了一下内存，发现在Solaris平台下每次打断OCIServerAttach()函数后会产生60K的内存泄露。

    终于写完了，托了好几天。接着闭关去！

 

References：
[1]Oracle® Call Interface Programmer's Guide 10g Release 1(10.1), Part No.B10779-01, December 2003