OTL使用指南

1 OTL简介

OTL 是 Oracle, Odbcand DB2-CLI Template Library 的缩写，是一个C++编译中操控关系数据库的模板库，它目前几乎支持当前所有的各种主流数据库，例如Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase /Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。

OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行，操作DB2数据库则是通过CLI接口进行，至于MS的数据库和其它一些数据库，OTL只提供了ODBC的操作方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式进行操纵。

在MS Windows and Unix 平台下，OTL目前支持的数据库版本主要有：Oracle 7 (直接使用 OCI7), Oracle 8 (直接使用 OCI8), Oracle 8i (直接使用OCI8i), Oracle 9i (直接使用OCI9i), Oracle 10g (直接使用OCI10g), DB2 (直接使用DB2 CLI), ODBC 3.x ,ODBC 2.5。目前OTL的最新版本为4.0，参见http://otl.sourceforge.net/，下载地址http://otl.sourceforge.net/otlv4_h.zip。

2 编译OTL

OTL是一个集成库，它包含了一个模板流框架(template stream framework)以及适配OCI7, OCI8, OCI8i, OCI9i, OCI10g, ODBC 2.5, ODBC 3.x, DB2 CLI和Informix CLI的适配器(OTL-adapters)。编译时需要使用相应的宏定义向编译器指明底层数据库API的类型。例如，如果底层使用ORACLE10g的API，则需要使用宏定义”#defineOTL_ORA10G”。

另外，也可以使用相应的宏定义控制编译器对OTL的编译。例如，如果需要和ACE库一起编译可以使用宏定义”#defineOTL_ACE”, 如果需要OTL为所分配并处理的字符串以空字符结尾成为C风格字符串则可以使用宏定义”#define OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE”等。

所有的相关宏请参见http://otl.sourceforge.net/otl3_compile.htm。

3 基本使用

OTL的一般使用步骤包括：

(1) 使用宏指明底层数据库API类型和控制编译器对OTL的编译。例如：

#define OTL_ORA9I // Compile OTL 4.0/OCI9i

#define OTL_UNICODE //Enable Unicode OTL for OCI9i

(2) 创建otl_connect对象，该对象一般为全局共享的。

(3) 调用otl_connect的静态方法otl_initialize()初始化OTL环境。

(4) 调用otl_connect的rlogon()方法连接数据库。

(5) 创建otl_stream()对象，该对象一般为局部的。

(6) 调用otl_stream的open()方法打开SQL进行解析。

(7) 使用otl_stream的<<操作符绑定SQL中的变量。

(8) 使用otl_stream的>>操作符读取返回结果。

(9) 调用otl_connect的logoff()方法从数据库断开。

下面将通过一个较为全面的示例说明使用OTL连接数据库、创建表和存储过程、调用存储过程、查询记录以及插入记录、从数据库断开的具体代码实现。

  1 #include <stdio.h>
  2 
  3 #include <string.h>
  4 
  5 #include <iostream>
  6 
  7 #include <vector>
  8 
  9 
 10 
 11 #define OTL_ORA9I       // Compile OTL 4.0/OCI9i
 12 
 13 //#define OTL_UNICODE   // Enable Unicode OTL for OCI9i
 14 
 15 #include "otlv4.h"          // include the OTL 4.0 header file
 16 
 17 
 18 
 19 using namespace std;
 20 
 21 
 22 
 23 /**
 24 
 25 *连接数据库
 26 
 27 */
 28 
 29 int OTLConnect(const char* pszConnStr, otl_connect& db)
 30 
 31 {
 32 
 33     try
 34 
 35     {
 36 
 37         otl_connect::otl_initialize(); // initialize OCI environment
 38 
 39         db.rlogon(pszConnStr);
 40 
 41         db.auto_commit_off();
 42 
 43         printf("CONNECT: OK!
");
 44 
 45     }
 46 
 47     catch (otl_exception& p)
 48 
 49     {   // intercept OTL exceptions
 50 
 51         printf("Connect Error: (%s) (%s) (%s)
", p.msg, p.stm_text, p.var_info);
 52 
 53         return -1;
 54 
 55     }
 56 
 57     return 0;
 58 
 59 }
 60 
 61 
 62 
 63 /**
 64 
 65 *从数据库断开
 66 
 67 */
 68 
 69 int OTLDisconnect(otl_connect& db)
 70 
 71 {
 72 
 73     db.commit();
 74 
 75     db.logoff();
 76 
 77 
 78 
 79     printf("DISCONNECT: OK!
");
 80 
 81     return 0;
 82 
 83 }
 84 
 85 
 86 
 87 /**
 88 
 89 *创建数据库表和存储过程
 90 
 91 */
 92 
 93 int OTLExec(otl_connect& db)
 94 
 95 {
 96 
 97     try
 98 
 99     {
100 
101         int nCnt = 0;
102 
103         char strSql[] = "SELECT count(0) FROM user_tables "
104 
105             " WHERE table_name = 'TEST_FTP' ";
106 
107 
108 
109         otl_stream otlCur(1, (const char*)strSql, db);
110 
111         otlCur >> nCnt;
112 
113 
114 
115         if (nCnt == 0)
116 
117         {
118 
119             char strDDL[] =
120 
121                 "create table TEST_FTP                 "
122 
123                 "(                                                                          "
124 
125                 "  AREA_ID         VARCHAR2(100) not null,     "
126 
127                 "  FTP_FILE_NAME   VARCHAR2(100) not null,    "
128 
129                 "  FTP_TIME        VARCHAR2(14),               "
130 
131                 "  FTP_BEGIN_TIME  VARCHAR2(14),                   "
132 
133                 "  FTP_END_TIME    VARCHAR2(14),                   "
134 
135                 "  FTP_MOD_TIME    date,                                 "
136 
137                 "  FTP_SIZE        NUMBER(8),                      "
138 
139                 "  FTP_SOURCE_PATH VARCHAR2(100),                 "
140 
141                 "  FTP_LOCAL_PATH  VARCHAR2(100),                "
142 
143                 "  FTP_RESULT      VARCHAR2(4),                       "
144 
145                 "  FTP_REDO        VARCHAR2(1)                       )";
146 
147 
148 
149             otl_cursor::direct_exec(db, (const char*)strDDL);
150 
151         }
152 
153 
154 
155         char strSqlProc[] = "SELECT count(0) from user_objects "
156 
157             " WHERE object_type = 'PROCEDURE' and object_name = 'PR_REMOVE_FTP' ";
158 
159         otl_stream otlCurProc(1, (const char*)strSqlProc, db);
160 
161         otlCurProc >> nCnt;
162 
163 
164 
165         if (nCnt == 0)
166 
167         {
168 
169             char strProc[] =
170 
171                 "CREATE OR REPLACE procedure pr_remove_ftp             "
172 
173                 "      ( area in varchar2, out_flag out varchar )      "
174 
175                 "AS                                                                                    "
176 
177                 "strtmp varchar2(32);                                                  "
178 
179                 "BEGIN                                                                                    "
180 
181                 "  strtmp := area||'%';                                                        "
182 
183                 "  DELETE FROM TEST_FTP where area_id LIKE strtmp;       "
184 
185                 "  out_flag := 'OK';                                                   "
186 
187                 "END;                                                                                ";
188 
189 
190 
191             otl_cursor::direct_exec(db, (const char*)strProc);
192 
193         }
194 
195 
196 
197     }
198 
199     catch (otl_exception& p)
200 
201     {   // intercept OTL exceptions
202 
203         printf("EXECUTE Error: (%s) (%s) (%s)
", p.msg, p.stm_text, p.var_info);
204 
205     }
206 
207     return 0;
208 
209 }
210 
211 
212 
213 /**
214 
215 *调用存储过程
216 
217 */
218 
219 int OTLProcedure(otl_connect& db)
220 
221 {
222 
223     try
224 
225     {
226 
227 
228 
229         char szData[64], szData1[64], szData2[64], szData3[64];
230 
231         int nSize = 0;
232 
233         char strSql[] = " BEGIN "
234 
235             "  pr_remove_ftp ( :area<char[100],in>, :out<char[100],out> ); "
236 
237             " END; ";
238 
239         otl_stream otlCur(1, (const char*)strSql, db);
240 
241         otlCur.set_commit(0);
242 
243 
244 
245         strcpy(szData, "AREA");
246 
247         memset(szData1, 0, sizeof(szData1));
248 
249         memset(szData2, 0, sizeof(szData2));
250 
251         memset(szData3, 0, sizeof(szData3));
252 
253 
254 
255         otlCur << szData;
256 
257         otlCur >> szData1;
258 
259 
260 
261         printf("PROCEDURE: %s!
", szData1);
262 
263     }
264 
265     catch (otl_exception& p)
266 
267     { // intercept OTL exceptions
268 
269         printf("PROCEDURE Error: (%s) (%s) (%s)
", p.msg, p.stm_text, p.var_info);
270 
271     }
272 
273     return 0;
274 
275 }
276 
277 
278 
279 /**
280 
281 *查询记录
282 
283 */
284 
285 int OTLSelect(otl_connect& db)
286 
287 {
288 
289     try
290 
291     {
292 
293         char szData[64], szData1[64], szData2[64], szData3[64], szRedo[2];
294 
295         int nSize;
296 
297         char strSql[] = " SELECT area_id, ftp_time, ftp_file_name, "
298 
299             " to_char(ftp_mod_time, 'YYYY-MM-DD HH24:MI:SS'), ftp_size "
300 
301             "  FROM TEST_FTP "
302 
303             " WHERE ftp_redo = :ftp_redo<char[2]>";
304 
305         otl_stream otlCur(1, (const char*)strSql, db);
306 
307 
308 
309         strcpy(szRedo, "Y");
310 
311         otlCur << szRedo;
312 
313         while (!otlCur.eof())
314 
315         {
316 
317             memset(szData, 0, sizeof(szData));
318 
319             otlCur >> szData;
320 
321             otlCur >> szData1;
322 
323             otlCur >> szData2;
324 
325             otlCur >> szData3;
326 
327             otlCur >> nSize;
328 
329             printf("SELECT: (%s %s %s %s %d)
",
330 
331                 szData, szData1, szData2, szData3, nSize);
332 
333         }
334 
335     }
336 
337     catch (otl_exception& p)
338 
339     { // intercept OTL exceptions
340 
341         printf("Select Error: (%s) (%s) (%s)
", p.msg, p.stm_text, p.var_info);
342 
343     }
344 
345     return 0;
346 
347 }
348 
349 
350 
351 /**
352 
353 *插入记录
354 
355 */
356 
357 int OTLInsert(otl_connect& db)
358 
359 {
360 
361     try
362 
363     {
364 
365         char szData[64], szData1[64], szData2[9], szData3[64], szRedo[2];
366 
367         int nSize;
368 
369         char strSql[] = " INSERT into TEST_FTP "
370 
371             " ( area_id, ftp_file_name, ftp_time, ftp_mod_time, ftp_size, ftp_redo )"
372 
373             " VALUES ( :area_id<char[100]>, "
374 
375             "      :ftp_file_name<char[100]>, "
376 
377             "      to_char(sysdate,'YYYYMMDDHH24MISS'), "
378 
379             "   to_date(:ftp_mod_time<char[20]>,'YYYYMMDD'), "
380 
381             "      :ftp_size<int>, "
382 
383             "   :ftp_redo<char[2]> ) ";
384 
385         otl_stream otlCur(1, (const char*)strSql, db);
386 
387 
388 
389         otlCur.set_commit(0);
390 
391 
392 
393         for (int i = 1; i < 10; i++)
394 
395         {
396 
397             sprintf(szData, "AREA_%d", i);
398 
399             sprintf(szData1, "FILE_NAME_%d", i);
400 
401             if (i < 5)
402 
403             {
404 
405                 sprintf(szData2, "20070415");
406 
407                 strcpy(szRedo, "Y");
408 
409             }
410 
411             else
412 
413             {
414 
415                 sprintf(szData2, "20070416");
416 
417                 strcpy(szRedo, "N");
418 
419             }
420 
421 
422 
423             memset(szData3, 0, sizeof(szData3));
424 
425             nSize = i * 100;
426 
427 
428 
429             otlCur << szData << szData1 << szData2 << nSize << szRedo;
430 
431         }
432 
433 
434 
435         printf("INSERT: OK!
");
436 
437     }
438 
439     catch (otl_exception& p)
440 
441     { // intercept OTL exceptions
442 
443         printf("INSERT Error: (%s) (%s) (%s)
", p.msg, p.stm_text, p.var_info);
444 
445     }
446 
447     return 0;
448 
449 }
450 
451 
452 
453 /**
454 
455 *主函数
456 
457 */
458 
459 int main(int argc, char *argv[])
460 
461 {
462 
463     otl_connect db;
464 
465     char szConn[64];
466 
467 
468 
469 
470 
471     if (argc >= 2)
472 
473         strcpy(szConn, argv[1]);
474 
475     else
476 
477     {
478 
479         printf("otltest conn_str");
480 
481         return -1;
482 
483     }
484 
485 
486 
487     if (OTLConnect(szConn, db) < 0)
488 
489         return 0;
490 
491     OTLExec(db);
492 
493     OTLProcedure(db);
494 
495     OTLInsert(db);
496 
497     OTLSelect(db);
498 
499     OTLDisconnect(db);
500 
501 
502 
503     return 0;
504 
505 }

4 OTL流的概念

OTL设计者认为，任何SQL语句、PL/SQL块或存储过程调用都被输入和输出变量特征化。例如：

l 一个SELECT语句在其WHERE子句中拥有标量的输入变量，而在其SELECT子句则定义了输出的列，如果SELECT语句返回的是多行记录则输出列是个向量参数。

l 一个INSERT和UPDATE语句需要将数据写入表中，它们拥有输入参数。另外，一个DELETE语句由于需要指明删除记录的类型，同样拥有输入。工业强度的数据库服务器通常也支持批量操作，例如批量的查询、更新、删除和插入，因此INSERT/UPDATE/DELETE语句的参数在批量操作的情况下也可能是向量。

l 一个存储过程可能含有输入和(或)输出参数。通常存储过程的参数是标量，但是也有特例，例如返回的是引用游标(Oracle)或者记录集(MS SQL SERVER或者Sybase)。

l 一个PL/SQL块可能含有输入和(或)输出参数，这些参数可能是标量也可能是向量。

图4-1 OTL的流

因此，任何的SQL或者其程序上的扩展在交互过程中都可以如图4-1所示看作拥有输入和输出的黑盒。OTL通过将数据流和SQL的概念联合起来，用otl_stream类表达这种抽象。

由于SQL语句可能以批量的方式执行，otl_stream是一个缓冲流。它拥有两个独立的缓冲区：输入和输出。输入缓冲区由所有集中到一起的输入参数组成，输出缓冲区则由所有集中到一起的输出变量组成。

OTL流和C++的缓冲流很相似。一个SQL语句或存储过程调用被当作一个普通的缓冲流被打开。OTL流的操作逻辑和C++流操作逻辑基本相同，但是OTL流的输出和输出缓冲区可能重叠。

OTL流拥有flush()方法在输入缓冲区写满的时候将其自动刷新，也含有一系列的<<和>>运算符来读和写不同数据类型的对象。它最重要的优点是为任何类型的SQL语句和存储过程调用提供了统一的接口。应用开发者能够通过熟悉少量的语法和函数名称像使用C++流一样来使用OTL流。

在OTL流的内部拥有一个小型的解析器来解析所声明的绑定变量以及绑定变量的数据类型。因此，免去了使用特殊的绑定函数来绑定已声明的C/C++主机变量(hostvariables)。由于所有必须的缓冲区在OTL流中会自动创建，因此OTL仅仅需要被打开来进行读和写相应的数值。

OTL流接口要求使用OTL异常。OTL流操作都能可能抛掷otl_exception异常。因此为了拦截异常并阻止程序异常终止，必须使用try/catch块来包裹OTL流的使用代码。

OTL流的实现otl_stream具有较高的自动化功能，当OTL流的所有的输入变量被定义好(也就是输入缓冲区被填满)，它会触发OTL流中的黑盒来执行。在黑盒执行的过程中输出缓冲区被填充。在执行完成后，输出缓冲区中的值能够从OTL流中被读取。如果执行的是一个SELECT语句并且返回多于输出缓冲区大小的行，那么在输出缓冲区的内容被读取后，OTL会自动读取下一批行记录到输出缓冲区。

5 主要类及方法说明

5-1 OTL主要类说明

类名	说明
otl_connect	负责创建和处理连接对象以及事务管理。
otl_stream	OTL流概念(参见第4小节)的具体实现。任何具有输入输出的SQL语句，匿名的PL/SQL块或者存储过程能够使用otl_stream类进行C++编程。一般传统的数据库API拥有绑定主机变量到SQL语句中占位符的函数。因此，开发者需要在程序中声明host array，解析SQL语句，调用绑定函数，填充输入变量，执行SQL语句，读输出变量等。这些操作结束后又继续填充输入变量，执行SQL语句，读输出变量。以上的所有事情能够在otl_stream中全部自动完成。otl_stream在保证性能的情况下提供了完全自动的与数据库的交互。 otl_stream的性能主要被缓冲区大小arr_size一个参数控制。缓冲区大小定义了插入表的逻辑行以及与数据库一次往反交互(one round-trip to the database)过程中从表或视图中查询的逻辑行。
otl_exception	可能代表数据库错误也可能代表OTL自身的错误。OTL函数如果在使用底层的数据库API时返回非0的错误码，则会产生otl_exception类型的异常。

4.1otl_stream的主要方法

5-2 类otl_stream的主要方法说明

主要方法	说明
otl_stream( const int arr_size, const char* sqlstm, otl_connect& db, const char* ref_cur_placeholder=0, const char* sqlstm_label=0 );	构造函数，负责创建otl_stream对象并调用open()方法。参数arr_size为流的缓冲区大小，参数db为otl_connect连接对象的引用，参数ref_cur_placeholder为reference cursor的占位符名称，参数sqlstm_label为SQL语句的标签，用于取代异常描述消息otl_exception::stm中默认填充的SQL语句。
void open( const int arr_size, const char* sqlstm, otl_connect& db, const char* ref_cur_placeholder=0, const char* sqlstm_label=0 );	打开SQL语句对其进行解析，所有的输入和输出变量都在流内部别动态分配，并且自动绑定到占位符。
void close(void); #ifdef OTL_STREAM_POOLING_ON void close( constbool save_in_stream_pool=true ); #endif	关闭流。如果使用了流缓冲池，则可以使用带save_in_stream_pool参数的close()函数。如果参数save_in_stream_pool为true则流并不会真正关闭，而是被缓冲池回收。
int good(void);	测试流是否打开。
int eof(void);	测试是否所有的数据都已经从流中读取。
void rewind(void);	重绕流。如果流不含有任何输入变量，该方法将强制流执行SQL语句。
operator int(void);	流到int的转换操作符，返回从!eof()获得的流状态。它允许运算符>>返回!EOF的流状态并且能够在while()循环中像下面的代码那样使用: while(s>>f1>>f2) { cout<<”f1=”<<f1<<”, f2=”<<f2<<endl; }
int is_null(void);	判断是否从流中获得NULL
void flush(void); void flush // OTL/OCI8,8i,9i,10g only ( const int row_offset=0, const bool force_flush=false );	刷新流的输出缓冲区。这实际上意味着批量执行SQL语句，其执行的批量和流输出缓冲区中已经填充的SQL数相等。当输出缓冲区被填满时，缓冲区将被自动刷新。如果流的auto_commit标志被置上，则在刷新完毕后当前事务被提交。 OTL/OCI8,8i,9i,10g拥有另外一个版本的flush()方法。能够通过参数row_offset指定缓冲区刷新的开始位置，通过参数force_flush则能够指定是否在出现错误抛出otl_exception的情况下仍然强制继续刷新，忽略之前的错误。
long get_rpc(void);	获得处理的行数(Rows Processed Count, RPC)。
void set_column_type( const int column_ndx, const int col_type, const int col_size=0 );	设置SELECT输出列的数据类型。参数column_ndx为列的索引，如1,2,3… 参数col_type为OTL定义的数据类型常量（参见11.2小节）参数col_size为新数据类型的大小，只被otl_var_char类型使用。
void set_commit(int auto_commit=0);	设置流的auto_commit标志。默认情况下，该标志被置上，即当输出缓冲区刷新时，当前的事务被自动提交。注意流的auto_commit标志和数据库库的自动提交模型没有任何关系。如果需要默认方式为不自动提交的流，则可以使用otl_stream的子类otl_nocommit_stream。
void set_flush(const bool auto_flush=true);	设置auto_flush标志。默认情况下auto_flush的值为true, 即如果缓冲区出现脏数据则在流的析构函数中刷新缓冲区。如果自动刷新标志被关闭，则需要使用close()方法或者flush()方法对流进行刷新。注意该函数仅仅能够设置流的析构函数中是否自动刷新，并不是通常意义上的缓冲区刷新。
int setBufSize(const int buf_size);	设置流缓冲区的大小。

4.2 otl_connect的主要方法

4-3 类otl_connect的主要方法说明

主要方法	说明
static int otl_initialize( const int threaded_mode=0 );	初始化OTL环境。需要在程序最开始连接数据库之前调用一次。参数threaded_mode指明程序是否运行在多线程环境，注意由于OTL并没有使用同步锁或者临界段，线程安全并不能够自动得到保证。
otl_connect( const char* connect_str, const int auto_commit=0 );	构造函数。参数connect_str为连接字符串,OTL/OCIx风格的连接字符串为： “USER/PASSWORD”(本地Oracle连接) “USER/PASSWORD@TNS_ALLAS”(通过SQL*Net进行的远程连接) 参数auto_commit指明是否每一个在连接中执行的SQL语句都会自动提交。如果需要自动提交则为1，默认情况下为0表示不需要自动提交。注意该auto_commit参数和otl_stream的自动提交没有任何关系。
void rlogon( const char* connect_str, const int auto_commit=0 );	连接数据库。参数同构造函数。
void logoff(void);	断开数据库连接。
static int otl_terminate(void);	终止Oracle 8i/9i的OCI环境。需要在程序最后的数据库连接进行关闭后调用一次。该方法仅仅是OCI Terminate()调用的包装。通常在多线程环境中，为了终止主线程的控制，该方法需要被调用使得进程能够从OCI客户端的共享内存中脱离以及做其他事情。
void cancel(void);// *OTL/OCI8/8i/9i* only	取消连接对象或者数据库会话中的正在执行或者活动的操作或数据库调用。
void commit(void); #if defined(OTL_ORA10G_R2) void commit_nowait(void); #endif	同步或异步的方式提交事务。
void rollback(void);	回滚事务。
void auto_commit_off(void); void auto_commit_on(void);	设置otl_connect对象的auto_commit标志。
void set_stream_pool_size( const int max_size =otl_max_default_pool_size );	如果使用了流缓冲池，则该方法重新分配被默认的流缓冲池和之前的set_stream_pool_size()调用分配的所有资源。
void set_character_set( const int char_set=SQLCS_IMPLICIT );	如果使用了UNICODE，则该方法设置默认或国家的字符集： SQLCS_IMPLICIT为数据库默认字符集。 SQLCS_NCHAR为数据库国家的字符集。
otl_connect& operator<<(const char* str);	发送字符串到otl_connect对象。如果该otl_connect对象还没有连接到数据库则字符串为"userid/passwd@db"格式的连接字符串，它使得otl_connect对象能够连接数据库。如果该otl_connect对象已经连接到数据库则字符串为静态SQL语句，该语句被马上执行。
otl_connect& operator<<=(const char* str);	发送字符串到otl_connect对象。otl_connect对象将保存该字符串并被下一个>>操作符使用。该字符串是一个拥有占位符并且能够发送到otl_stream对象的SQL语句。
otl_connect& operator>>(otl_stream& s);	发送之前使用操作符<<=保存的SQL语句到otl_stream对象。它使得该SQL语句被otl_stream打开。注意如果并没有被>>=操作符保存的字符串，则字符串"* INVALID COMMAND *"被发送到otl_stream，最终会将导致解析错误，抛掷otl_exception异常。
long direct_exec( const char *sqlstm, int ignore_error = otl_exception::enabled );	直接执行静态的SQL语句，返回处理的行数。
void syntax_check( const char *sqlstm );	解析静态的SQL语句，如果出现SQL错误将抛掷otl_exception异常。
void server_attach(const char* tnsname=0, const char* xa_server_external_name=0, const char* xa_server_internal_name=0, #if defined(OTL_ORA_OCI_ENV_CREATE) bool threaded_mode=false #endif);	附加到Oralcle。
void server_detach(void);	从Oracle分离。
void session_begin ( const char* username, const char* password, const int auto_commit=0, const int session_mode=OCI_DEFAULT );	开始Oracle8会话。
void session_end(void);	结束Oracle8会话。

6 SQL的变量绑定和常量SQL

6.1 SQL的变量绑定

OTL拥有一个小型的解析器，负责在流的内部动态的为SQL语句、PL/SQL 块或存储过程调用中声明的绑定变量分配空间。OTL将Oracle传统的使用命名符号作为占位符的变量绑定机制进行了扩展，增加了数据类型说明，例如：

INSERT INTO my_table2values(:employee_id<int>,:supervisor_name<char[32]>)

OTL占位符中的支持的数据类型如表6-1所示。

表6-1 OTL占位符中支持的数据类型

bigint	64-bit signed integer, for binding with BIGINT table columns (or stored procedure parameters) in MS SQL Server, DB2, MySQL, PostrgeSQL, etc. ODBC, and DB2 CLI support this kind bind variables natively, so does OTL. OCIs do not have native support for 64-bit integers, so OTL has to emulate it via string (<char[XXX]>) bind variables internally and does string-to-bigint and bigint-to-string conversion.
blob	for Oracle 8/9; BLOB
char[length] OTL 4.0.118 and higher:char(length)	null terminated string; length is database dependent; for Oracle in [3,32545]; for ODBC it depends on the database backend and the ODBC driver; for DB2-CLI >2. In Unicode OTL, this type of bind variable declaration means a null terminated Unicode character string (two bytes per character). Thelength field of this declarator needs to include an extra byte / Unicode character, in order to accomodate the null terminator itself (for example char[11] can be used in binding with a VARCHAR(9) column), unless #define OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE is enabled.
charz	Same as char[] for OTL_ORA7, OTL_ORA8, OTL_ORA8I, OTL_ORA9I, OTL_ORA10G. Should be used only when PL/SQL tables of type CHAR(XXX) are used. charz is actually a workaround for the following Oracle error: PLS-00418: array bind type must match PL/SQL table row type.Normally, the internal OCI datatype that is used to bind VARCHAR2/CHAR table columns / scalar PL/SQL procedure parameters works fine, except for PL/SQL tables of CHAR(XXX). PL/SQL engine does not like what OTL tries to bind with a PL/SQL table of CHAR(XXX).charz[] should be used instead of char[] in cases like that.
clob	for Oracle 8/9: CLOB, NCLOB
db2date	for DB2 DATEs; should be used in the binding of a placeholder with a DB2 DATE column in case of both
db2time	for DB2 TIMEs; should be used in the binding of a placeholder with a DB2 TIME column in case of both OTL/DB2-CLI and OTL/ODBC for DB2; requiresotl_datetime as a data container. See example91 for more detail.
double	8-byte floating point number
float	4-byte floating point number
int	32-bit signed int
ltz_timestamp	Oracle 9i TIMESTAMP WITH LOCAL TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime
nchar[length]	Same as char[] + otl_connect::set_character_set(SQLCS_NCHAR) for Oracle 8i/9i/10g only, under #define OTL_UNICODE., or #define OTL_ORA_UTF8. nchar[] is required only when both VARCHAR2/CHAR and NVARCHAR2/NCHAR need to be declared in the same SQL statement, or PL/SQL block.
nclob	Same as clob + otl_connect::set_character_set(SQLCS_NCHAR) for Oracle 8i/9i/10g only, under #define OTL_UNICODE, or #defineOTL_ORA_UTF8. nclob is required only when both CLOB and NCLOB need to be declared in the same SQL statement, or PL/SQL block.
raw[length]
raw_long
short	short int (16-bit signed integer)
timestamp	MS SQL Server/Sybase DATETIME, DB2 TIMESTAMP, Oracle DATE, Oracle 9i TIMESTAMP (when #defineOTL_ORA_TIMESTAMP is enabled) ; it requires TIMESTAMP_STRUCT (OTL/ODBC, OTL/DB2-CLI), orotl_datetime (ODBC, DB2-CLI, and OCIx). OTL/DB2-CLI and OTL/ODBC for DB2; requiresotl_datetime as a data container. See example91 for more detail
tz_timestamp	Oracle 9i TIMESTAMP WITH TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime
unsigned	unsigned int (32-bit unsigned integer)
varchar_long	for Oracle 7: LONG; for Oracle 8/9: LONG; for ODBC: SQL_LONGVARCHAR; for DB2: CLOB

为了区分PL/SQL 块或存储过程中的输入和输出变量，OTL引入了以下限定词：

l in – 输入变量

l out – 输出变量

l inout – 输入输出变量

其用法如下的Oracle示例代码片断所示。

1 BEGIN
2    :rc<int,out> := my_func(:salary<float,in>,  
3                            :ID<int,inout>, 
4                            :name<char[32],out>
5                           );
6  END;

6.2 常量SQL

如果SQL语句、PL/SQL 块或存储过程调用中不含有任何绑定变量，则可以称之为静态的。OTL包含了静态方法执行静态语句，例如：

 1 otl_cursor::direct_exec
 2 
 3    (db, // connect object
 4 
 5     "create table test_tab(f1 number, f2 varchar2(30))"
 6 
 7 );  // create table
 8 
 9  
10 
11   otl_cursor::direct_exec
12 
13    (db, // connect object
14 
15     "drop table test_tab", // SQL statement or PL/SQL block
16 
17     otl_exception::disabled // disable OTL exceptions,
18 
19                             // in other words, ignore any
20 
21                             // database error
22 
23    ); // drop table

otl_cursor是OTL4.0的一个internalclass。OTL虽然并不推荐使用低级别的类，但是otl_cursor的direct_exec()方法是一个特例。该方法的返回值可能为：

l -1, 如果otl_exception异常被禁止使用(第二个参数被设置成otl_exception::disabled)，并且底层的API返回了错误。

l >=0, 如果成功执行SQL命令，在执行INSERT、DELETE或UPDATE语句时实际返回的是已处理行数。

7迭代器

7.1 OTL流的读迭代器

OTL提供了模板类otl_stream_read_iterator来扩展OTL流接口以支持迭代，该模板类提供了类似JDBC的传统getter接口，可以使用名称访问返回列。

以下是使用读迭代器的示例代码。

  1 #include <iostream>
  2 using namespace std;
  3   
  4 #include <stdio.h>
  5 //#define OTL_ORA7 // Compile OTL 4.0/OCI7
  6 //#define OTL_ORA8 // Compile OTL 4.0/OCI8
  7 //#define OTL_ORA8I // Compile OTL 4.0/OCI8i
  8 #define OTL_ORA9I // Compile OTL 4.0/OCI9i
  9 //#define OTL_ORA10G // Compile OTL 4.0/OCI10g
 10 #define OTL_STREAM_READ_ITERATOR_ON
 11 #define OTL_STL
 12 #include <otlv4.h> // include the OTL 4.0 header file
 13 
 14 otl_connect db; // connect object
 15 
 16 void insert()
 17 // insert rows into table
 18 { 
 19  otl_stream o(50, // buffer size
 20               "insert into test_tab values(:f1<int>,:f2<char[31]>)", // SQL statement
 21               db // connect object
 22              );
 23  char tmp[32];
 24 
 25  for(int i=1;i<=100;++i){
 26   sprintf(tmp,"Name%d",i);
 27   o<<i<<tmp;
 28  }
 29 }
 30 
 31 void select()
 32 { 
 33  otl_stream i(50, // buffer size
 34               "select * from test_tab "
 35          "where f1>=:f11<int> and f1<=:f12<int>*2",
 36                  // SELECT statement
 37               db // connect object
 38              ); 
 39    // create select stream
 40  
 41  int f1;
 42  char f2[31];
 43  otl_stream_read_iterator<otl_stream,otl_exception,otl_lob_stream> rs;
 44 
 45  rs.attach(i); // attach the iterator "rs" to the stream "i".
 46  i<<8<<8; // assigning :f11 = 8, :f12 = 8
 47    // SELECT automatically executes when all input variables are
 48    // assigned. First portion of output rows is fetched to the buffer
 49 
 50  while(rs.next_row()){// while not end-of-data
 51     rs.get("F2",f2);
 52     rs.get("F1",f1);
 53     cout<<"f1="<<f1<<", f2="<<f2<<endl;
 54  }
 55 
 56  rs.detach(); // detach the itertor from the stream
 57 
 58  i<<4<<4; // assigning :f11 = 4, :f12 = 4
 59    // SELECT automatically executes when all input variables are
 60    // assigned. First portion of output rows is fetched to the buffer
 61 
 62  while(!i.eof()){ // while not end-of-data
 63     i>>f1>>f2;
 64     cout<<"f1="<<f1<<", f2="<<f2<<endl;
 65  }
 66 }
 67 
 68 int main()
 69 {
 70  otl_connect::otl_initialize(); // initialize OCI environment
 71  try{
 72 
 73   db.rlogon("scott/tiger"); // connect to Oracle
 74 
 75   otl_cursor::direct_exec
 76    (
 77     db,
 78     "drop table test_tab",
 79     otl_exception::disabled // disable OTL exceptions
 80    ); // drop table
 81 
 82   otl_cursor::direct_exec
 83    (
 84     db,
 85     "create table test_tab(f1 number, f2 varchar2(30))"
 86     );  // create table
 87 
 88   insert(); // insert records into table
 89   select(); // select records from table
 90 
 91  }
 92 
 93  catch(otl_exception& p){ // intercept OTL exceptions
 94   cerr<<p.msg<<endl; // print out error message
 95   cerr<<p.stm_text<<endl; // print out SQL that caused the error
 96   cerr<<p.var_info<<endl; // print out the variable that caused the error
 97  }
 98 
 99  db.logoff(); // disconnect from Oracle
100 
101  return 0;
102 
103 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

7.2 STL兼容的迭代器

OTL将泛型编程和Oracle紧密结合以构筑小容量的、可靠的、高性能并且容易维护的C++数据库应用，为此分别提供了两个STL兼容的迭代器：otl_output_iterator<T>和otl_input_iterator<T,Distance>。

otl_output_iterator<T>是一种输出迭代器(Output Iterator)，它将类型为T的对象输出到otl_stream。其构造函数为otl_output_iterator(otl_stream&s)。

otl_input_iterator<T,Distance>是一种输入迭代器(InputIterator)，它从otl_stream中读出将类型为T的对象，另外一个模板参数Distance为otl_input_iterator的指针偏移类型。当流的末尾到达时，otl_input_iterator会得到一个特殊的值即past-the-end迭代器。

otl_input_iterator的构造函数分别为otl_output_iterator(otl_stream&s)和otl_output_iterator()，其中无参数的默认构造函数otl_output_iterator()将创建一个past-the-end迭代器用以指示otl_input_iterator到达流尾。

以下是使用STL兼容迭代器的示例代码。

  1 #include <iostream>
  2 #include <vector>
  3 #include <iterator>
  4 #include <string>
  5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  6 #define OTL_STL // Turn on STL features
  7 #define OTL_ANSI_CPP // Turn on ANSI C++ typecasts
  8 #include <otlv4.h> // include the OTL 4.0 header file
  9 
 10 using namespace std;
 11 
 12 otl_connect db; // connect object
 13 
 14                 // row container class
 15 class row {
 16 public:
 17     int f1;
 18     string f2;
 19 
 20     // default constructor
 21     row() { f1 = 0; }
 22 
 23     // destructor 
 24     ~row() {}
 25 
 26     // copy constructor
 27     row(const row& row)
 28     {
 29         f1 = row.f1;
 30         f2 = row.f2;
 31     }
 32     // assignment operator
 33     row& operator=(const row& row)
 34     {
 35         f1 = row.f1;
 36         f2 = row.f2;
 37         return *this;
 38     }
 39 };
 40 
 41 // redefined operator>> for reading row& from otl_stream
 42 otl_stream& operator >> (otl_stream& s, row& row)
 43 {
 44     s >> row.f1 >> row.f2;
 45     return s;
 46 }
 47 
 48 // redefined operator<< for writing row& into otl_stream
 49 otl_stream& operator<<(otl_stream& s, const row& row)
 50 {
 51     s << row.f1 << row.f2;
 52     return s;
 53 }
 54 
 55 // redefined operator<< writing row& into ostream
 56 ostream& operator<<(ostream& s, const row& row)
 57 {
 58     s << "f1=" << row.f1 << ", f2=" << row.f2;
 59     return s;
 60 }
 61 
 62 void insert()
 63 // insert rows into table
 64 {
 65     otl_stream o(50, // buffer size
 66         "insert into test_tab values(:f1<int>,:f2<char[31]>)",
 67         // SQL statement
 68         db // connect object
 69     );
 70 
 71     row r; // single row buffer
 72     vector<row> vo; // vector of rows
 73 
 74                     // populate the vector
 75     for (int i = 1; i <= 100; ++i) {
 76         r.f1 = i;
 77         r.f2 = "NameXXX";
 78         vo.push_back(r);
 79     }
 80 
 81     cout << "vo.size=" << vo.size() << endl;
 82 
 83     // insert vector into table
 84     copy(vo.begin(), vo.end(), otl_output_iterator<row>(o));
 85 }
 86 
 87 void select()
 88 {
 89     otl_stream i(50, // buffer size
 90         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 91         // SELECT statement
 92         db // connect object
 93     );
 94     // create select stream
 95 
 96     vector<row> v; // vector of rows
 97 
 98                    // assigning :f = 8
 99     i << 8;
100 
101     // SELECT automatically executes when all input variables are
102     // assigned. First portion of out rows is fetched to the buffer
103 
104     // copy all rows to be fetched into the vector
105     copy(otl_input_iterator<row, ptrdiff_t>(i),
106         otl_input_iterator<row, ptrdiff_t>(),
107         back_inserter(v));
108 
109     cout << "Size=" << v.size() << endl;
110 
111     // send the vector to cout
112     copy(v.begin(), v.end(), ostream_iterator<row>(cout, "
"));
113 
114     // clean up the vector
115     v.erase(v.begin(), v.end());
116 
117     i << 4; // assigning :f = 4
118             // SELECT automatically executes when all input variables are
119             // assigned. First portion of out rows is fetched to the buffer
120 
121             // copy all rows to be fetched to the vector
122     copy(otl_input_iterator<row, ptrdiff_t>(i),
123         otl_input_iterator<row, ptrdiff_t>(),
124         back_inserter(v));
125 
126     cout << "Size=" << v.size() << endl;
127 
128     // send the vector to cout
129     copy(v.begin(), v.end(), ostream_iterator<row>(cout, "
"));
130 
131 }
132 
133 int main()
134 {
135     otl_connect::otl_initialize(); // initialize OCI environment
136     try {
137 
138         db.rlogon("scott/tiger"); // connect to Oracle
139 
140         otl_cursor::direct_exec
141         (
142             db,
143             "drop table test_tab",
144             otl_exception::disabled // disable OTL exceptions
145         ); // drop table
146 
147         otl_cursor::direct_exec
148         (
149             db,
150             "create table test_tab(f1 number, f2 varchar2(30))"
151         );  // create table
152 
153         insert(); // insert records into table
154         select(); // select records from table
155 
156     }
157 
158     catch (otl_exception& p) { // intercept OTL exceptions
159         cerr << p.msg << endl; // print out error message
160         cerr << p.stm_text << endl; // print out SQL that caused the error
161         cerr << p.var_info << endl; // print out the variable that caused the error
162     }
163 
164     db.logoff(); // disconnect from Oracle
165 
166     return 0;
167 
168 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

8 资源池

8.1 连接缓冲池

对于Oracle数据库API，OTL的otl_connect类提供了server_attach()、server_detached()、session_begin()、session_end()四个方法(见4.2小节)，它们可以联合使用以创建类似连接缓冲池机制。使用session_begin()比直接使用rlogon()快大约50到100倍。

以下是联合使用otl_connect的以上四个方法创建连接缓冲池机制的示例代码。

  1 #include <iostream>
  2 
  3 using namespace std;
  4 
  5 
  6 
  7 #include <stdio.h>
  8 
  9 #define OTL_ORA8 // Compile OTL 4.0/OCI8
 10 
 11 #include <otlv4.h> // include the OTL 4.0 header file
 12 
 13 
 14 
 15 otl_connect db; // connect object
 16 
 17 
 18 
 19 void insert()
 20 
 21 // insert rows into table
 22 
 23 {
 24 
 25     otl_stream o(50, // buffer size
 26 
 27         "insert into test_tab values(:f1<float>,:f2<char[31]>)",
 28 
 29         // SQL statement
 30 
 31         db // connect object
 32 
 33     );
 34 
 35     char tmp[32];
 36 
 37 
 38 
 39     for (int i = 1; i <= 100; ++i) {
 40 
 41         sprintf(tmp, "Name%d", i);
 42 
 43         o << (float)i << tmp;
 44 
 45     }
 46 
 47 }
 48 
 49 
 50 
 51 void select()
 52 
 53 {
 54 
 55     otl_stream i(50, // buffer size
 56 
 57         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 58 
 59         // SELECT statement
 60 
 61         db // connect object
 62 
 63     );
 64 
 65     // create select stream
 66 
 67 
 68 
 69     float f1;
 70 
 71     char f2[31];
 72 
 73 
 74 
 75     i << 4; // assigning :f = 4
 76 
 77             // SELECT automatically executes when all input variables are
 78 
 79             // assigned. First portion of output rows is fetched to the buffer
 80 
 81 
 82 
 83     while (!i.eof()) { // while not end-of-data
 84 
 85         i >> f1 >> f2;
 86 
 87         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 88 
 89     }
 90 
 91 
 92 
 93 }
 94 
 95 
 96 
 97 int main()
 98 
 99 {
100 
101     otl_connect::otl_initialize(); // initialize OCI environment
102 
103     try {
104 
105 
106 
107         db.rlogon("scott/tiger"); // connect to Oracle
108 
109 
110 
111         otl_cursor::direct_exec
112 
113         (
114 
115             db,
116 
117             "drop table test_tab",
118 
119             otl_exception::disabled // disable OTL exceptions
120 
121         ); // drop table
122 
123 
124 
125         otl_cursor::direct_exec
126 
127         (
128 
129             db,
130 
131             "create table test_tab(f1 number, f2 varchar2(30))"
132 
133         );  // create table
134 
135 
136 
137         insert(); // insert records into table
138 
139 
140 
141         db.logoff(); // disconnect from Oracle
142 
143 
144 
145         db.server_attach(); // attach to the local Oracle server
146 
147                             // In order to connect to a remote server,
148 
149                             // a TNS alias needs to be specified
150 
151 
152 
153         for (int i = 1; i <= 100; ++i) {
154 
155             cout << "Session begin ==> " << i << endl;
156 
157             db.session_begin("scott", "tiger");
158 
159             // begin session; this function is much faster
160 
161             // than rlogon() and should be used (see the Oracle
162 
163             // manuals for more detail) in high-speed processing
164 
165             // systems, possibly with thousands of users.
166 
167             // this technique can be used instead of traditional
168 
169             // connection pooling.
170 
171 
172 
173             select(); // select records from table
174 
175 
176 
177             cout << "Session end ==> " << i << endl;
178 
179             db.session_end(); // end session
180 
181         }
182 
183 
184 
185         db.server_detach();// detach from the Oracle server
186 
187     }
188 
189     catch (otl_exception& p) { // intercept OTL exceptions
190 
191         cerr << p.msg << endl; // print out error message
192 
193         cerr << p.stm_text << endl; // print out SQL that caused the error
194 
195         cerr << p.var_info << endl; // print out the variable that caused the error
196 
197     }
198 
199 
200 
201     db.logoff(); // make sure that the program gets disconnected from Oracle
202 
203 
204 
205     return 0;
206 
207 
208 
209 }

输出结果：

Session begin ==> XXX

f1=4, f2=Name4

f1=5, f2=Name5

f1=6, f2=Name6

f1=7, f2=Name7

f1=8, f2=Name8

Session end ==> XXX

8.2 OTL流缓冲池

流缓冲池是OTL的一个新机制，当otl_stream实例关闭时，otl_stream变量实例将被保存到流缓冲池中，使得程序能够继续重用。otl_stream的每次实例化将触发数据库后台对OTL流中SQL语句的重新解析，这是相对耗时的操作，而流缓冲池机制将减少这方面的耗时并简化编码技术。

缓冲池中的流可以是局部变量也可以是分配在堆上的动态变量。流之间的相似型和流缓冲区大小以及SQL语句文本有关，拥有相同缓冲区大小和SQL语句文本的流将保存在缓冲池中相同的桶中如图8-1所示。由于流缓冲池的底层使用STL的map和vector实现，因此使用时应该用”#defineOTL_STL”向编译器指明。

图8-1 OTL的流缓冲池

以下是采用流缓冲池机制的示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 // Uncomment the line below when OCI7 is used with OTL
  7 // #define OTL_ORA7 // Compile OTL 4.0/OCI7 
  8 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  9 #define OTL_STL // turn on OTL in the STL compliance mode
 10 #define OTL_STREAM_POOLING_ON 
 11 // turn on OTL stream pooling.
 12 // #define OTL_STREAM_POOLING_ON line 
 13 // can be commented out the number of iterations in
 14 // the select() loop can be increased, and the difference 
 15 // in performace with and without OTL_STREAM_POOLING_ON can
 16 // be benchmarked. The difference should grow with the overall
 17 // number of streams to be used in one program.
 18 
 19 #include <otlv4.h> // include the OTL 4.0 header file
 20 
 21 otl_connect db; // connect object
 22 
 23 void insert()
 24 // insert rows into table
 25 {
 26     otl_stream o(50, // buffer size
 27         "insert into test_tab values(:f1<int>,:f2<char[31]>)",
 28         // SQL statement
 29         db // connect object
 30     );
 31     char tmp[32];
 32 
 33     for (int i = 1; i <= 100; ++i) {
 34         sprintf(tmp, "Name%d", i);
 35         o << i << tmp;
 36     }
 37 #ifdef OTL_STREAM_POOLING_ON
 38     o.close(false); // do not save the stream in the stream pool.
 39                     // in other words, destroy it on the spot, since
 40                     // the stream is not going to be reused later.
 41 #else
 42     o.close();
 43 #endif
 44 }
 45 
 46 void select()
 47 { // when this function is called in a loop,
 48   // on the second iteration of the loop the streams i1, i2 will
 49   // will get the instances of the OTL stream from the stream
 50   // pool, "fast reopen", so to speak.
 51 
 52     otl_stream i1(50, // buffer size
 53         "select * from test_tab where f1>=:f11<int> and f1<=:f12<int>*2",
 54         // SELECT statement
 55         db // connect object
 56     );
 57     // create select stream
 58 
 59     otl_stream i2(33, // buffer size
 60         "select f1,f2 from test_tab where f1>=:f11<int> and f1<=:f12<int>*2",
 61         // SELECT statement
 62         db // connect object
 63     );
 64     // create select stream
 65 
 66     // i1 and i2 are NOT similar, because their buffer sizes as well
 67     // as SQL statements are not equal. It will generate two entry points in the
 68     // OTL stream pool.
 69 
 70     int f1;
 71     char f2[31];
 72 
 73     i1 << 2 << 2; // assigning :f11 = 2, :f12 = 2
 74                   // SELECT automatically executes when all input variables are
 75                   // assigned. First portion of output rows is fetched to the buffer
 76 
 77     while (!i1.eof()) { // while not end-of-data
 78         i1 >> f1 >> f2;
 79         cout << "I1==> f1=" << f1 << ", f2=" << f2 << endl;
 80     }
 81 
 82     i2 << 3 << 3; // assigning :f11 = 2, :f12 = 2
 83                   // SELECT automatically executes when all input variables are
 84                   // assigned. First portion of output rows is fetched to the buffer
 85 
 86     while (!i2.eof()) { // while not end-of-data
 87         i2 >> f1 >> f2;
 88         cout << "I2==> f1=" << f1 << ", f2=" << f2 << endl;
 89     }
 90 
 91 } // destructors of i1, i2 will call the close()
 92   // function for both of the streams and the OTL stream
 93   // instances will be placed in the stream pool.
 94 
 95 int main()
 96 {
 97     otl_connect::otl_initialize(); // initialize the environment
 98     try {
 99 
100         db.rlogon("scott/tiger"); // connect to the database
101 #ifdef OTL_STREAM_POOLING_ON
102         db.set_stream_pool_size(2);
103         // set the maximum stream pool size and actually initializes 
104         // the stream pool.
105         // if this function is not called, the stream pool
106         // gets initialized anyway, with the default size of 32 entries.
107 #endif
108 
109         otl_cursor::direct_exec
110         (
111             db,
112             "drop table test_tab",
113             otl_exception::disabled // disable OTL exceptions
114         ); // drop table
115 
116         otl_cursor::direct_exec
117         (
118             db,
119             "create table test_tab(f1 int, f2 varchar(30))"
120         );  // create table
121 
122         insert(); // insert records into table
123         for (int i = 1; i <= 10; ++i) {
124             cout << "===================> Iteration: " << i << endl;
125             select(); // select records from table
126         }
127     }
128 
129     catch (otl_exception& p) { // intercept OTL exceptions
130         cerr << p.msg << endl; // print out error message
131         cerr << p.stm_text << endl; // print out SQL that caused the error
132         cerr << p.var_info << endl; // print out the variable that caused the error
133     }
134 
135     db.logoff(); // disconnect from the database
136 
137     return 0;
138 
139 }

输出结果：

===================> Iteration: 1
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 2
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 3
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 4
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 5
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 6
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 7
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 8
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 9
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6
===================> Iteration: 10
I1==> f1=2, f2=Name2
I1==> f1=3, f2=Name3
I1==> f1=4, f2=Name4
I2==> f1=3, f2=Name3
I2==> f1=4, f2=Name4
I2==> f1=5, f2=Name5
I2==> f1=6, f2=Name6

9 操作大型对象

OTL提供了otl_longstring、otl_long_unicode_string以及otl_lob_stream三个类操作大型对象。其中otl_long string、otl_long_unicode_string用来存储大型对象，otl_lob_stream用来读写大型对象。

9.1大型对象的存储

9.1.1 otl_long_string

OTL提供了类otl_long_string来存放ANSI字符集编码的大型对象，其定义如下。

 1 class otl_long_string{
 2 public:
 3      unsigned char* v;
 4      otl_long_string(
 5 const int buffer_size=32760,
 6               const int input_length=0
 7       );
 8      otl_long_string(const void* external_buffer,
 9              const int buffer_size,
10              const int input_length=0
11     );
12      void set_len(const int len=0);
13     void set_last_piece(const bool last_piece=false); 
14      int len(void);
15      unsigned char& operator[](int ndx);
16  
17 otl_long_string& operator=(const otl_long_string&);
18 otl_long_string(const otl_long_string&);
19 }; // end of otl_long_string

otl_long_string的成员变量v存放大型对象的缓存起始位置。构造函数中的参数说明如下：

l buffer_size参数指明存放大型对象的缓存大小，默认为32760，可以通过otl_connect的set_max_long_size()方法来改变默认的大小值。

l input_length参数则指明实际输入的大小，如果该参数被设定则set_len()成员方法就没有必要使用了。

l 另外，如果使用参数external_buffer则otl_long_string不再为大型对象实际分配存储空间，而是直接使用用户传入的以external_buffer为起始地址的缓存。

9.1.2 otl_long_unicode_string

OTL提供了类otl_long_string来存放UNICODE字符集编码的大型对象，其定义如下。

 1 class otl_long_unicode_string: public otl_long_string{
 2 public:
 3 otl_long_unicode_string(
 4             const int buffer_size=32760,
 5             const int input_leng
 6 );
 7  
 8 otl_long_unicode_string(
 9             const void* external_buffer,
10             const int buffer_size,
11             const int input_length=0
12      );
13  
14 void set_len(const int len=0);
15           int len(void);
16           unsigned short& operator[](int ndx);
17 }; // end of otl_long_unicode_string

otl_long_unicode_string的成员变量、方法以及构造函数和父类相同，但是注意缓冲区大小为UNICODE字符数量而不是字节数。

9.2 大型对象的读写

大型对象的读写通过类otl_lob_stream实现，其定义如下。

 1 class otl_lob_stream {
 2 public:
 3     void set_len(const int alen);
 4     otl_lob_stream& operator<<(const std::string& s);
 5     otl_lob_stream& operator<<(const ACE_TString& s);
 6 
 7     otl_lob_stream& operator >> (std::string& s);
 8     otl_lob_stream& operator >> (ACE_TString& s);
 9 
10     void setStringBuffer(const int chunk_size);
11     otl_lob_stream& operator<<(const otl_long_string& s);
12     otl_lob_stream& operator<<(const otl_long_unicode_string& s);
13     otl_lob_stream& operator >> (otl_long_string& s);
14 
15     otl_lob_stream& operator >> (otl_long_unicode_string& s);
16 
17     int len(void);
18     int eof(void);
19     void close(void);
20     bool is_initialized(void);
21 }; // end of otl_lob_stream

otl_lob_stream重载了<<和>>运算符来操作存放在std::string、otl_long_string、otl_long_unicode_string以及ACE_TString中的大型对象。

以下是操作大型对象的示例代码。包括INSERT、UPDATE、SELECT操作。注意在使用INSERT和UPDATE时流缓冲区的大小必须为1，另外必须将otl_stream的auto_commit标志设置为false。初始化otl_lob_stream是通过将otl_lob_stream对象作为otl_stream的<<操作符参数来实现的。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  6 #include <otlv4.h> // include the OTL 4.0 header file
  7 
  8 otl_connect db; // connect object
  9 
 10 void insert()
 11 // insert rows into table
 12 {
 13     otl_long_string f2(60000); // define long string variable
 14     otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
 15         "insert into test_tab values(:f1<int>,empty_clob()) "
 16         "returning f2 into :f2<clob> ", // SQL statement
 17         db // connect object
 18     );
 19     o.set_commit(0); // setting stream "auto-commit" to "off". It is required
 20                      // when LOB stream mode is used.
 21 
 22     otl_lob_stream lob; // LOB stream for reading/writing unlimited number
 23                         // of bytes regardless of the buffer size.
 24 
 25     for (int i = 1; i <= 20; ++i) {
 26         for (int j = 0; j<50000; ++j)
 27             f2[j] = '*';
 28         f2[50000] = '?';
 29         f2.set_len(50001);
 30 
 31         o << i;
 32 
 33         o << lob; // Initialize otl_lob_stream by writing it
 34                   // into otl_stream. Weird, isn't it?
 35 
 36         lob.set_len(50001 + 23123); // setting the total  size of
 37                                     // the CLOB to be written.
 38                                     // It is required for compatibility
 39                                     // with earlier releases of OCI8: OCI8.0.3, OCI8.0.4.
 40 
 41         lob << f2; // writing first chunk of the CLOB into lob
 42 
 43         f2[23122] = '?';
 44         f2.set_len(23123); // setting the size of the second chunk
 45 
 46         lob << f2; // writing the second chunk of the CLOB into lob
 47         lob.close(); // closing the otl_lob_stream
 48     }
 49 
 50     db.commit(); // committing transaction.
 51 }
 52 void update()
 53 // insert rows in table
 54 {
 55     otl_long_string f2(6200); // define long string variable
 56 
 57     otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
 58         "update test_tab "
 59         "   set f2=empty_clob() "
 60         "where f1=:f1<int> "
 61         "returning f2 into :f2<clob> ",
 62         // SQL statement
 63         db // connect object
 64     );
 65 
 66     otl_lob_stream lob;
 67 
 68     o.set_commit(0); // setting stream "auto-commit" to "off". 
 69 
 70 
 71     for (int j = 0; j<6000; ++j) {
 72         f2[j] = '#';
 73     }
 74 
 75     f2[6000] = '?';
 76     f2.set_len(6001);
 77 
 78     o << 5;
 79     o << lob; // Initialize otl_lob_stream by writing it
 80               // into otl_stream.
 81 
 82     lob.set_len(6001 * 4); // setting the total size of of the CLOB to be written
 83     for (int i = 1; i <= 4; ++i)
 84         lob << f2; // writing chunks of the CLOB into the otl_lob_stream
 85 
 86     lob.close(); // closing the otl_lob_stream
 87 
 88     db.commit(); // committing transaction
 89 
 90 }
 91 
 92 void select()
 93 {
 94     otl_long_string f2(20000); // define long string variable
 95 
 96     otl_stream i(10, // buffer size. To read CLOBs, it can be set to a size greater than 1
 97         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 98         // SELECT statement
 99         db // connect object
100     );
101     // create select stream
102 
103     float f1;
104     otl_lob_stream lob; // Stream for reading CLOB
105 
106     i << 4; // assigning :f = 4
107             // SELECT automatically executes when all input variables are
108             // assigned. First portion of output rows is fetched to the buffer
109 
110     while (!i.eof()) { // while not end-of-data
111         i >> f1;
112         cout << "f1=" << f1 << endl;
113         i >> lob; // initializing CLOB stream by reading the CLOB reference 
114                   // into the otl_lob_stream from the otl_stream.
115         int n = 0;
116         while (!lob.eof()) { // read while not "end-of-file" -- end of CLOB
117             ++n;
118             lob >> f2; // reading a chunk of CLOB
119             cout << "   chunk #" << n;
120             cout << ", f2=" << f2[0] << f2[f2.len() - 1] << ", len=" << f2.len() << endl;
121         }
122         lob.close(); // closing the otl_lob_stream. This step may be skipped.
123     }
124 }
125 
126 int main()
127 {
128     otl_connect::otl_initialize(); // initialize OCI environment
129     try {
130 
131         db.rlogon("scott/tiger"); // connect to Oracle
132 
133         otl_cursor::direct_exec
134         (
135             db,
136             "drop table test_tab",
137             otl_exception::disabled // disable OTL exceptions
138         ); // drop table
139 
140         otl_cursor::direct_exec
141         (
142             db,
143             "create table test_tab(f1 number, f2 clob)"
144         );  // create table
145 
146         insert(); // insert records into table
147         update(); // update records in table
148         select(); // select records from table
149 
150     }
151 
152     catch (otl_exception& p) { // intercept OTL exceptions
153         cerr << p.msg << endl; // print out error message
154         cerr << p.stm_text << endl; // print out SQL that caused the error
155         cerr << p.var_info << endl; // print out the variable that caused the error
156     }
157 
158     db.logoff(); // disconnect from Oracle
159 
160     return 0;
161 
162 }

输出结果：

f1=4

   chunk #1, f2=**, len=20000

   chunk #2, f2=**, len=20000

   chunk #3, f2=**, len=20000

   chunk #4, f2=*?, len=13124

f1=5

   chunk #1, f2=##, len=20000

   chunk #2, f2=#?, len=4004

f1=6

   chunk #1, f2=**, len=20000

   chunk #2, f2=**, len=20000

   chunk #3, f2=**, len=20000

   chunk #4, f2=*?, len=13124

f1=7

   chunk #1, f2=**, len=20000

   chunk #2, f2=**, len=20000

   chunk #3, f2=**, len=20000

   chunk #4, f2=*?, len=13124

f1=8

   chunk #1, f2=**, len=20000

   chunk #2, f2=**, len=20000

   chunk #3, f2=**, len=20000

   chunk #4, f2=*?, len=13124

10国际化

OTL的国际化支持主要是通过支持编码类型为UNICODE或UTF8的字符串操作实现。

10.1 使用UNICODE字符串

可以通过宏”#define OTL_UNICODE”指示OTL内部使用UNICODE字符串。以下是使用UNICODE字符串的示例代码。

示例代码中使用unsigned short类型数组存放UNICODE字符串，由于otl_stream的操作符<<并不支持unsigned short*类型，因此在变量绑定使用<<操作符时，将其强制转换成unsigned char*类型进行操作。与此类似，由于otl_stream的操作符>>并不支持unsigned short*类型，读取结果使用>>操作符时也是将其强制转换成unsigned char*类型进行操作。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 #define OTL_ORA9I // Compile OTL 4.0/OCI9i
  7 #define OTL_UNICODE // Enable Unicode OTL for OCI9i
  8 #include <otlv4.h> // include the OTL 4.0 header file
  9 
 10 otl_connect db; // connect object
 11 
 12 void insert()
 13 // insert rows into table
 14 {
 15     otl_stream o(50, // buffer size
 16         "insert into test_tab values(:f1<float>,:f2<char[31]>)",
 17         // SQL statement
 18         db // connect object
 19     );
 20     char tmp[32];
 21     unsigned short tmp2[32]; // Null terminated Unicode character array.
 22 
 23     for (int i = 1; i <= 100; ++i) {
 24         sprintf(tmp, "Name%d", i);
 25         unsigned short* c2 = tmp2;
 26         char* c1 = tmp;
 27         // Unicode's first 128 characters are ASCII (0..127), so
 28         // all is needed for converting ASCII into Unicode is as follows:
 29         while (*c1) {
 30             *c2 = (unsigned char)*c1;
 31             ++c1; ++c2;
 32         }
 33         *c2 = 0; // target Unicode string is null terminated,
 34                  // only the null terminator is a two-byte character, 
 35                  // not one-byte
 36         o << (float)i;
 37         o << (unsigned char*)tmp2;
 38         // overloaded operator<<(const unsigned char*) in the case of Unicode
 39         // OTL accepts a pointer to a Unicode character array.
 40         // operator<<(const unsigned short*) wasn't overloaded
 41         // in order to avoid ambiguity in C++ type casting.
 42     }
 43 
 44 }
 45 
 46 void select()
 47 {
 48     otl_stream i(50, // buffer size
 49         "select * from test_tab where f1>=:f<int> and f1<=:f*2", // SELECT statement
 50         db // connect object
 51     );
 52     // create select stream
 53 
 54     float f1;
 55     unsigned short f2[32];
 56 
 57     i << 8; // assigning :f = 8
 58             // SELECT automatically executes when all input variables are
 59             // assigned. First portion of output rows is fetched to the buffer
 60 
 61     while (!i.eof()) { // while not end-of-data
 62         i >> f1;
 63         i >> (unsigned char*)f2;
 64         // overloaded operator>>(unsigned char*) in the case of Unicode
 65         // OTL accepts a pointer to a Unicode chracter array.
 66         // operator>>(unsigned short*) wasn't overloaded 
 67         // in order to avoid ambiguity in C++ type casting.
 68         cout << "f1=" << f1 << ", f2=";
 69         // Unicode's first 128 characters are ASCII, so in order
 70         // to convert Unicode back to ASCII all is needed is
 71         // as follows:
 72         for (int j = 0; f2[j] != 0; ++j) {
 73             cout << (char)f2[j];
 74         }
 75         cout << endl;
 76     }
 77 
 78     i << 4; // assigning :f = 4
 79             // SELECT automatically executes when all input variables are
 80             // assigned. First portion of output rows is fetched to the buffer
 81 
 82     while (!i.eof()) { // while not end-of-data
 83         i >> f1 >> (unsigned char*)f2;
 84         cout << "f1=" << f1 << ", f2=";
 85         for (int j = 0; f2[j] != 0; ++j) {
 86             cout << (char)f2[j];
 87         }
 88         cout << endl;
 89     }
 90 
 91 }
 92 
 93 int main()
 94 {
 95     otl_connect::otl_initialize(); // initialize OCI environment
 96     try {
 97 
 98         db.rlogon("scott/tiger"); // connect to Oracle
 99 
100         otl_cursor::direct_exec
101         (
102             db,
103             "drop table test_tab",
104             otl_exception::disabled // disable OTL exceptions
105         ); // drop table
106 
107         otl_cursor::direct_exec
108         (
109             db,
110             "create table test_tab(f1 number, f2 varchar2(30))"
111         );  // create table
112 
113         insert(); // insert records into table
114         select(); // select records from table
115 
116     }
117 
118     catch (otl_exception& p) { // intercept OTL exceptions
119         cerr << p.msg << endl; // print out error message
120         cerr << p.stm_text << endl; // print out SQL that caused the error
121         cerr << p.var_info << endl; // print out the variable that caused the error
122     }
123 
124     db.logoff(); // disconnect from Oracle
125 
126     return 0;
127 
128 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

10.2 使用UTF8字符串

可以通过宏”#define OTL_ORA_UTF8”指示OTL内部使用UTF8字符串。以下是使用UTF字符串的示例代码。

示例代码中使用unsigned char类型数组存放UTF8字符串, 在使用otl_stream的操作符<<进行变量绑定时，通过转型并使用copy()函数将其转成char*类型进行操作。另外需要注意到环境变量的设定NLS_LANG=.AL32UTF8。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 #define OTL_ORA9I // Compile OTL 4.0/OCI9i
  7 #define OTL_ORA_UTF8 // Enable UTF8 OTL for OCI9i
  8 #include <otlv4.h> // include the OTL 4.0 header file
  9 
 10 otl_connect db; // connect object
 11 
 12                 // Sample UTF8 based string
 13 unsigned char utf8_sample[] =
 14 { 0x61,0x62,0x63,0xd0,0x9e,0xd0,0x9b,0xd0,
 15 0xac,0xd0,0x93,0xd0,0x90,0x0 };
 16 
 17 void insert()
 18 // insert rows into table
 19 {
 20     otl_stream o(50, // buffer size
 21         "insert into test_tab values(:f1<int>,:f2<char[31]>)",
 22         // SQL statement
 23         db // connect object
 24     );
 25 
 26     unsigned char tmp[31];
 27 
 28     for (int i = 1; i <= 100; ++i) {
 29         strcpy(reinterpret_cast<char*>(tmp), reinterpret_cast<const char*>(utf8_sample));
 30         o << i;
 31         o << tmp;
 32     }
 33 
 34 }
 35 
 36 void select()
 37 {
 38     otl_stream i(50, // buffer size
 39         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 40         // SELECT statement
 41         db // connect object
 42     );
 43     // create select stream
 44 
 45     int f1;
 46     unsigned char f2[31];
 47 
 48     i << 8; // assigning :f = 8
 49             // SELECT automatically executes when all input variables are
 50             // assigned. First portion of output rows is fetched to the buffer
 51 
 52     while (!i.eof()) { // while not end-of-data
 53         i >> f1;
 54         i >> f2;
 55         cout << "f1=" << f1 << ", f2=";
 56         for (int j = 0; f2[j] != 0; ++j)
 57             printf("%2x ", f2[j]);
 58         cout << endl;
 59     }
 60 
 61 }
 62 
 63 int main()
 64 {
 65     putenv(const_cast<char*>("NLS_LANG=.AL32UTF8"));
 66 
 67     // set your Oracle Client NLS_LANG 
 68     // if its default was set to something else
 69     otl_connect::otl_initialize(); // initialize OCI environment
 70     try {
 71 
 72         db.rlogon("scott/tiger"); // connect to Oracle
 73 
 74         otl_cursor::direct_exec
 75         (
 76             db,
 77             "drop table test_tab",
 78             otl_exception::disabled // disable OTL exceptions
 79         ); // drop table
 80 
 81         otl_cursor::direct_exec
 82         (
 83             db,
 84             "create table test_tab(f1 number, f2 varchar2(30))"
 85         );  // create table
 86 
 87         insert(); // insert records into table
 88         select(); // select records from table
 89 
 90     }
 91 
 92     catch (otl_exception& p) { // intercept OTL exceptions
 93         cerr << p.msg << endl; // print out error message
 94         cerr << p.stm_text << endl; // print out SQL that caused the error
 95         cerr << p.var_info << endl; // print out the variable that caused the error
 96     }
 97 
 98     db.logoff(); // disconnect from Oracle
 99 
100     return 0;
101 }

输出结果：

f1=8, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=9, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=10, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=11, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=12, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=13, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=14, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=15, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90
f1=16, f2=61 62 63 d0 9e d0 9b d0 ac d0 93 d0 90

11 Reference Cursor流

OTL为OTL/OCI8/8i/9i/10g提供了类otl_refcur_stream，它可以从reference cursor类型的绑定变量中读取结果行，其定义如下所示。

 1 class otl_refcur_stream {
 2 public:
 3  void set_column_type(const int column_ndx,
 4                       const int col_type,
 5                       const int col_size=0);
 6  void set_all_column_types(const unsigned mask=0);
 7   
 8  void rewind(void);
 9  int is_null(void);
10  int eof(void);
11  void close(void);
12   
13  otl_column_desc* describe_select(int& desc_len);
14   
15  otl_var_desc* describe_out_vars(int& desc_len);
16  otl_var_desc* describe_next_out_var(void); 
17   
18  otl_refcur_stream& operator>>(char& c);
19  otl_refcur_stream& operator>>(unsigned char& c);
20  otl_refcur_stream& operator>>(char* s);
21  otl_refcur_stream& operator>>(unsigned char* s);
22  otl_refcur_stream& operator>>(int& n);
23  otl_refcur_stream& operator>>(unsigned& u);
24  otl_refcur_stream& operator>>(short& sh);
25  otl_refcur_stream& operator>>(long int& l);
26  otl_refcur_stream& operator>>(float& f);
27  otl_refcur_stream& operator>>(double& d);
28   
29   
30 //for large whole number
31  otl_refcur_stream& operator>>(OTL_BIGINT& n);
32     
33  //for LOBs
34  otl_refcur_stream& operator>>(otl_long_string& s);
35  otl_refcur_stream& operator>>(otl_datetime& dt);
36  otl_refcur_stream& operator>>(otl_lob_stream& lob);                      
37  otl_refcur_stream& operator>>(std::string& s); 
38  
39 //for UNICODE
40  otl_stream& operator>>(unsigned char* s);
41  otl_stream& operator>>(otl_long_unicode_string& s); 
42      
43 }; // end of otl_refcur_stream

otl_refcur_stream的初始化通过将otl_refcur_stream对象作为otl_stream的>>操作符参数来实现。以下是其基本使用的示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  6 //#define OTL_ORA8I // Compile OTL 4.0/OCI8i
  7 //#define OTL_ORA9I // Compile OTL 4.0/OCI9i
  8 #include <otlv4.h> // include the OTL 4.0 header file
  9 
 10 otl_connect db; // connect object
 11 
 12 void insert()
 13 // insert rows into table
 14 {
 15     otl_stream o(50, // buffer size
 16         "insert into test_tab values(:f1<float>,:f2<char[31]>)", // SQL statement
 17         db // connect object
 18     );
 19     char tmp[32];
 20 
 21     for (int i = 1; i <= 100; ++i) {
 22         sprintf(tmp, "Name%d", i);
 23         o << (float)i << tmp;
 24     }
 25 }
 26 
 27 void select()
 28 {
 29 
 30     // :cur is a bind variable name, refcur -- its type, 
 31     // out -- output parameter, 50 -- the buffer size when this
 32     // reference cursor will be attached to otl_refcur_stream
 33     otl_stream i(1, // buffer size
 34         "begin "
 35         " open :cur<refcur,out[50]> for "
 36         "  select * "
 37         "  from test_tab "
 38         "  where f1>=:f<int,in> and f1<=:f*2; "
 39         "end;", // PL/SQL block returns a referenced cursor
 40         db // connect object
 41     );
 42     // create select stream with referenced cursor
 43 
 44     i.set_commit(0); // set stream "auto-commit" to OFF.
 45 
 46     float f1;
 47     char f2[31];
 48     otl_refcur_stream s; // reference cursor stream for reading rows.
 49 
 50     i << 8; // assigning :f = 8
 51     i >> s;// initializing the refrence cursor stream with the output 
 52            // reference cursor.
 53 
 54     while (!s.eof()) { // while not end-of-data
 55         s >> f1 >> f2;
 56         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 57     }
 58 
 59     s.close(); // closing the reference cursor
 60 
 61     i << 4; // assigning :f = 4
 62     i >> s;
 63 
 64     while (!s.eof()) { // while not end-of-data
 65         s >> f1 >> f2;
 66         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 67     }
 68     // there is no need to explicitly calls s.close() since s's destructor 
 69     // will take care of closing the stream
 70 }
 71 
 72 int main()
 73 {
 74     otl_connect::otl_initialize(); // initialize OCI environment
 75     try {
 76 
 77         db.rlogon("scott/tiger"); // connect to Oracle
 78 
 79         otl_cursor::direct_exec
 80         (
 81             db,
 82             "drop table test_tab",
 83             otl_exception::disabled // disable OTL exceptions
 84         ); // drop table
 85 
 86         otl_cursor::direct_exec
 87         (
 88             db,
 89             "create table test_tab(f1 number, f2 varchjar2(30))"
 90         );  // create table
 91 
 92         insert(); // insert records into table
 93         select(); // select records from table
 94 
 95     }
 96     catch (otl_exception& p) { // intercept OTL exceptions
 97         cerr << p.msg << endl; // print out error message
 98         cerr << p.stm_text << endl; // print out SQL that caused the error
 99         cerr << p.var_info << endl; // print out the variable that caused the error
100     }
101 
102     db.logoff(); // disconnect from Oracle
103 
104     return 0;
105 
106 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

12 杂项

12.1 使用otl_nocommit_stream避免SQL执行成功后立刻提交事务

otl_stream提供了方法set_commit()方法(见4.1小节)设置auto_commit标志。该标志控制SQL执行成功后，是否立刻提交当前事务。auto_commit默认为true, 即提交事务。可以通过设置auto_commit标志值为false避免这种情况。

otl_stream的派生类otl_nocimmit_stream提供了SQL执行成功后不立刻提交事务的功能，该类通过将auto_commit标记默认设置为true, 以简化实现该功能时的代码编写。

以下是使用otl_nocommit_stream类避免SQL执行成功后立刻提交事务的示例代码。

 1 #include <iostream>
 2 using namespace std;
 3 
 4 #include <stdio.h>
 5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
 6 #include <otlv4.h> // include the OTL 4.0 header file
 7 
 8 otl_connect db; // connect object
 9 
10 void insert()
11 // insert rows into table
12 {
13     otl_nocommit_stream o
14     (50, // buffer size
15         "insert into test_tab values(:f1<float>,:f2<char[31]>)", // SQL statement
16         db // connect object
17     );
18     char tmp[32];
19 
20     for (int i = 1; i <= 100; ++i) {
21         sprintf(tmp, "Name%d", i);
22         o << (float)i << tmp;
23     }
24 
25     o.flush();
26     db.commit();
27 
28 }
29 
30 void select()
31 {
32     otl_stream i(50, // buffer size
33         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
34         // SELECT statement
35         db // connect object
36     );
37     // create select stream
38 
39     float f1;
40     char f2[31];
41 
42     i << 8; // assigning :f = 8
43             // SELECT automatically executes when all input variables are
44             // assigned. First portion of output rows is fetched to the buffer
45 
46     while (!i.eof()) { // while not end-of-data
47         i >> f1 >> f2;
48         cout << "f1=" << f1 << ", f2=" << f2 << endl;
49     }
50 
51     i << 4; // assigning :f = 4
52             // SELECT automatically executes when all input variables are
53             // assigned. First portion of output rows is fetched to the buffer
54 
55     while (!i.eof()) { // while not end-of-data
56         i >> f1 >> f2;
57         cout << "f1=" << f1 << ", f2=" << f2 << endl;
58     }
59 
60 }
61 
62 int main()
63 {
64     otl_connect::otl_initialize(); // initialize OCI environment
65     try {
66 
67         db.rlogon("scott/tiger"); // connect to Oracle
68 
69         otl_cursor::direct_exec
70         (
71             db,
72             "drop table test_tab",
73             otl_exception::disabled // disable OTL exceptions
74         ); // drop table
75 
76         otl_cursor::direct_exec
77         (
78             db,
79             "create table test_tab(f1 number, f2 varchar2(30))"
80         );  // create table
81 
82         insert(); // insert records into table
83         select(); // select records from table
84 
85     }
86 
87     catch (otl_exception& p) { // intercept OTL exceptions
88         cerr << p.msg << endl; // print out error message
89         cerr << p.stm_text << endl; // print out SQL that caused the error
90         cerr << p.var_info << endl; // print out the variable that caused the error
91     }
92 
93     db.logoff(); // disconnect from Oracle
94 
95     return 0;
96 
97 }

输出结果：

f1=8, f2=Name8

f1=9, f2=Name9

f1=10, f2=Name10

f1=11, f2=Name11

f1=12, f2=Name12

f1=13, f2=Name13

f1=14, f2=Name14

f1=15, f2=Name15

f1=16, f2=Name16

f1=4, f2=Name4

f1=5, f2=Name5

f1=6, f2=Name6

f1=7, f2=Name7

f1=8, f2=Name8

12.2 SELECT中的数据类型映射覆写

otl_stream在执行SELECT语句返回结果列时，具有如下表12-1所示的数据库数据类型(Database datatype)到默认数据类型(Default datatype)的映射。

某些情况下往往需要对这种默认映射进行覆写，即将数据库数据类型映射为非默认的数据类型。例如当读取超过16位的数字时，将其映射为字符串类型往往更方便。为此，otl_stream提供了set_colunm_type()方法(参见4.1小节)进行数据类型覆写。

12-1 otl_stream中的数据类型映射覆写

Database datatype	Default datatype	Datatype override
NUMBER (Oracle)	otl_var_double	otl_var_char, otl_var_int, otl_var_float, otl_var_short, otl_var_unsigned_int
NUMERIC, FLOAT, REAL, MONEY, DECIMAL (MS SQL Server, Sybase, DB2)	otl_var_double	otl_var_char, otl_var_int, otl_var_float, otl_var_short, otl_var_unsigned_int, otl_var_long_int
INT (MS SQL Server, Sybase, DB2)	otl_var_int	otl_var_char, otl_var_double, otl_var_float, otl_var_short, otl_var_unsigned_int, otl_var_long_int
SMALLINT, TINYINT (MS SQL Server, Sybase, DB2)	otl_var_short	otl_var_char, otl_var_int, otl_var_float, otl_var_double, otl_var_unsigned_int, otl_var_long_int
DATE (Oracle), DATETIME (MS SQL Server, Sybase)	otl_timestamp	otl_var_char
LONG (Oracle)	otl_var_varchar_long	otl_var_char (<=32000 bytes)
TEXT (MS SQL Server, Sybase)	otl_var_varchar_long	otl_var_char(<= max. size of varchar, e.g. <=8000 in MS SQL 7.0)

以下是SELECT中的数据类型覆写的示例代码。

 1 #include <iostream>
 2 using namespace std;
 3 
 4 #include <stdio.h>
 5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
 6 #include <otlv4.h> // include the OTL 4.0 header file
 7 
 8 otl_connect db; // connect object
 9 
10 void insert()
11 // insert rows into table
12 {
13     otl_stream o(50, // buffer size
14         "insert into test_tab values(12345678900000000+:f1<int>,:f2<char[31]>)",
15         // SQL statement
16         db // connect object
17     );
18     char tmp[32];
19 
20     for (int i = 1; i <= 100; ++i) {
21         sprintf(tmp, "Name%d", i);
22         o << i << tmp;
23     }
24 }
25 
26 void select()
27 {
28     otl_stream i;
29 
30     i.set_column_type(1, otl_var_char, 40);// use a string(40) instead of default double
31     i.open(50, // buffer size
32         "select * from test_tab "
33         "where f1>=12345678900000000+:f<int> "
34         "  and f1<=12345678900000000+:f*2",
35         // SELECT statement
36         db // connect object
37     );
38     // create select stream
39 
40     char f1[40];
41     char f2[31];
42 
43     i << 8; // assigning :f = 8
44             // SELECT automatically executes when all input variables are
45             // assigned. First portion of output rows is fetched to the buffer
46 
47     while (!i.eof()) { // while not end-of-data
48         i >> f1 >> f2;
49         cout << "f1=" << f1 << ", f2=" << f2 << endl;
50     }
51 
52     i << 4; // assigning :f = 4
53             // SELECT automatically executes when all input variables are
54             // assigned. First portion of output rows is fetched to the buffer
55 
56     while (!i.eof()) { // while not end-of-data
57         i >> f1 >> f2;
58         cout << "f1=" << f1 << ", f2=" << f2 << endl;
59     }
60 
61 }
62 
63 int main()
64 {
65     otl_connect::otl_initialize(); // initialize OCI environment
66     try {
67 
68         db.rlogon("scott/tiger"); // connect to Oracle
69 
70         otl_cursor::direct_exec
71         (
72             db,
73             "drop table test_tab",
74             otl_exception::disabled // disable OTL exceptions
75         ); // drop table
76 
77         otl_cursor::direct_exec
78         (
79             db,
80             "create table test_tab(f1 number, f2 varchar2(30))"
81         );  // create table
82 
83         insert(); // insert records into table
84         select(); // select records from table
85 
86     }
87 
88     catch (otl_exception& p) { // intercept OTL exceptions
89         cerr << p.msg << endl; // print out error message
90         cerr << p.stm_text << endl; // print out SQL that caused the error
91         cerr << p.var_info << endl; // print out the variable that caused the error
92     }
93 
94     db.logoff(); // disconnect from Oracle
95 
96     return 0;
97 
98 }

输出结果：

f1=12345678900000008, f2=Name8
f1=12345678900000009, f2=Name9
f1=12345678900000010, f2=Name10
f1=12345678900000011, f2=Name11
f1=12345678900000012, f2=Name12
f1=12345678900000013, f2=Name13
f1=12345678900000014, f2=Name14
f1=12345678900000015, f2=Name15
f1=12345678900000016, f2=Name16
f1=12345678900000004, f2=Name4
f1=12345678900000005, f2=Name5
f1=12345678900000006, f2=Name6
f1=12345678900000007, f2=Name7
f1=12345678900000008, f2=Name8

12.3 使用OTL tracing跟踪OTL的方法调用

OTL可以通过宏定义打开内部的跟踪机制，方便程序的调试。通过宏OTL_TRACE_LEVEL指明跟踪的级别，通过宏OTL_TRACE_STREAM指明跟踪消息的输出流，通过宏OTL_TRACE_LINE_PREFIX指明跟踪消息的输出头。

以下是使用OTL tracing来跟踪OTL方法调用的示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 #include <stdio.h>
  4 unsigned int my_trace_level=
  5    0x1 | // 1st level of tracing
  6    0x2 | // 2nd level of tracing
  7    0x4 | // 3rd level of tracing
  8    0x8 | // 4th level of tracing
  9    0x10; // 5th level of tracing
 10 // each level of tracing is represented by its own bit, 
 11 // so levels of tracing can be combined in an arbitrary order.
 12 
 13 #define OTL_TRACE_LEVEL my_trace_level
 14    // enables OTL tracing, and uses my_trace_level as a trace control variable.
 15 
 16 #define OTL_TRACE_STREAM cerr 
 17    // directs all OTL tracing to cerr
 18 
 19 #define OTL_TRACE_LINE_PREFIX "MY OTL TRACE ==> " 
 20    // redefines the default OTL trace line prefix. This #define is optional
 21 
 22 #define OTL_ORA9I // Compile OTL 4.0/OCI9i
 23 // #define OTL_ORA8I // Compile OTL 4.0/OCI8i
 24 // #define OTL_ORA8 // Compile OTL 4.0/OCI8
 25 #include <otlv4.h> // include the OTL 4.0 header file
 26 
 27 otl_connect db; // connect object
 28 
 29 void insert()
 30 // insert rows into table
 31 { 
 32  otl_stream o(10, // buffer size
 33               "insert into test_tab values(:f1<int>,:f2<char[31]>)", // SQL statement
 34               db // connect object
 35              );
 36  char tmp[32];
 37 
 38  for(int i=1;i<=23;++i){
 39   sprintf(tmp,"Name%d",i);
 40   o<<i<<tmp;
 41  }
 42 }
 43 
 44 void select()
 45 { 
 46  otl_stream i(5, // buffer size
 47               "select * from test_tab where f1>=:f<int> and f1<=:ff<int>*2",
 48                  // SELECT statement
 49               db // connect object
 50              ); 
 51    // create select stream
 52  
 53  float f1;
 54  char f2[31];
 55 
 56  i<<8<<8; // assigning :f = 8; :ff = 8
 57    // SELECT automatically executes when all input variables are
 58    // assigned. First portion of output rows is fetched to the buffer
 59 
 60  while(!i.eof()){ // while not end-of-data
 61   i>>f1>>f2;
 62   cout<<"f1="<<f1<<", f2="<<f2<<endl;
 63  }
 64 
 65 }
 66 
 67 int main()
 68 {
 69  otl_connect::otl_initialize(); // initialize OCI environment
 70  try{
 71 
 72   db.rlogon("scott/tiger"); // connect to the database
 73 
 74   otl_cursor::direct_exec
 75    (
 76     db,
 77     "drop table test_tab",
 78     otl_exception::disabled // disable OTL exceptions
 79    ); // drop table
 80 
 81   otl_cursor::direct_exec
 82    (
 83     db,
 84     "create table test_tab(f1 int, f2 varchar(30))"
 85     );  // create table
 86 
 87   insert(); // insert records into table
 88   select(); // select records from table
 89 
 90  }
 91  catch(otl_exception& p){ // intercept OTL exceptions
 92   cerr<<p.msg<<endl; // print out error message
 93   cerr<<p.stm_text<<endl; // print out SQL that caused the error
 94   cerr<<p.var_info<<endl; // print out the variable that caused the error
 95  }
 96 
 97  db.logoff(); // disconnect from the database
 98 
 99  return 0;
100 }

输出结果：

MY OTL TRACE ==> otl_connect(this=004332D4)::rlogon(connect_str="scott/*****", auto_commit=0); 
MY OTL TRACE ==> otl_cursor::direct_exec(connect=004332CC,sqlstm="drop table test_tab",exception_enabled=0);
MY OTL TRACE ==> otl_cursor::direct_exec(connect=004332CC,sqlstm="create table test_tab(f1 int, f2 varchar(30))",exception_enabled=1);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::open(buffer_size=10, sqlstm=insert into test_tab values(:f1<int>,:f2<char[31]>), connect=004332CC); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=1); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name1"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=2); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name2"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=3); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name3"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=4); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name4"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=5); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name5"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=6); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name6"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=7); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name7"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=8); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name8"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=9); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name9"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=10); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name10"); 
MY OTL TRACE ==> otl_stream, executing SQL Stm=insert into test_tab values(:f1     ,:f2          ), current batch size=10, row offset=0
MY OTL TRACE ==> otl_connect(this=004332CC)::commit(); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=11); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name11"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=12); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name12"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=13); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name13"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=14); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name14"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=15); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name15"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=16); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name16"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=17); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name17"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=18); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name18"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=19); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name19"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=20); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name20"); 
MY OTL TRACE ==> otl_stream, executing SQL Stm=insert into test_tab values(:f1     ,:f2          ), current batch size=10, row offset=0
MY OTL TRACE ==> otl_connect(this=004332CC)::commit(); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=21); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name21"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=22); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name22"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f1, value=23); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(char*: ftype=1, placeholder=:f2, value="Name23"); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::close(); 
MY OTL TRACE ==> otl_stream, executing SQL Stm=insert into test_tab values(:f1     ,:f2          ), current batch size=3, row offset=0
MY OTL TRACE ==> otl_connect(this=004332CC)::commit(); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::open(buffer_size=5, sqlstm=select * from test_tab where f1>=:f<int> and f1<=:ff<int>*2, connect=004332CC); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:f, value=8); 
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator <<(int: ftype=4, placeholder=:ff, value=8); 
MY OTL TRACE ==> otl_stream, executing SQL Stm=select * from test_tab where f1>=:f      and f1<=:ff     *2, buffer size=5
MY OTL TRACE ==> otl_stream, fetched the first batch of rows, SQL Stm=select * from test_tab where f1>=:f      and f1<=:ff     *2, RPC=5
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=8);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name8");
f1=8, f2=Name8
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=9);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name9");
f1=9, f2=Name9
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=10);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name10");
f1=10, f2=Name10
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=11);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name11");
f1=11, f2=Name11
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=12);
MY OTL TRACE ==> otl_stream, fetched the next batch of rows, SQL Stm=select * from test_tab where f1>=:f      and f1<=:ff     *2, RPC=9
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name12");
f1=12, f2=Name12
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=13);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name13");
f1=13, f2=Name13
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=14);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name14");
f1=14, f2=Name14
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=15);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name15");
f1=15, f2=Name15
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(float& : ftype=2, placeholder=F1, value=16);
MY OTL TRACE ==> otl_stream(this=0012FEFC)::operator >>(char* : ftype=1, placeholder=F2, value="Name16");
f1=16, f2=Name16
MY OTL TRACE ==> otl_stream(this=0012FEFC)::close(); 
MY OTL TRACE ==> otl_connect(this=004332CC)::logoff();

12.4 获取已处理行数(Rows Processed Count)

otl_stream提供了get_rpc()方法(参见4.1小节)获取已处理行数。另外，类otl_cursor的direct_exec()方法如果处理成功也返回已处理行数。

已处理行数和INSERT、UPDATE以及DELETE语句有关。对于INSERT语句而言，已处理行数可能小于等于流的缓冲区大小。对于UPDATE以及DELETE而言，则和多少行被更新或删除相关。

以下是获取已处理行数的示例代码。

 1 #include <iostream>
 2 using namespace std;
 3   
 4 #include <stdio.h>
 5 #define OTL_ORA8 // Compile OTL 4.0/OCI8
 6 #include <otlv4.h> // include the OTL 4.0 header file
 7 
 8 otl_connect db; // connect object
 9 
10 void insert()
11 // insert rows into table
12 { 
13  otl_stream o(200, // buffer size
14               "insert into test_tab values(:f1<float>,:f2<char[31]>)", 
15                  // SQL statement
16               db // connect object
17              );
18  char tmp[32];
19 
20  for(int i=1;i<=123;++i){
21   sprintf(tmp,"Name%d",i);
22   o<<(float)i<<tmp;
23  }
24  o.flush();
25 
26  cout<<"Rows inserted: "<<o.get_rpc()<<endl;
27 
28 }
29 
30 void delete_rows()
31 { 
32   long rpc=otl_cursor::direct_exec(db,"delete from test_tab where f1>=95");
33   
34   cout<<"Rows deleted: "<<rpc<<endl;
35 }
36 
37 int main()
38 {
39  otl_connect::otl_initialize(); // initialize OCI environment
40  try{
41 
42   db.rlogon("scott/tiger"); // connect to Oracle
43 
44   otl_cursor::direct_exec
45    (
46     db,
47     "drop table test_tab",
48     otl_exception::disabled // disable OTL exceptions
49    ); // drop table
50 
51   otl_cursor::direct_exec
52    (
53     db,
54     "create table test_tab(f1 number, f2 varchar2(30))"
55     );  // create table
56 
57   insert(); // insert records into table
58   delete_rows(); // select records from table
59 
60  }
61 
62  catch(otl_exception& p){ // intercept OTL exceptions
63   cerr<<p.msg<<endl; // print out error message
64   cerr<<p.stm_text<<endl; // print out SQL that caused the error
65   cerr<<p.var_info<<endl; // print out the variable that caused the error
66  }
67 
68  db.logoff(); // disconnect from Oracle
69 
70  return 0;
71 
72 }

输出结果：

Rows inserted: 123
Rows deleted: 29

12.5 使用otl_connect的重载运算符<<, <<=, >>

otl_connect重载了<<, <<=和>>运算符(参见4.2小节)，用于简化编程操作。其中操作符<<发送字符串到otl_connect对象。如果该otl_connect对象还没有连接到数据库则字符串为"userid/passwd@db"格式的连接字符串，它使得otl_connect对象能够立刻连接数据库。如果该otl_connect对象已经连接到数据库则字符串被当作为静态SQL语句立刻执行。

操作符<<=发送字符串到otl_connect对象。otl_connect对象将保存该字符串并被下一个>>操作符使用。该字符串是一个拥有占位符并且能够发送到otl_stream对象的SQL语句。操作符>>取出之前使用操作符<<=保存的SQL语句并则发送到otl_stream对象。它使得该SQL语句被otl_stream打开。

以下是使用otl_connect重载的<<, <<=和>>运算符示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 // #define OTL_ORA8 // Compile OTL 4.0/OCI8
  7 // #define OTL_ORA8I // Compile OTL 4.0/OCI8i
  8 // #define OTL_ORA9I // Compile OTL 4.0/OCI9I
  9 // #define OTL_ORA10G // Compile OTL 4.0/OCI10g
 10 #define OTL_ORA10G_R2 // Compile OTL 4.0/OCI10gR2
 11 #include <otlv4.h> // include the OTL 4.0 header file
 12 
 13 otl_connect db; // connect object
 14 
 15 void insert()
 16 // insert rows into table
 17 { 
 18  otl_stream o;
 19  o.setBufSize(50);
 20 
 21 // Send a message (SQL statement) to the otl_connect object.
 22  db<<="insert into test_tab values(:f1<int>,:f2<char[31]>)";
 23 
 24 // Send a message (SQL statement) from the connect object 
 25 // to the otl_stream object. 
 26 
 27  db>>o;
 28 
 29 // By and large, this is all syntactical sugar, but "some like it hot".
 30 
 31  char tmp[32];
 32 
 33  for(int i=1;i<=100;++i){
 34   sprintf(tmp,"Name%d",i);
 35   o<<i<<tmp;
 36  }
 37 }
 38 
 39 void select()
 40 { 
 41  otl_stream i;
 42 
 43  i.setBufSize(50);
 44 
 45 // Send a message (SQL statement) to the otl_connect object.
 46  db<<="select * from test_tab where f1>=:f11<int> and f1<=:f12<int>*2";
 47 
 48 // Send a message (SQL statement) from the connect object 
 49 // to the otl_stream object. 
 50 
 51  db>>i;
 52 
 53 // By and large, this is all syntactical sugar, but "some like it hot".
 54  
 55  int f1;
 56  char f2[31];
 57 
 58  i<<8<<8; // assigning :f11 = 8, :f12 = 8
 59    // SELECT automatically executes when all input variables are
 60    // assigned. First portion of output rows is fetched to the buffer
 61 
 62  while(!i.eof()){ // while not end-of-data
 63   i>>f1>>f2;
 64   cout<<"f1="<<f1<<", f2="<<f2<<endl;
 65  }
 66 
 67  i<<4<<4; // assigning :f11 = 8, :f12 = 8
 68    // SELECT automatically executes when all input variables are
 69    // assigned. First portion of output rows is fetched to the buffer
 70 
 71  while(!i.eof()){ // while not end-of-data
 72   i>>f1>>f2;
 73   cout<<"f1="<<f1<<", f2="<<f2<<endl;
 74  }
 75 
 76 }
 77 
 78 int main()
 79 {
 80  otl_connect::otl_initialize(); // initialize the database API environment
 81  try{
 82 
 83   db<<"scott/tiger";// connect to the database
 84 
 85   // Send SQL statements to the connect obejct for immediate execution. 
 86   // Ignore any exception for the first statement.
 87   try{ db<<"drop table test_tab"; } catch(otl_exception&){}
 88   db<<"create table test_tab(f1 int, f2 varchar(30))";
 89 
 90   insert(); // insert records into table
 91   select(); // select records from table
 92 
 93  }
 94 
 95  catch(otl_exception& p){ // intercept OTL exceptions
 96   cerr<<p.msg<<endl; // print out error message
 97   cerr<<p.stm_text<<endl; // print out SQL that caused the error
 98   cerr<<p.var_info<<endl; // print out the variable that caused the error
 99  }
100 
101  db.logoff(); // disconnect from the database
102 
103  return 0;
104 
105 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

12.6 手工刷新otl_stream缓冲区

otl_stream提供了方法set_flush()方法(见4.1小节)设置auto_flush标志。该标志仅仅控制析构函数中由于输出缓冲区数据变脏引起的刷新，并不能控制缓冲区填满引起自动刷新。auto_flush标志默认为true即进行刷新。

提供set_flush()方法控制otl_stream析构函数中的刷新操作和C++的异常机制有关。C++异常机制退出作用域时，局部对象会由于堆栈回退发生析构。如果在析构函数中又产生异常将导致std::terminate()被调用，从而使程序中止。

otl_stream析构函数中的输出缓冲区刷新在某些情况下极有可能导致这种级联异常发生，因此otl_stream提供了set_flush()方法进行控制。如果auto_flush被设置成false，则必须手工调用otl_stream的flush()或close()方法进行刷新。当然，前提是输出缓冲区还没有填满，因为缓冲区填满的自动刷新是不能通过set_flush()控制的。

以下是手工刷新otl_stream缓冲区的示例代码。

  1 #include <iostream>
  2 #include <stdio.h>
  3 
  4 // Uncomment the line below when OCI7 is used with OTL
  5 // #define OTL_ORA7 // Compile OTL 4.0/OCI7 
  6 
  7 
  8 using namespace std;
  9 
 10 #define OTL_ORA8 // Compile OTL 4.0/OCI8
 11 #include <otlv4.h> // include the OTL 4.0 header file
 12 
 13 otl_connect db; // connect object
 14 
 15 void insert1()
 16 // insert rows into table
 17 {
 18     otl_stream o; // define an otl_stream variable
 19 
 20     o.set_flush(false); // set the auto-flush flag to OFF.
 21 
 22     o.open(200, // buffer size
 23         "insert into test_tab values(:f1<float>,:f2<char[31]>)", // SQL statement
 24         db // connect object
 25     );
 26     char tmp[32];
 27     for (int i = 1; i <= 100; ++i) {
 28         sprintf(tmp, "Name%d", i);
 29         o << (float)i << tmp;
 30         if (i % 55 == 0)
 31             throw "Throwing an exception";
 32     }
 33 
 34     o.flush();  // when the auto-flush flag is OFF, an explicit flush
 35                 // of the stream buffer is required in case of successful
 36                 // completion of execution of the INSERT statement.
 37                 // In case of a raised exception, the stream buffer would not be flushed.          
 38 }
 39 
 40 void insert2()
 41 // insert rows into table
 42 {
 43     otl_stream o; // define an otl_stream variable
 44 
 45     o.set_flush(false); // set the auto-flush flag to OFF.
 46 
 47     o.open(200, // buffer size
 48         "insert into test_tab values(:f1<float>,:f2<char[31]>)", // SQL statement
 49         db // connect object
 50     );
 51     char tmp[32];
 52 
 53     for (int i = 1; i <= 100; ++i) {
 54         sprintf(tmp, "Name%d", i);
 55         o << (float)i << tmp;
 56         //if(i%55==0)
 57         //   throw "Throwing an exception";
 58     }
 59     o.flush();   // when the auto-flush flag is OFF, an explicit flush
 60                  // of the stream buffer is required in case of successful
 61                  // completion of execution of the INSERT statement.
 62                  // In case of a raised exception, the stream buffer would not be flushed.          
 63 }
 64 
 65 void select()
 66 {
 67     otl_stream i(50, // buffer size
 68         "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 69         // SELECT statement
 70         db // connect object
 71     );
 72     // create select stream
 73 
 74     float f1;
 75     char f2[31];
 76 
 77     i << 8; // assigning :f = 8
 78             // SELECT automatically executes when all input variables are
 79             // assigned. First portion of output rows is fetched to the buffer
 80 
 81     while (!i.eof()) { // while not end-of-data
 82         i >> f1 >> f2;
 83         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 84     }
 85 
 86     i << 4; // assigning :f = 4
 87             // SELECT automatically executes when all input variables are
 88             // assigned. First portion of output rows is fetched to the buffer
 89 
 90     while (!i.eof()) { // while not end-of-data
 91         i >> f1 >> f2;
 92         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 93     }
 94 
 95 }
 96 
 97 int main()
 98 {
 99     otl_connect::otl_initialize(); // initialize OCI environment
100     try {
101 
102         db.rlogon("scott/tiger"); // connect to Oracle
103 
104         otl_cursor::direct_exec
105         (
106             db,
107             "drop table test_tab",
108             otl_exception::disabled // disable OTL exceptions
109         ); // drop table
110 
111         otl_cursor::direct_exec
112         (
113             db,
114             "create table test_tab(f1 number, f2 varchar2(30))"
115         ); // create table
116 
117         try {
118             insert1(); // insert records into table
119         }
120         catch (const char* p) {
121             cout << p << endl;
122         }
123         cout << "Selecting the first time around:" << endl;
124         select(); // select records from table
125 
126         insert2();
127         cout << "Selecting the second time around:" << endl;
128         select();
129 
130     }
131 
132     catch (otl_exception& p) { // intercept OTL exceptions
133         cerr << p.msg << endl; // print out error message
134         cerr << p.stm_text << endl; // print out SQL that caused the error
135         cerr << p.var_info << endl; // print out the variable that caused the error
136     }
137 
138     db.logoff(); // disconnect from Oracle
139 
140     return 0;
141 }

输出结果：

Throwing an exception
Selecting the first time around:
Selecting the second time around:
f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

12.7 忽略INSERT操作时的重复键异常

底层操作为Oracle API时，otl_stream提供了特殊版本的flush()方法(参见4.1小节)，该方法可以设置刷新的起始行以及忽略产生错误时抛出的otl_exception异常继续刷新。在进行INSERT操作有时候需要忽略重复键值引起的错误继续处理，这时可以利用该flush()方法进行处理。

以下是忽略INSERT操作时的重复键异常示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 //#define OTL_ORA8I // Compile OTL 4.0/OCI8
  6 #define OTL_ORA8I // Compile OTL 4.0/OCI8i
  7 // #define OTL_ORA9I // Compile OTL 4.0/OCI9i
  8 #include <otlv4.h> // include the OTL 4.0 header file
  9 
 10 otl_connect db; // connect object
 11 
 12 void insert()
 13 // insert rows into table
 14 {
 15     otl_stream o(10, // make the buffer size larger than the actual 
 16                      // row set to inserted, so that the stream will not
 17                      // flush the buffer automatically
 18         "insert into test_tab values(:f1<int>,:f2<char[31]>)", // SQL statement
 19         db // connect object
 20     );
 21 
 22     o.set_commit(0); // set stream's auto-commit to OFF.
 23 
 24     long total_rpc = 0; // total "rows processed count"
 25     long rpc = 0; // rows successfully processed in one flush() call
 26     int iters = 0; // number of rows to be bypassed
 27 
 28     try {
 29         o << 1 << "Line1"; // Enter one row into the stream
 30         o << 1 << "Line1"; // Enter the same data into the stream
 31                            // and cause a "duplicate key" error.
 32         o << 2 << "Line2"; // Enter one row into the stream
 33         o << 3 << "Line3"; // Enter one row into the stream
 34         o << 4 << "Line4"; // Enter one row into the stream
 35         o << 4 << "Line4"; // Enter the same data into the stream
 36                            // and cause a "duplicate key" error.
 37         o.flush();
 38     }
 39     catch (otl_exception& p) {
 40         if (p.code == 1) { // ORA-0001: ...duplicate key...
 41             ++iters;
 42             rpc = o.get_rpc();
 43             total_rpc = rpc;
 44             do {
 45                 try {
 46                     cout << "TOTAL_RPC=" << total_rpc << ", RPC=" << rpc << endl;
 47                     o.flush(total_rpc + iters,// bypass the duplicate row and start
 48                                               // with the rows after that
 49                         true // force buffer flushing regardless
 50                     );
 51                     rpc = 0;
 52                 }
 53                 catch (otl_exception& p2) {
 54                     if (p2.code == 1) { // ORA-0001: ... duplicate key ...
 55                         ++iters;
 56                         rpc = o.get_rpc();
 57                         total_rpc += rpc;
 58                     }
 59                     else
 60                         throw;
 61                 }
 62             } while (rpc>0);
 63         }
 64         else
 65             throw; // re-throw the exception to the outer catch block.
 66     }
 67 
 68     db.commit(); // commit transaction
 69 
 70 }
 71 
 72 void select()
 73 {
 74     otl_stream i(10, // buffer size
 75         "select * from test_tab",
 76         // SELECT statement
 77         db // connect object
 78     );
 79     // create select stream
 80 
 81     int f1;
 82     char f2[31];
 83 
 84     while (!i.eof()) { // while not end-of-data
 85         i >> f1 >> f2;
 86         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 87     }
 88 
 89 }
 90 
 91 int main()
 92 {
 93     otl_connect::otl_initialize(); // initialize OCI environment
 94     try {
 95 
 96         db.rlogon("scott/tiger"); // connect to Oracle
 97 
 98         otl_cursor::direct_exec
 99         (
100             db,
101             "drop table test_tab",
102             otl_exception::disabled // disable OTL exceptions
103         ); // drop table
104 
105         otl_cursor::direct_exec
106         (
107             db,
108             "create table test_tab(f1 number, f2 varchar2(30))"
109         );  // create table
110 
111         otl_cursor::direct_exec
112         (
113             db,
114             "create unique index ind001 on test_tab(f1)"
115         );  // create unique index 
116 
117         insert(); // insert records into table
118         select(); // select records from table
119 
120     }
121 
122     catch (otl_exception& p) { // intercept OTL exceptions
123         cerr << p.msg << endl; // print out error message
124         cerr << p.stm_text << endl; // print out SQL that caused the error
125         cerr << p.var_info << endl; // print out the variable that caused the error
126     }
127 
128     db.logoff(); // disconnect from Oracle
129 
130     return 0;
131 
132 }

输出结果：

TOTAL_RPC=1, RPC=1
TOTAL_RPC=4, RPC=3
f1=1, f2=Line1
f1=2, f2=Line2
f1=3, f2=Line3
f1=4, f2=Line4

12.8 使用模板otl_value<T>创建数据容器

otl_value<T>是一个OTL模板类，能够基于标量的数据类型包括int, unsigned, long, short, float, double, otl_datetime(OTL date&time container), std::string (STL string class)来创建派生的数据容器。

派生的数据容器具有内建的NULL指示器功能，即容器内部拥有一个NULL指示器域并且能够从一个操作传递到另一个操作。例如从SELECT语句读取并装入容器otl_value<int>的值是NULL，在该值被写入INSERT语句后，otl_value<int>容器中的NULL指示器域仍然保存该NULL值并且NULL值也从SELECT传递到了INSERT。

模板otl_value<T>的使用可以通过”#define OTL_STL”或者”#define OTL_VALUE_TEMPLATE_ON”激活。其定义如下：

 1 template<class TData> 
 2 
 3 class otl_value{
 4 
 5 public:
 6 
 7      TData v;
 8 
 9      bool ind;
10 
11  
12 
13      otl_value(); // default constructor
14 
15  
16 
17      otl_value(const otl_value<TData>& var); // copy constructor
18 
19      otl_value(const TData& var); // copy constructor
20 
21      otl_value(const otl_null& var); // copy constructor
22 
23  
24 
25      otl_value<TData>& operator=(const otl_value<TData>& var); //assignment operator
26 
27      otl_value<TData>& operator=(const TData& var); // assignment operator
28 
29      otl_value<TData>& operator=(const otl_null& var); // assignment operator
30 
31  
32 
33      bool is_null(void) const;
34 
35      void set_null(void);
36 
37      void set_non_null(void);
38 
39 }; // end of otl_value
40 
41  
42 
43 template <class TData>
44 
45 otl_stream& operator<<(otl_stream& s, const otl_value<TData>& var);
46 
47  
48 
49 template <class TData>
50 
51 otl_stream& operator>>(otl_stream& s, otl_value<TData>& var);
52 
53  
54 
55 template <class TData> std::ostream&
56 
57 operator<<(std::ostream& s, const otl_value<TData>& var);

其中方法set_null()和set_non_null()将otl_value设置成NULL或者非NULL，这两个方法必须直接操作public数据成员TData v或bool ind的时候才使用。数据成员v为标量数据类型值的存放容器，成员ind则为NULL指示器。

以下为使用otl_value<T>模板的示例代码。

  1 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  2 #define OTL_STL // Turn on STL features and otl_value<T>
  3 #define OTL_ANSI_CPP // Turn on ANSI C++ typecasts
  4 #include <otlv4.h> // include the OTL 4.0 header file
  5  
  6 using namespace std;
  7  
  8 otl_connect db; // connect object
  9  
 10 void insert()
 11 // insert rows into table
 12 { 
 13  otl_stream o(50, // buffer size
 14               "insert into test_tab "
 15               "values(:f1<int>,:f2<char[31]>,:f3<timestamp>)", 
 16                  // SQL statement
 17               db // connect object
 18              );
 19  
 20  otl_value<string> f2; // otl_value container of STL string
 21  otl_value<otl_datetime> f3; // container of otl_datetime
 22  
 23  
 24  for(int i=1;i<=100;++i){
 25  
 26   if(i%2==0)
 27      f2="NameXXX";
 28   else
 29      f2=otl_null(); // Assign otl_null() to f2
 30  
 31   if(i%3==0){
 32      // Assign a value to f3 via the .v field directly
 33      f3.v.year=2001;
 34      f3.v.month=1;
 35      f3.v.day=1;
 36      f3.v.hour=13;
 37      f3.v.minute=10;
 38      f3.v.second=5;
 39      f3.set_non_null(); // Set f3 as a "non-NULL"
 40   }else
 41      f3.set_null(); // Set f3 as null via .set_null() function
 42  
 43   o<<i<<f2<<f3;
 44  
 45  }
 46 }
 47  
 48 void select()
 49 { 
 50  otl_stream i(50, // buffer size
 51               "select * from test_tab where f1>=:f<int> and f1<=:f*2",
 52                  // SELECT statement
 53               db // connect object
 54              ); 
 55    // create select stream
 56  
 57  int f1; 
 58  otl_value<string> f2;
 59  otl_value<otl_datetime> f3;
 60  
 61  
 62  i<<8; // assigning :f = 8
 63    // SELECT automatically executes when all input variables are
 64    // assigned. First portion of output rows is fetched to the buffer
 65  
 66  while(!i.eof()){ // while not end-of-data
 67     i>>f1>>f2>>f3;
 68     cout<<"f1="<<f1<<", f2="<<f2<<", f3="<<f3<<endl;
 69  }
 70  
 71  i<<4; // assigning :f = 4
 72    // SELECT automatically executes when all input variables are
 73    // assigned. First portion of output rows is fetched to the buffer
 74  
 75  while(!i.eof()){ // while not end-of-data
 76   i>>f1>>f2>>f3;
 77   cout<<"f1="<<f1<<", f2="<<f2<<", f3="<<f3<<endl;
 78  }
 79  
 80 }
 81  
 82 int main()
 83 {
 84  otl_connect::otl_initialize(); // initialize OCI environment
 85  try{
 86  
 87   db.rlogon("scott/tiger"); // connect to Oracle
 88  
 89   otl_cursor::direct_exec
 90    (
 91     db,
 92     "drop table test_tab",
 93     otl_exception::disabled // disable OTL exceptions
 94    ); // drop table
 95  
 96   otl_cursor::direct_exec
 97    (
 98     db,
 99     "create table test_tab(f1 number, f2 varchar2(30), f3 date)"
100     );  // create table
101  
102   insert(); // insert records into table
103   select(); // select records from table
104  
105  }
106  
107  catch(otl_exception& p){ // intercept OTL exceptions
108   cerr<<p.msg<<endl; // print out error message
109   cerr<<p.stm_text<<endl; // print out SQL that caused the error
110   cerr<<p.var_info<<endl; // print out the variable that caused the error
111  }
112  
113  db.logoff(); // disconnect from Oracle
114  
115  return 0;
116  
117 }

输出结果：

f1=8, f2=NameXXX, f3=NULL

f1=9, f2=NULL, f3=1/1/2001 13:10:5

f1=10, f2=NameXXX, f3=NULL

f1=11, f2=NULL, f3=NULL

f1=12, f2=NameXXX, f3=1/1/2001 13:10:5

f1=13, f2=NULL, f3=NULL

f1=14, f2=NameXXX, f3=NULL

f1=15, f2=NULL, f3=1/1/2001 13:10:5

f1=16, f2=NameXXX, f3=NULL

f1=4, f2=NameXXX, f3=NULL

f1=5, f2=NULL, f3=NULL

f1=6, f2=NameXXX, f3=1/1/2001 13:10:5

f1=7, f2=NULL, f3=NULL

f1=8, f2=NameXXX, f3=NULL

12.9 使用OTL流的读迭代器遍历流返回的Reference Cursor

otl_stream的构造函数和open()方法(参见4.1小节)中参数sqlstm 可以为SQL, 也可以为PL/SQL块或者存储过程调用，如果流返回reference cursor，则需要在参数ref_cur_placeholder中指明占位符号。

以下是使用使用OTL流的读迭代器遍历OTL流返回的reference cursor的示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 // #define OTL_ORA7 // Compile OTL 4.0/OCI7
  7 //#define OTL_ORA8 // Compile OTL 4.0/OCI8
  8 //#define OTL_ORA8I // Compile OTL 4.0/OCI8i
  9 #define OTL_ORA9I // Compile OTL 4.0/OCI9i
 10 //#define OTL_ORA10G // Compile OTL 4.0/OCI10g
 11 #define OTL_STREAM_READ_ITERATOR_ON
 12 #include <otlv4.h> // include the OTL 4.0 header file
 13 
 14 otl_connect db; // connect object
 15 
 16 void insert()
 17 // insert rows into table
 18 { 
 19  otl_stream o(50, // buffer size
 20               "insert into test_tab values(:f1<int>,:f2<char[31]>)", // SQL statement
 21               db // connect object
 22              );
 23  char tmp[32];
 24 
 25  for(int i=1;i<=100;++i){
 26   sprintf(tmp,"Name%d",i);
 27   o<<i<<tmp;
 28  }
 29 }
 30 
 31 void select()
 32 { 
 33  otl_stream i(50, // buffer size
 34               "begin "
 35 
 36              "   open :cur1 for "
 37 
 38              "   select * from test_tab "
 39 
 40            "   where f1>=:f11<int> "
 41 
 42               "     and f1<=:f12<int>*2; "
 43 
 44            "end;", // SELECT statement
 45               db, // connect object
 46               ":cur1"
 47              ); 
 48    // create select stream
 49  
 50  int f1;
 51  char f2[31];
 52  otl_stream_read_iterator<otl_stream,otl_exception,otl_lob_stream> rs;
 53 
 54  i<<8<<8; // assigning :f11 = 8, :f12 = 8
 55    // SELECT automatically executes when all input variables are
 56    // assigned. First portion of output rows is fetched to the buffer
 57 
 58  rs.attach(i); // attach the iterator "rs" to the stream "i"
 59                // after the PL/SQL block is executed.
 60  while(rs.next_row()){ // while not end-of-data
 61     rs.get(1,f1);
 62     rs.get(2,f2);
 63     cout<<"f1="<<f1<<", f2="<<f2<<endl;
 64  }
 65 
 66  rs.detach(); // detach the itertor from the stream
 67 
 68  i<<4<<4; // assigning :f11 = 4, :f12 = 4
 69    // SELECT automatically executes when all input variables are
 70    // assigned. First portion of output rows is fetched to the buffer
 71 
 72  while(!i.eof()){ // while not end-of-data
 73     i>>f1>>f2;
 74     cout<<"f1="<<f1<<", f2="<<f2<<endl;
 75  }
 76 
 77 }
 78 
 79 int main()
 80 {
 81  otl_connect::otl_initialize(); // initialize OCI environment
 82  try{
 83 
 84   db.rlogon("scott/tiger"); // connect to Oracle
 85 
 86   otl_cursor::direct_exec
 87    (
 88     db,
 89     "drop table test_tab",
 90     otl_exception::disabled // disable OTL exceptions
 91    ); // drop table
 92 
 93   otl_cursor::direct_exec
 94    (
 95     db,
 96     "create table test_tab(f1 number, f2 varchar2(30))"
 97     );  // create table
 98 
 99   insert(); // insert records into table
100   select(); // select records from table
101 
102  }
103 
104  catch(otl_exception& p){ // intercept OTL exceptions
105   cerr<<p.msg<<endl; // print out error message
106   cerr<<p.stm_text<<endl; // print out SQL that caused the error
107   cerr<<p.var_info<<endl; // print out the variable that caused the error
108  }
109 
110  db.logoff(); // disconnect from Oracle
111 
112  return 0;
113 
114 }

输出结果：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

12.10使用Reference Cursor流从存储过程中返回多个Referece Cursor

otl_refcur_stream和reference cursor类型的占位符联合，允许在存储过程调用中返回多个reference cursor。以下为示例代码。

  1 #include <iostream>
  2 using namespace std;
  3 
  4 #include <stdio.h>
  5 
  6 #define OTL_ORA8 // Compile OTL 4.0/OCI8
  7 //#define OTL_ORA8I // Compile OTL 4.0/OCI8i
  8 //#define OTL_ORA9I // Compile OTL 4.0/OCI9i
  9 #include <otlv4.h> // include the OTL 4.0 header file
 10 
 11 otl_connect db; // connect object
 12 
 13 void insert()
 14 // insert rows into table
 15 {
 16     otl_stream o(50, // buffer size
 17         "insert into test_tab values(:f1<int>,:f2<char[31]>)", // SQL statement
 18         db // connect object
 19     );
 20     char tmp[32];
 21 
 22     for (int i = 1; i <= 100; ++i) {
 23         sprintf(tmp, "Name%d", i);
 24         o << i << tmp;
 25     }
 26 }
 27 
 28 void select()
 29 {
 30 
 31     // :str1 is an output string parameter
 32     // :cur1, :cur2 are a bind variable names, refcur -- their types, 
 33     // out -- output parameter, 50 -- the buffer size when this
 34     // reference cursor will be attached to otl_refcur_stream
 35     otl_stream i(1,// buffer size
 36         "begin "
 37         " my_pkg.my_proc(:f1<int,in>,:f2<int,in>, "
 38         "         :str1<char[100],out>, "
 39         "         :cur1<refcur,out[50]>, "
 40         "         :cur2<refcur,out[50]>); "
 41 
 42         "end;", // PL/SQL block that calls a stored procedure
 43         db // connect object
 44     );
 45 
 46 
 47     i.set_commit(0); // set stream "auto-commit" to OFF.
 48 
 49     char str1[101];
 50     float f1;
 51     char f2[31];
 52     otl_refcur_stream s1, s2; // reference cursor streams for reading rows.
 53 
 54     i << 8 << 4; // assigning :f1 = 8, :f2 = 4
 55     i >> str1; // reading :str1 from the stream
 56     i >> s1; // initializing the refeence cursor stream with the output 
 57 
 58              // reference cursor :cur1
 59     i >> s2;// initializing the refeence cursor stream with the output 
 60             // reference cursor :cur2
 61 
 62     cout << "STR1=" << str1 << endl;
 63     cout << "=====> Reading :cur1..." << endl;
 64     while (!s1.eof()) { // while not end-of-data
 65         s1 >> f1 >> f2;
 66         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 67     }
 68 
 69     cout << "=====> Reading :cur2..." << endl;
 70     while (!s2.eof()) { // while not end-of-data
 71         s2 >> f1 >> f2;
 72         cout << "f1=" << f1 << ", f2=" << f2 << endl;
 73     }
 74 
 75     s1.close(); // closing the reference cursor
 76     s2.close(); // closing the reference cursor
 77 }
 78 
 79 int main()
 80 {
 81     otl_connect::otl_initialize(); // initialize OCI environment
 82     try {
 83 
 84         db.rlogon("scott/tiger"); // connect to Oracle
 85 
 86         otl_cursor::direct_exec
 87         (
 88             db,
 89             "drop table test_tab",
 90             otl_exception::disabled // disable OTL exceptions
 91         ); // drop table
 92 
 93         otl_cursor::direct_exec
 94         (
 95             db,
 96             "create table test_tab(f1 number, f2 varchar2(30))"
 97         );  // create table
 98 
 99             // create a PL/SQL package
100         otl_cursor::direct_exec
101         (db,
102             "CREATE OR REPLACE PACKAGE my_pkg IS "
103             " "
104             "  TYPE my_cursor IS REF CURSOR; "
105             " "
106             "  PROCEDURE my_proc "
107             "    (f1_in IN NUMBER, "
108             "     f2_in IN NUMBER, "
109             "     str1 OUT VARCHAR2, "
110             "     cur1 OUT my_cursor, "
111             "     cur2 OUT my_cursor); "
112             " "
113             "END; "
114         );
115 
116         otl_cursor::direct_exec
117         (db,
118             "CREATE OR REPLACE PACKAGE BODY my_pkg IS "
119             " "
120             "  PROCEDURE my_proc "
121             "    (f1_in IN NUMBER, "
122             "     f2_in IN NUMBER, "
123             "     str1 OUT VARCHAR2, "
124             "     cur1 OUT my_cursor, "
125             "     cur2 OUT my_cursor) "
126             "  IS "
127             "    lv_cur1 my_cursor; "
128             "    lv_cur2 my_cursor; "
129             "  BEGIN "
130             "    OPEN lv_cur1 FOR "
131             "      SELECT * FROM test_tab "
132             "      WHERE f1>=f1_in  "
133             "        AND f1<=f1_in*2; "
134             "    OPEN lv_cur2 FOR "
135             "      SELECT * FROM test_tab "
136             "      WHERE f1>=f2_in  "
137             "        AND f1<=f2_in*2; "
138             "    str1 := 'Hello, world'; "
139             "    cur1 := lv_cur1; "
140             "    cur2 := lv_cur2; "
141             "  END; "
142             "   "
143             "END; "
144         );
145 
146         insert(); // insert records into table
147         select(); // select records from table
148 
149     }
150 
151     catch (otl_exception& p) { // intercept OTL exceptions
152         cerr << p.msg << endl; // print out error message
153         cerr << p.stm_text << endl; // print out SQL that caused the error
154         cerr << p.var_info << endl; // print out the variable that caused the error
155     }
156 
157     db.logoff(); // disconnect from Oracle
158 
159     return 0;
160 
161 }

输出结果：

STR1=Hello, world
=====> Reading :cur1...
f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
=====> Reading :cur2...
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8

13 最佳实践

13.1流缓冲区大小的设置

OTL流的性能主要被缓冲区大小一个参数控制，在创建使用OTL流时必须通过构造函数或open()函数的第一个参数arr_size指定，合理设置流缓冲区大小对数据库访问的性能优化尤其重要。

与INSERT/DELETE/UPDATE语句不同，SELECT语句的执行往往导致若干满足查询条件的记录行返回，OTL的底层实现将这两类语句的操作执行区分开来，因此SELECT语句相关的流缓冲区在作用和使用方式上与其他语句存在很大的差别。

对于SELECT语句而言，缓冲区大小主要影响OTL底层调用OCI接口OCIStmtFetch()一次性获取结果记录的行数。其底层实现如以下代码片断所示。

 1 / **
 2 
 3  *OTL底层的OCI接口封装类otl_cur的fetch()具体实现，其功能主要是
 4 
 5  *在执行SELECT语句后获取返回的记录行。
 6 
 7  *参数：iters 输入参数，从当前位置处一次性提取的记录数
 8 
 9  *      eof_date 输入输出参数，标识是否还有未获取的记录行，0为是，1为否
10 
11  */
12 
13 int fetch(const otl_stream_buffer_size_type iters, int& eof_data)
14 
15  {
16 
17         eof_data=0;
18 
19         status=OCIStmtFetch(
20 
21                cda,                              //语句句柄
22 
23                errhp,                             //错误句柄
24 
25                OTL_SCAST(ub4,iters),              //从当前位置处开始一次提取的记录数
26 
27                OTL_SCAST(ub4,OCI_FETCH_NEXT), //提取方向
28 
29                OTL_SCAST(ub4,OCI_DEFAULT)     //模式
30 
31         );
32 
33  
34 
35         eof_status=status;
36 
37         if(status!=OCI_SUCCESS&&
38 
39                status!=OCI_SUCCESS_WITH_INFO&&
40 
41                status!=OCI_NO_DATA)
42 
43                return 0;
44 
45  
46 
47         //如果记录被提取完或没有数据则
48 
49         //输入输出参数eof_data赋值为1，返回1
50 
51         if(status==OCI_NO_DATA){
52 
53                eof_data=1;
54 
55                return 1;
56 
57         }
58 
59      return 1;
60 
61  }

由于缓冲区大小作为第一参数传递给fetch()方法进行底层的OCI接口调用，因此缓冲区大小决定了从当前位置处开始一次提取的记录数。

对于查询数据量较大的应用，缓冲区大小的合理设置往往是性能优化的关键。表13-1为查询10000条记录时的缓冲区大小对结果行获取的影响。数据库为Oracle10g, 操作系统为HP-UNIX。

13-1 查询10000条记录时缓冲区大小不同设置的相应耗时

缓存区大小	耗时(s)
1	0.25
10	0.14
50	0.09
100	0.06
200	0.05
300	0.05
1000	0.05
2000	0.04

备注：(1) 测试环境为HP, Oracle10g

(2) 耗时仅指SQL执行成功后，记录的获取时间

由该表可见，对于查询结果为10000条记录的SELECT语句而言，缓冲区大小的设置对结果行的获取时间存在几十倍的差距。因此在使用OTL流进行数据库查询时，请合理设置缓冲区大小。

注意对于SELECT语句来说，缓冲区的大小并不影响其执行时机。当otl_stream以SELECT语句实例化，并设定好缓冲区大小后，一旦所有声明的绑定变量使用<<操作符被绑定完成，该SELECT语句将被OTL流立刻执行，与设置的缓存区具体大小并没有关系。

与此相反，对于INSERT/DELETE/UPDATE语句，缓冲区大小的设置将影响其执行时机。当缓冲区被SQL填满时，OTL流将自动刷新缓冲区，立刻批量执行缓冲区中所有的SQL。因此，当执行语句为INSERT/DELETE/UPDATE时，也应该根据需要合理设置缓冲区大小。

13.2批量操作注意的问题

当使用OTL的SQL变量绑定进行数据的批量操作时，应该注意otl_stream缓冲区大小的合理设置(参见13.1小节)以及OTL默认的语句执行成功后立刻提交事务。

对于批量操作，OTL默认的语句执行成功后立刻提交事务的操作方式往往不实用。例如对于批量更新10000条记录，缓冲区大小为1000的情况，当缓冲区填满时更新操作被执行，执行成功后当前事务被立刻提交，如果在此后的处理中出现错误，根本没有办法通过回滚事务取消之前的更新。防止语句执行后立刻提交事务的方法请参见12.1小节。

OTL流otl_stream 的这种默认操作方式，主要和底层调用OCI接口OCIStmtExecute()的相关实现有关。其底层实现如以下代码片断所示。

 1 / **
 2 
 3  *OTL底层的OCI接口封装类otl_cur的exec ()具体实现，其功能主要是
 4 
 5  *在执行SELECT语句。
 6 
 7  *参数：iters 输入参数，SQL语句执行次数
 8 
 9  *      rowoff  输入参数，绑定变量的开始偏移量
10 
11  */
12 
13 int exec(const int iters, const int rowoff)
14 
15  {
16 
17         //如果模式为语句执行成功后立刻提交事务则
18 
19         //设置相应的局部变量mode值
20 
21         ub4 mode;
22 
23         if(commit_on_success)
24 
25               mode=OCI_COMMIT_ON_SUCCESS;
26 
27         else
28 
29               mode=OCI_DEFAULT;
30 
31  
32 
33         //调用OCI接口OCIStmtExecute()执行SQL语句
34 
35         status=OCIStmtExecute(
36 
37               db->svchp,
38 
39        cda,
40 
41               errhp,
42 
43               OTL_SCAST(ub4,iters),
44 
45               OTL_SCAST(ub4,rowoff),
46 
47               0,
48 
49               0,
50 
51               mode
52 
53        );
54 
55  
56 
57        //如果执行SQL语句错误则返回0
58 
59        if(status!=OCI_SUCCESS)
60 
61               return 0;
62 
63  
64 
65        return 1;
66 
67  }

由于otl_stream默认将语句执行成功立刻提交事务的开关打开，因此在exec()方法中调用OCI接口时，将使用该操作模式。

13.3与开源项目ORAPP的性能对比

ORAPP是一个基于C++语言的封装了OCI函数，对Oracle数据库进行专门访问的类库。表13-2所示的性能对比结果的测试环境为Linux, Oracle10g, OTL流缓冲区大小为1000，可以看出当数据量剧增时OTL的性能明显优越。

表1-2 ORAPP与OTL性能对比

数据量	OTL耗时(s)	ORAPP耗时(s)
1000	0.02	0.02
10000	0.03	0.21
100000	0.19	2.04
300000	0.56	5.77

备注：(1) 测试环境为Linux, Oracle10g, OTL流缓冲区大小为1000

(2) 耗时包括SQL执行时间和记录获取时间的总和

究其原因，是由于OTL存在流缓冲区、流缓冲池等性能保证的机制，对于大数据量的查询OTL可以通过设置缓冲池大小，指定每次底层OCI调用获取的结果行，几倍甚至几十倍的提高性能。ORAPP则将每次获取的记录行数硬编码为1，性能不能保证。