OTL使用指南

 

1 OTL简介

OTL 是 Oracle, Odbcand DB2-CLI Template Library
的缩写,是二个C++编写翻译中操控关全面据库的模板库,它如今差不离支持当前拥有的各类主流数据库,例如Oracle,
MS SQL Server, Sybase, Informix, MySQL, DB二, Interbase /Firebird,
PostgreSQL, SQLite, SAP/DB, 提姆esTen, MS ACCESS等等。

OTL中央直机关接操作Oracle主借使通过Oracle提供的OCI接口进行,操作DB2数据库则是经过CLI接口进行,至于MS的数据库和其余一些数据库,OTL只提供了ODBC的操作办法。当然Oracle和DB二也足以由OTL直接使用ODBC的办法举行控制。

在MS Windows and Unix 平台下,OTL近来帮助的数据库版本重要有:Oracle 七(直接动用 OCI7), Oracle 8 (直接使用 OCI八), Oracle 8i (直接利用OCI八i),
Oracle 九i (直接利用OCI九i), Oracle 10g (直接选择OCI10g), DB二 (直接运用DB2
CLI), ODBC 三.x ,ODBC 贰.伍。近期OTL的前卫版本为四.0,参见http://otl.sourceforge.net/,下载地址http://otl.sourceforge.net/otlv4_h.zip

2 编译OTL

OTL是二个集成库,它涵盖了2个模板流框架(template stream
framework)以及适配OCI七, OCI八, OCI八i, OCI玖i, OCI10g, ODBC 2.五, ODBC 三.x,
DB二 CLI和Informix
CLI的适配器(OTL-adapters)。编写翻译时索要选用相应的宏定义向编译器指明底层数据库API的类型。例如,就算底层使用ORACLE十g的API,则必要选用宏定义”#defineOTL_ORA10G”。

其它,也得以接纳相应的宏定义控制编写翻译器对OTL的编写翻译。
例如,假设需求和ACE库一起编写翻译能够应用宏定义”#defineOTL_ACE”,
假使须要OTL为所分配并处理的字符串以空字符结尾成为C风格字符串则能够动用宏定义”#define
OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE”等。

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

叁 基本选择

OTL的形似采纳手续包蕴:

(一)   使用宏指明底层数据库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()对象,该对象1般为部分的。

(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!\n");
 44 
 45     }
 46 
 47     catch (otl_exception& p)
 48 
 49     {   // intercept OTL exceptions
 50 
 51         printf("Connect Error: (%s) (%s) (%s)\n", 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!\n");
 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)\n", 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!\n", szData1);
262 
263     }
264 
265     catch (otl_exception& p)
266 
267     { // intercept OTL exceptions
268 
269         printf("PROCEDURE Error: (%s) (%s) (%s)\n", 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)\n",
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)\n", 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!\n");
436 
437     }
438 
439     catch (otl_exception& p)
440 
441     { // intercept OTL exceptions
442 
443         printf("INSERT Error: (%s) (%s) (%s)\n", 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 }

 

四 OTL流的定义

OTL设计者认为,任何SQL语句、PL/SQL块或存款和储蓄进程调用都被输入和出口变量特征化。例如:

l       一个SELECT语句在其WHERE子句中具备标量的输入变量,而在其SELECT子句则定义了出口的列,假诺SELECT语句再次回到的是多行记录则输出列是个向量参数。

l       三个INSECR-VT和UPDATE语句需求将数据写入表中,它们有着输入参数。其余,2个DELETE语句由于须求指明删除记录的档次,同样有所输入。工业强度的数据库服务器日常也扶助批量操作,例如批量的查询、更新、删除和插入,由此INSE卡宴T/UPDATE/DELETE语句的参数在批量操作的情景下也大概是向量。

l       贰个储存进程或许带有输入和(或)输出参数。平日存款和储蓄进程的参数是标量,不过也有特例,例如重回的是援引游标(Oracle)或许记录集(MS
SQL SEHighlanderVE冠道只怕Sybase)。

l       一个PL/SQL块或者包括输入和(或)输出参数,那个参数也许是标量也也许是向量。

 

图4-1 OTL的流

为此,任何的SQL或许其程序上的扩大在相互进程中都能够如图4-一所示看作拥有输入和输出的黑盒。OTL通过将数据流和SQL的概念联合起来,用otl_stream类表明那种肤浅。

出于SQL语句也许以批量的艺术实施,otl_stream是叁个缓冲流。它具备多少个单身的缓冲区:输入和输出。输入缓冲区由具有集中到壹道的输入参数组成,输出缓冲区则由拥有集中到1块儿的输出变量组成。

OTL流和C++的缓冲流很相似。二个SQL语句或存款和储蓄进度调用被看作三个平凡的缓冲流被打开。OTL流的操作逻辑和C++流操作逻辑基本相同,但是OTL流的输出和出口缓冲区只怕重叠。

OTL流拥有flush()方法在输入缓冲区写满的时候将其自行刷新,也蕴藏一连串的<<和>>运算符来读和写差别数据类型的靶子。它最要紧的优点是为其它类型的SQL语句和储存进程调用提供了统一的接口。应用开发者能够由此熟习少量的语法和函数名称像使用C++流1样来利用OTL流。

在OTL流的内部装有三个微型的解析器来分析所注明的绑定变量以及绑定变量的数据类型。因而,免去了动用特殊的绑定函数来绑定已扬言的C/C++主机变量(hostvariables)。由于全部必须的缓冲区在OTL流中会自动成立,因而OTL仅仅需求被打开来进展读和写相应的数值。

OTL流接口需求利用OTL十分。OTL流操作都能容许抛掷otl_exception十分。因而为了阻拦很是并阻挠程序万分终止,必须利用try/catch块来包裹OTL流的采纳代码。

OTL流的兑现otl_stream具有较高的自动化效率,当OTL流的享有的输入变量被定义好(也正是输入缓冲区被填满),它会触发OTL流中的黑盒来执行。在黑盒执行的历程中输出缓冲区被填充。在履行到位后,输出缓冲区中的值能够从OTL流中被读取。假使实行的是贰个SELECT语句并且重临多于输出缓冲区大小的行,那么在出口缓冲区的始末被读取后,OTL会自动读取下一堆行记录到输出缓冲区。

五 首要类及格局求证

伍-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

陆.壹 SQL的变量绑定

OTL拥有一个袖珍的解析器,负责在流的内部动态的为SQL语句、PL/SQL
块或存款和储蓄进度调用中扬言的绑定变量分配空间。OTL将Oracle古板的行任务名符号作为占位符的变量绑定机制举办了扩张,增添了数据类型表达,例如:

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

OTL占位符中的支撑的数据类型如表陆-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_ORA8IOTL_ORA9IOTL_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

万1SQL语句 、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是OTL肆.0的三个internalclass。OTL固然并不引进应用低级其他类,可是otl_cursor的direct_exec()方法是3个特例。该方法的重返值只怕为:

l       -1,
如果otl_exception十分被禁止接纳(第四个参数被设置成otl_exception::disabled),并且底层的API再次回到了错误。

l       >=0,
假若成功实践SQL命令,在进行INSERAV4T、DELETE或UPDATE语句时实际重临的是已处理行数。

7迭代器

7.壹 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.二 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>是1种输入迭代器(InputIterator),它从otl_stream中读出将品种为T的目的,此外叁个模板参数Distance为otl_input_iterator的指针偏移类型。当流的末段到达时,otl_input_iterator会得到2个分化平日的值即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, "\n"));
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, "\n"));
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()五个章程(见肆.2小节),它们得以联手使用以创立类似连接缓冲池机制。使用session_begin()比一直运用rlogon()快差不离50到十0倍。

以下是共同使用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

八.二 OTL流缓冲池

ACCESS,流缓冲池是OTL的三个新机制,当otl_stream实例关闭时,otl_stream变量实例将被封存到流缓冲池中,使得程序可以一连起用。otl_stream的每一次实例化将触发数据库后台对OTL流中SQL语句的重新分析,那是相对耗费时间的操作,而流缓冲池机制将回落那上头的耗费时间并简化编码技术。

缓冲池中的流能够是壹对变量也得以是分配在堆上的动态变量。流之间的相似型和流缓冲区大小以及SQL语句文本有关,拥有同等缓冲区大小和SQL语句文本的流将保存在缓冲池中1样的桶中如图8-一所示。由于流缓冲池的最底层使用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

 

玖 操作大型对象

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

九.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字符数量而不是字节数。

玖.二 大型对象的读写

    大型对象的读写通过类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中的大型对象。

以下是操作大型对象的言传身教代码。包涵INSE帕杰罗T、UPDATE、SELECT操作。注目的在于使用INSEKoleosT和UPDATE时代风尚缓冲区的尺寸必须为一,此外必须将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或UTF八的字符串操作达成。

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内部使用UTF八字符串。以下是应用UTF字符串的示范代码。

示范代码中选择unsigned char类型数组存放UTF八字符串,
在运用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/八i/九i/十g提供了类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

1贰.二 SELECT中的数据类型映射覆写

otl_stream在实践SELECT语句重临结果列时,具有如下表1二-壹所示的数据库数据类型(Database
datatype)到暗中认可数据类型(Default datatype)的投射。

有个别景况下往往须要对那种暗中同意映射举办覆写,即将数据库数据类型映射为非私下认可的数据类型。例如当读取超过12位的数字时,将其映射为字符串类型往往更便宜。为此,otl_stream提供了set_colunm_type()方法(参见4.一小节)举行数据类型覆写。

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

1二.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.四 获取已处理行数(Rows Processed Count)

otl_stream提供了get_rpc()方法(参见4.一小节)获取已处理行数。别的,类otl_cursor的direct_exec()方法假诺处理成功也回到已处理行数。

已处理行数和INSE奥迪Q3T、UPDATE以及DELETE语句关于。对于INSE卡宴T语句而言,已处理行数也许低于等于流的缓冲区大小。对于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重载了<<,
<<=和>>运算符(参见肆.二小节),用于简化编制程序操作。个中操作符<<发送字符串到otl_connect对象。如果该otl_connect对象还未有连接到数据库则字符串为”userid/passwd@db”格式的接连字符串,它使得otl_connect对象能够立刻连接数据库。假如该otl_connect对象已经接二连三到数据库则字符串被当作为静态SQL语句立刻实施。

操作符<<=发送字符串到otl_connect对象。otl_connect对象将保存该字符串并被下1个>>操作符使用。该字符串是1个拥有占位符并且能够发送到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

1二.陆 手工业刷新otl_stream缓冲区

otl_stream提供了法子set_flush()方法(见4.1小节)设置auto_flush标志。该标志仅仅控制析构函数中由于出口缓冲区数据变脏引起的刷新,并无法控制缓冲区填满引起自动刷新。auto_flush标志暗许为true即实行刷新。

提供set_flush()方法控制otl_stream析构函数中的刷新操作和C++的特别机制有关。C++格外机制退出职能域时,局地对象会出于堆栈回退发生析构。固然在析构函数中又生出1二分将造成std::terminate()被调用,从而使程序中止。

otl_stream析构函数中的输出缓冲区刷新在1些意况下极有希望造成那种级联非常爆发,由此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

1二.七 忽略INSELacrosseT操作时的重复键卓殊

底层操作为Oracle
API时,otl_stream提供了相当版本的flush()方法(参见四.1小节),该办法能够设置刷新的开头行以及忽略发生错误时抛出的otl_exception万分继续刷新。在开始展览INSEEscortT操作有时候供给忽略重复键值引起的谬误继续处理,那时可以使用该flush()方法开始展览处理。

以下是忽视INSEGL450T操作时的重复键非常示例代码。

  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

1二.八 使用模板otl_value<T>创设数量容器

otl_value<T>是1个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,在该值被写入INSE卡宴T语句后,otl_value<int>容器中的NULL提醒器域依然保留该NULL值并且NULL值也从SELECT传递到了INSE锐界T。

模板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

1二.玖 使用OTL流的读迭代器遍历流再次来到的Reference Cursor

otl_stream的构造函数和open()方法(参见肆.壹小节)中参数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 最棒实践

一3.一流缓冲区大小的设置

OTL流的品质主要被缓冲区大小3个参数控制,在创制使用OTL流时务必经过构造函数或open()函数的第叁个参数arr_size内定,合理设置流缓冲区大小对数据库访问的习性优化特别重要。

与INSE安德拉T/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接口调用,因而缓冲区大小决定了从日前岗位处开头1遍提取的记录数。

对此查询数据量较大的选用,缓冲区大小的合理性设置往往是性质优化的重中之重。表一三-一为查询一千0条记下时的缓冲区大小对结果行获得的熏陶。数据库为Oracle拾g,
操作系统为HP-UNIX。

一三-1 查询一千0条记下时缓冲区大小不一设置的照应耗费时间

缓存区大小

耗时(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

备考:(一) 测试环境为HP, Oracle10g

(二) 耗费时间仅指SQL执行成功后,记录的得到时间

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

在意对于SELECT语句来说,缓冲区的轻重并不影响其执行时机。当otl_stream以SELECT语句实例化,并设定好缓冲区大小后,1旦拥有宣称的绑定变量使用<<操作符被绑定完毕,该SELECT语句将被OTL流立即执行,与安装的缓存区具体大小并从未涉嫌。

与此相反,对于INSE中华VT/DELETE/UPDATE语句,缓冲区大大小小的设置将震慑其实行时机。当缓冲区被SQL填满时,OTL流将自动刷新缓冲区,立时批量推行缓冲区中有所的SQL。由此,当执行语句为INSE奥迪Q伍T/DELETE/UPDATE时,也理应依照须要合理设置缓冲区大小。

一三.二批量操作注意的难题

当使用OTL的SQL变量绑定实行数据的批量操作时,应该专注otl_stream缓冲区大小的合理性设置(参见一三.1小节)以及OTL暗中认可的言语执行成功后旋即交付业务。

对于批量操作,OTL暗中同意的言语执行成功后随即交给业务的操作方法频仍不实用。例如对于批量翻新10000条记下,缓冲区大大小小为一千的事态,当缓冲区填满时更新操作被实践,执行成功后当前事务被当下交给,即使在之后的拍卖中出现谬误,根本未曾艺术通过回滚事务撤销在此以前的更新。制止语句执行后立时交给业务的不二秘籍请参见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接口时,将采取该操作形式。

一三.三与开源项目O奥德赛应用软件的属性相比较

O猎豹CS6应用程式是多个基于C++语言的包裹了OCI函数,对Oracle数据库进行专门访问的类库。表一三-二所示的性质比较结果的测试环境为Linux,
Oracle拾g, OTL流缓冲区大小为一千,
能够观察当数据量剧增时OTL的天性显然优于。

表1-2  O奇骏应用程式与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

       备注:(一) 测试环境为Linux, Oracle拾g, OTL流缓冲区大小为一千

                   (二) 耗时包含SQL执行时间和著录获取时间的总数

究其原因,是由于OTL存在流缓冲区、流缓冲池等性情保险的体制,对于大数据量的查询OTL能够经过设置缓冲池大小,钦定每一次底层OCI调用获取的结果行,数倍甚至几10倍的增高品质。OBMWX五APP则将每趟获得的记录行数硬编码为1,品质否担保。

相关文章