Oracle Call Interface Template Library 1.0.5 (OTL), Pro*OTL / Pre-Pro*C preprocessor 1.0.0 (PPC)

Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com

Table of Contents

• 1. Introduction
• 2. Getting started with OTL
  • 2.1. Examples
    • 2.1.1. Example 1 (with ordinary, non-template host variables)
    • 2.1.2. Example 2 (with template instantiated host variables)
    • 2.1.3. Example 3 (with the extended "parse" function)
    • 2.1.4. Example 4 (with Oracle LONG columns)
    • 2.1.5. Example 5 (with the otl_select_stream class)
    • 2.1.6. Example 6 (with the otl_out_stream class)
    • 2.1.7. Example 7 (with the otl_inout_stream class)
    • 2.1.8. Example 8 (with the otl_stream class)
    • 2.1.9. Example 9 (with PL/SQL block)
    • 2.1.10. Example 10 (with printf/scanf functions)
    • 2.1.11. Example 11 (with Prosto*C)
  • 2.2. Comparison with the other C++/database libraries
    • 2.2.1. OTL vs. SQLObjects (by Intelligent Objects)
    • 2.2.2. OTL vs. DBTools.h++ (by Rogue Wave)
  • 3. Library structure
    • 3.1. Host variable and array template classes
      • 3.1.1. Specialized host variable classes
      • 3.1.2. Specialized host array classes
    • 3.2. Oracle Call Interface "wrapper"
      • Class otl_exception
      • Class otl_object
      • Class otl_connect
      • Class otl_column_desc
      • Class otl_cursor
      • Class otl_select_cursor
      • Class otl_dynamic_variable
      • Class otl_err_info
    • 3.3. OTL stream interface
      • Class otl_select_stream
      • Class otl_out_stream
      • Class otl_inout_stream
      • Class otl_stream
      • Stream bind variable declarations
    • 3.4. Prosto*C
  • 4. Pro*OTL / Pre-Pro*C preprocessor (PPC)
    • 4.1. Getting started with PPC
      • Example in Pro*C
      • Example in C++
    • 4.2. Directives
      • 4.2.1. #sql-select
      • 4.2.2. #sql-out-stm
      • 4.2.3. #sql-plsql
      • 4.2.4. #sql-init-module
      • 4.2.5. #sql-init-main
      • 4.2.6. #sql-str-type
    • 4.3. Command line parameters
  • 5. Acknowledgements
  • 6. Bibliography
  • Appendix A. OTL class hierarchy
  • Appendix B. Error message list
  • Appendix C. OTL source code (otl.h)
  • Appendix D. Pro*OTL / Pre-Pro*C preprocessor's source code (ppc.C or ppc.cpp)
  • Appendix E. How to install the OTL library and Pro*OTL/Pre-Pro*C preprocessor
  • Appendix F. Modules, generated by PPC for the example from Chapter 4.
    • Pro*C module (ppc_test.pc)
    • C++ module (ppc_test.C)
    • Interface header file (ppc_test.h)
    • Command line for the example from Chapter 4.

  1. Introduction

  This document provides information on the Oracle Call Interface Template Library (OTL). OTL is a new kind of C++ libraries, similar to the Standard Template Library. This kind of libraries is easy to use, since the user needs only to include C++ header files which contain template classes and functions. There is no need to link additional object libraries into C++ applications. The code, instantiated from template classes and inline functions, is efficient and reliable in terms of runtime performance and C++ strict type checking.

  OTL comprises of a set of template classes. The templates allow the user to create scalar host variables and host arrays, then dynamically bind the variables and arrays with SQL statements or PL/SQL blocks. OTL has a number of non-template classes which encapsulate the Oracle Call Interface (OCI) functions and provide transparent programming interface to them.

  OTL provides an optional exception handling mechanism, given in the form of the otl_exception class. This mechanism takes advantage of C++ exceptions compared to coding database applications in plain C. The user does not need to check out return codes after each function call. The code, instantiated from the OTL templates and inline functions, is much nicer and cleaner in comparison with the code generated by the Pro*C precompiler.

  In OTL, a concept of SQL streams is introduced. The SQL programming interface becomes unified and homogeneous.

  OTL has a simplified set of functions, called Prosto*C. It provides basic functions, such as connect/disconnect, printf/scanf, commit/rollback, etc. The word "Prosto*C" is originated in the author's native language: "prosto" means "simple". The idea here is to simplify the interface as much as possible, without losing the functionality.

  OTL is a database access function library. Also, this document describes the Pro*OTL / Pre-Pro*C preprocessor (PPC). PPC is a preprocessor which takes a directive file on input and generates OTL and Pro*C code on output. Besides, PPC produces a header file with prototypes of the generated functions and data structures, used by the functions. The generated functions are the "executable" form of the directives. The directives are more declarative and much more compact than the corresponding functions. The generated header file can be included in both C++ and plain C modules.

  OTL, as a library, and PPC, as a preprocessor, are intended to boost productivity of Oracle database developers who work with C++ as well as Pro*C. PPC is a pathway from traditional Pro*C to more advanced C++ database APIs.

  OTL and PPC compile with the following 32-bit C++ compilers:

  • IBM AIX, C++ (xlC), 1.x and higher
  • SunOS/Solaris, Sun C++, 4.x
  • Unix, GNU C++ (g++), 2.7.x
  • Windows 95, NT, Visual C++, 4.x, 32-bit

  The author is hoping to get feedback from potential users of OTL and that the OTL source code is clean enough to be ported across the 32-bit platforms, different from the mentioned above.

  Besides, the author's goal is to eventually find a sponsor to make this product commercial, in order to enhance, maintaion and support it on the regular basis.

  Despite the common opinion that Freeware products are not that good and badly supported, the author believes that OTL & PPC have production quality and can be used successfully.

  The OTL source code resides in Appendix C, PPC -- in Appendix D. The whole page may be downloaded, in order to get the source code. Examples may be clipped from the text, copied to separate files and used. Comments and questions would be appreciated very much. Email to skuchin@gmail.comskuchin@gmail.com.

  2. Getting started with OTL

  2.1. Examples

  Let's assume you want to create a table, fill it out with a hundred records and then select some of them. This may be accomplished by the following code.

  2.1.1. Example 1 (with ordinary, non-template host variables)

  Source code

  <a name="sec211"> #include &lt;iostream&gt; using namespace std; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int STR_SIZE=31; // string size void insert() // insert rows into table { float f1[BUF_SIZE]; // float host array f1 without indicators char f2[BUF_SIZE][STR_SIZE]; // string host array f2 without indicators otl_cursor o(db); // create cursor int n=0; o.parse("insert into test_tab values(:f1,:f2)"); // parse sql statement o.bind_float(":f1",f1); // bind f1 o.bind_cstring(":f2",(char*)f2,STR_SIZE); // bind f2 for(int i=1;i&lt;=100;++i){ ++n; f1[n-1]=i; // fill out host array f1 sprintf(f2[n-1],"Name%d",i); // fill out host array f2 if(n==BUF_SIZE){ // execute the sql statement when buffer gets // full o.exec(n); n=0; } } if(n&gt;0) o.exec(n); db.commit(); // commit transaction } /* insert */ void select() { float f1[BUF_SIZE]; // float host array f1 without indicators char f2[BUF_SIZE][STR_SIZE]; // string host array f2 without indicators int f; // host variable f without indicator otl_select_cursor i(db,BUF_SIZE); // create specialized select cursor i.parse("select * from test_tab where f1&gt;=:f and f1&lt;=:f*2"); // parse select statement i.bind_float(1,f1); // bind f1 to column 1 i.bind_cstring(2,(char*)f2,STR_SIZE); // bind f2 to column 2 i.bind_int(":f",&amp;f); // bind f f=8; // assign 8 to f while(i.next()){ // while not end-of-data int k=i.cur_row; // index of current row in host arrays cout&lt;&lt;"f1="&lt;&lt;f1[k]&lt;&lt;", f2="&lt;&lt;f2[k]&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); //disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec211"> 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 </a>

  2.1.2. Example 2 (with template instantiated host variables)

  Source code

  <a name="sec212"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int STR_SIZE=31; // string size void insert() // insert rows into table { otl_float_array&lt;BUF_SIZE&gt; f1; // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2; // C-string host array f2 otl_cursor o(db); // create cursor int n=0; o.parse("insert into test_tab values(:f1,:f2)"); // parse sql statement o.bind(":f1",f1); // bind f1 o.bind(":f2",f2); // bind f2 for(int i=1;i&lt;=100;++i){ ++n; f1.v[n-1]=i; // fill out host array f1 sprintf(f2.v[n-1],"Name%d",i); // fill out host array f2 if(n==BUF_SIZE){ // execute sql statement when buffer gets // full o.exec(n); n=0; } } if(n&gt;0) o.exec(n); db.commit(); // commit transaction } /* insert */ void select() { otl_float_array&lt;BUF_SIZE&gt; f1; // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2; // string host array f2 otl_int f; // host variable f otl_select_cursor i(db,BUF_SIZE); // create specialized select cursor i.parse("select * from test_tab where f1&gt;=:f and f1&lt;=:f*2"); // parse select statement i.bind(1,f1); // bind f1 to column 1 i.bind(2,f2); // bind f2 to column 2 i.bind(":f",f); // bind f. f is input variable f.v=8; // assign 8 to f while(i.next()){ // while not end-of-data int k=i.cur_row; // index of current row in host arrays cout&lt;&lt;"f1="&lt;&lt;f1.v[k]&lt;&lt;", f2="&lt;&lt;f2.v[k]&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec212"> 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 </a>

  2.1.3. Example 3 (with the extended "parse" function)

  Source code

  <a name="sec213"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int STR_SIZE=31; // string size void insert() // insert rows into table { otl_float_array&lt;BUF_SIZE&gt; f1(":f1"); // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2(":f2"); // C-string host array f2 otl_cursor o(db); // create cursor int n=0; o.eparse("insert into test_tab values(:f1,:f2)",&amp;f1,&amp;f2,0); // parse sql statement and bind variables f1 and f2 with the // statement. the variable list is NULL terminated for(int i=1;i&lt;=100;++i){ ++n; f1.v[n-1]=i; // fill out host array f1 sprintf(f2.v[n-1],"Name%d",i); // fill out host array f2 if(n==BUF_SIZE){ // execute sql statement when buffer gets // full o.exec(n); n=0; } } if(n&gt;0) o.exec(n); db.commit(); // commit transaction } /* insert */ void select() { otl_float_array&lt;BUF_SIZE&gt; f1; // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2; // string host array f2 otl_int f(":f"); // host variable f otl_select_cursor i(db,BUF_SIZE); // create specialized select cursor i.eparse("select * from test_tab where f1&gt;=:f and f1&lt;=:f*2",&amp;f1,&amp;f2,&amp;f,0); // parse select statement, bind input variable f and output columns // f1, f2 with the statement. f1 is treated as column 1 in the // select list, f2 -- as column 2. The variable list is NULL terminated f.v=8; // assign 8 to f while(i.next()){ // while not end-of-data int k=i.cur_row; // index of current row in host arrays cout&lt;&lt;"f1="&lt;&lt;f1.v[k]&lt;&lt;", f2="&lt;&lt;f2.v[k]&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec213"> 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 </a>

  2.1.4. Example 4 (with Oracle LONG columns)

  Source code

  <a name="sec214"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int LONG_STR_SIZE=2000; // LONG string size void insert() // insert rows into table { otl_float f1(":f1"); // float host variable f1 otl_long_varchar&lt;LONG_STR_SIZE&gt; f2(":f2"); // long varchar host variable f2 otl_cursor o(db); // create cursor o.eparse("insert into test_tab values(:f1,:f2)",&amp;f1,&amp;f2,0); // parse sql statement and bind variables f1 and f2 with the // statement. the variable list is NULL terminated for(int i=1;i&lt;=9;++i){ f1.v=i; // assign host variable f1 f2.set_len(5); // set the long_varchar string length f2[0]='N'; f2[1]='a'; f2[2]='m'; f2[3]='e'; f2[4]='0'+i; o.exec(); db.commit(); // commit transaction } } /* insert */ void select() { otl_float f1; // float f1 otl_long_varchar&lt;LONG_STR_SIZE&gt; f2; otl_int f(":f"); // host variable f otl_select_cursor i(db); // create specialized select cursor i.eparse("select * from test_tab where f1&gt;=:f and f1&lt;=:f*2",&amp;f1,&amp;f2,&amp;f,0); // parse select statement, bind input variable f and output columns // f1, f2 with the statement. f1 is treated as column 1 in the // select list, f2 -- as column 2. The variable list is NULL terminated f.v=4; // assign 4 to f while(i.next()){ // while not end-of-data cout&lt;&lt;"f1="&lt;&lt;f1.v&lt;&lt;", f2="; for(int j=0;j&lt;f2.len();++j) cout&lt;&lt;f2[j]; // print out the long_varchar string cout&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 long)" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec214"> f1=4, f2=Name4 f1=5, f2=Name5 f1=6, f2=Name6 f1=7, f2=Name7 f1=8, f2=Name8 </a>

  2.1.5. Example 5 (with the otl_select_stream class)

  Source code

  <a name="sec215"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int STR_SIZE=31; // string size void insert() // insert rows into table { otl_float_array&lt;BUF_SIZE&gt; f1(":f1"); // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2(":f2"); // C-string host array f2 otl_cursor o(db); // create cursor int n=0; o.eparse("insert into test_tab values(:f1,:f2)",&amp;f1,&amp;f2,0); // parse sql statement and bind variables f1 and f2 with the // statement. the variable list is NULL terminated for(int i=1;i&lt;=100;++i){ ++n; f1.v[n-1]=i; // fill out host array f1 sprintf(f2.v[n-1],"Name%d",i); // fill out host array f2 if(n==BUF_SIZE){ // execute sql statement when buffer gets // full o.exec(n); n=0; } } if(n&gt;0) o.exec(n); db.commit(); // commit transaction } /* insert */ void select() { otl_int f(":f"); // host variable f otl_select_stream i(db, "select * from test_tab where f1&gt;=:f and f1&lt;=:f*2", BUF_SIZE, // size of the buffer attached to the // stream &amp;f, // input variable :f2 0 // NULL terminator of the variable list ); // create select stream int f1; char f2[STR_SIZE]; f.v=8; // assign 8 to f i.rewind(); // rewind the stream: re-execute the statement and fetch // first portion of rows. while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } f.v=4; // assign 4 to f i.rewind(); // rewind the stream: re-execute the statement and fetch // first portion of rows. while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec215"> 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 </a>

  2.1.6. Example 6 (with the otl_out_stream class)

  Source code

  <a name="sec216"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object const int BUF_SIZE=50; // host array size const int STR_SIZE=31; // string size void insert() // insert rows into table { otl_float_array&lt;BUF_SIZE&gt; f1(":f1"); // float host array f1 otl_cstring_array&lt;BUF_SIZE,STR_SIZE&gt; f2(":f2"); // C-string host array f2 otl_out_stream o(db, // connect object "insert into test_tab values(:f1,:f2)", // SQL statement &amp;f1, // bind variable :f1 &amp;f2, // bind variable :f2 0 // end of variable list ); char tmp[31]; for(int i=1;i&lt;=100;++i){ sprintf(tmp,"Name%d",i); o&lt;&lt;(float)i&lt;&lt;tmp; } } /* insert */ void select() { otl_int f(":f"); // host variable f otl_select_stream i(db, "select * from test_tab where f1&gt;=:f and f1&lt;=:f*2", BUF_SIZE, // size of the buffer attached to the // stream &amp;f, // input variable :f2 0 // NULL terminator of the variable list ); // create select stream int f1; char f2[STR_SIZE]; f.v=8; // assign 8 to f i.rewind(); // rewind the stream: re-execute the statement and fetch // first portion of rows. while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } f.v=4; // assign 4 to f i.rewind(); // rewind the stream: re-execute the statement and fetch // first portion of rows. while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec216"> 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 </a>

  2.1.7. Example 7 (with the otl_inout_stream class)

  Source code

  <a name="sec217"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object void insert() // insert rows into table { otl_inout_stream o(50, // buffer size "insert into test_tab values(:f1&lt;float&gt;,:f2&lt;char[31]&gt;)", // SQL statement db // connect object ); char tmp[32]; for(int i=1;i&lt;=100;++i){ sprintf(tmp,"Name%d",i); o&lt;&lt;(float)i&lt;&lt;tmp; } } /* insert */ void select() { otl_select_stream i(50, // buffer size "select * from test_tab where f1&gt;=:f&lt;int&gt; and f1&lt;=:f*2", // SELECT statement db // connect object ); // create select stream int f1; char f2[31]; i&lt;&lt;8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } i&lt;&lt;4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec217"> 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 </a>

  2.1.8. Example 8 (with the otl_stream class)

  Source code

  <a name="sec218"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object void insert() // insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1&lt;float&gt;,:f2&lt;char[31]&gt;)", // SQL statement db // connect object ); char tmp[32]; for(int i=1;i&lt;=100;++i){ sprintf(tmp,"Name%d",i); o&lt;&lt;(float)i&lt;&lt;tmp; } } /* insert */ void select() { otl_stream i(50, // buffer size "select * from test_tab where f1&gt;=:f&lt;int&gt; and f1&lt;=:f*2", // SELECT statement db // connect object ); // create select stream int f1; char f2[31]; i&lt;&lt;8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } i&lt;&lt;4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i&gt;&gt;f1&gt;&gt;f2; cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec218"> 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 </a>

  2.1.9. Example 9 (with PL/SQL block)

  Source code

  <a name="sec219"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object void plsql() // invoking PL/SQL block { otl_stream o(5, // buffer size "begin " " :A&lt;int,inout&gt; := :A+1; " " :B&lt;char[31],out&gt; := :C&lt;char[31],in&gt;; " "end;", // PL/SQL block db // connect object ); o&lt;&lt;1&lt;&lt;"Test String1"; // assigning :A = 1, :C = "Test String1" o&lt;&lt;2&lt;&lt;"Test String2"; // assigning :A = 2, :C = "Test String2" o&lt;&lt;3&lt;&lt;"Test String3"; // assigning :A = 3, :C = "Test String3" o.flush(); // executing PL/SQL block 3 times int a; char b[32]; while(!o.eof()){ // not end-of-data o&gt;&gt;a&gt;&gt;b; cout&lt;&lt;"A="&lt;&lt;a&lt;&lt;", B="&lt;&lt;b&lt;&lt;endl; } } /* plsql */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle plsql(); // invoking PL/SQL block } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec219"> A=2, B=Test String1 A=3, B=Test String2 A=4, B=Test String3 </a>

  2.1.10. Example 10 (with printf/scanf functions)

  Source code

  <a name="sec2110"> #include &lt;iostream.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object void insert() // insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1&lt;int&gt;,:f2&lt;char[31]&gt;)", // SQL statement db // connect object ); char tmp[32]; for(int i=1;i&lt;=100;++i){ sprintf(tmp,"Name%d",i); o.printf("%d %s",i,tmp); // write one row into stream } } /* insert */ void select() { otl_stream i(50, // buffer size "select * from test_tab where f1&gt;=:f&lt;int&gt; and f1&lt;=:f*2", // SELECT statement db // connect object ); // create select stream int f1; char f2[31]; i&lt;&lt;8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i.scanf("%d %s",&amp;f1,f2); // read one row from stream cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } i&lt;&lt;4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i.scanf("%d %s",&amp;f1,f2); // read one row from stream cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } } /* select */ int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table test_tab", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table } catch(otl_exception&amp; p){ // intercept OTL exceptions cerr&lt;&lt;p.msg&lt;&lt;endl; // print out error message } db.logoff(); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec2110"> 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 </a>

  2.1.11. Example 11 (with Prosto*C)

  Source code

  <a name="sec2111"> #include &lt;iostream.h&gt; #include &lt;stdlib.h&gt; #include &lt;stdio.h&gt; #include &lt;otl.h&gt; otl_connect* db=0; // pointer to connect object void my_handler(char* msg, int code) // my error handler { cout&lt;&lt;msg&lt;&lt;endl; if(db)db-&gt;rollback(); // on error, roll back transaction if already // connected exit(1); // exit from the program } void insert() // insert rows into table { otl_stream* o=otl_stream_open // open OTL stream ( db, // connect object "insert into test_tab values(:f1&lt;int&gt;,:f2&lt;char[31]&gt;)", // SQL statement 50 // buffer size ); char tmp[32]; for(int i=1;i&lt;=100;++i){ sprintf(tmp,"Name%d",i); otl_printf(o,"%d %s",i,tmp); // write one row into the stream } otl_stream_close(o); // Close OTL stream } /* insert */ void select() { otl_stream* i=otl_stream_open // Open OTL stream ( db, // connect object "select * from test_tab where f1&gt;=:f&lt;int&gt; and f1&lt;=:f*2", // SELECT statement 50 // buffer size ); // create select stream int f1; char f2[31]; otl_printf(i,"%d",8); // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!otl_eof(i)){ // while not end-of-data otl_scanf(i,"%d %s",&amp;f1,f2); // fetch a row from the stream cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } otl_printf(i,"%d",4); // assigning :f = 4 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!otl_eof(i)){ // while not end-of-data otl_scanf(i,"%d %s",&amp;f1,f2); // fetch a row from the stream cout&lt;&lt;"f1="&lt;&lt;f1&lt;&lt;", f2="&lt;&lt;f2&lt;&lt;endl; } otl_stream_close(i); // Close OTL stream } /* select */ int main() { // connect to Oracle db=otl_logon("scott/tiger", my_handler // attaching error handler to the connect object ); otl_exec( db, "drop table test_tab", 1 // ignore error ); // drop table otl_exec( db, "create table test_tab(f1 number, f2 varchar2(30))" ); // create table insert(); // insert records into table select(); // select records from table otl_logoff(db); // disconnect from Oracle return 0; } /* main */ </a>

  Output

  <a name="sec2111"> 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 </a>

  2.2. Comparison with the other C++/database libraries

  In this section, OTL is compared with the other C++/database libraries, namely:

  • SQLObjects (by Intelligent Objects)
  • DBTools.h++ (by Rogue Wave)

  SQLObjects and DBTools.h++ are both commercial, multi-platform, multi-database C++ database access libraries. It is hard to perform good comparison between them and OTL, since they provide similar functionality but belong to the different category. Nevertheless, OTL has advantage of being well suited for a range of Oracle applications and platforms. It is portable and easy-to-maintain within this range.

  Multi-database C++ libraries lose the sense of "closeness" with the database. They, most often, do not have so called "native access" to the database. All their multi-database functionality is based upon the monster called ODBC. Oracle has many advantages compared to the other databases, such as Sybase, Informix, etc.

  It is not such a bad idea to harness most of the power Oracle provides:

  • PL/SQL functions/procedures/packages
  • Other Oracle SQL extentions
  • Oracle Array Interface

  Typical problem of accessing Oracle via ODBC is that no ODBC driver provides transparent access to PL/SQL, especially with OUT or IN OUT parameters.

  Often, it is critical to have fast interface. If the interface is fast, then, most probably, it is not portable accross database servers from different vendors. If the interface is portable and unified, then it is awfully slow.

  Considering the fact that a big segment of the database market is covered by Oracle, it makes a lot of sense to develop an interface unified for Oracle across most of the platforms on which Oracle runs.

  Let's consider a typical C++ interface to a database. It provides a set of functions like:

  • Connect/Disconnect
  • Open/Close cursor
  • Parse/Execute SQL statement
  • Bind host variables
  • Fetch rows from a SELECT statement
  • Get and process database errors

  Usually, such a layer of code is called access library. Some vendors go far beyond that and provide factories, based upon such access libraries. The factory is a GUI based front end which generates the access library calls. Does it really improve the developer's productivity? Sometimes, yes.

  In contrast, OTL provides a multilayer class hierarchy and allows the database programmer to decide which layer is more appropriate for each case. The main advantage of OTL is that it provides the code layers both as low as the Oracle Call Interface and as high as abstract and unified SQL stream interface.

  Therefore, OTL, in a sense, fills out the gap between the access layer of code and the factory. The database programmer does not have to learn tons of new front ends. He has to deals only with the same C++ code and the database.

  It is not hard to imagine that it is rather easy to build up a factory on top of OTL, as access library. The code, generated by the factory would occupy less memory and would be more readable than the code, generated into the calls of a low level access library. The Pro*OTL / Pre-Pro*C preprocessor may be the first step toward such a factory.

  2.2.1. OTL vs. SQLObjects (by Intelligent Objects)

  Sample Program in SQLObjects

  <a name="sec221"> Step 1 char szName[31]=""; Step 2 char szAddress[31]=""; Step 3 SQLObject *pDb=new SQLSybase(); // Instantiate SQLObject object Step 4 pDb-&gt;sqlLogin(); // Log into the database Step 5 pDb-&gt;sqlExec("SELECT name, address FROM customer"); // execute the SQL command Step 6 pDb-&gt;sqlBind(1,bindZSTRING,31,szName); Step 7 pDb-&gt;sqlBind(2,bindZSTRING,31,szAddress); // Binds the host variables to the column results Step 8 while(pDb-&gt;sqlFetch() == TRUE) { cout &lt;&lt; szName &lt;&lt; " " &lt;&lt; szAddress &lt;&lt; "\n"; } // Fetches the records from the data source Step 9 pDb-&gt;sqlDisconnect(); // Disconnects from the data source Step 10 delete pDb; // Deletes the object </a>

  The Same Sample Program in OTL streams

  "As high as abstract and unified SQL stream interface":

  <a name="sec221"> Step 1 char szName[31]; Step 2 char szAddress[31]; Step 3 otl_stream s(20, // stream buffer size (in rows) "SELECT name, address FROM customer", // SELECT statement db // connect object ; Step 4,5,6,7 // no explicit binding, no explicit execution Step 8 while(!s.eof()) { s &gt;&gt; szName &gt;&gt; szAddress; // fetch one row cout &lt;&lt; szName &lt;&lt; " " &lt;&lt; szAddress &lt;&lt; endl; } // Fetches the records from the data source Step 9, 10 // Automatic destructor will do the job </a>

  The Same Sample Program in OTL lower code layer

  "As low as the Oracle Call Interface interface":

  <a name="sec221"> Step 1 char szName[31]; Step 2 char szAddress[31]; Step 3 otl_select_cursor s(db); // db -- connect object // Instantiate Cursor object Step 4 // Everything is done in the constructor Step 5 s.parse("SELECT name, address FROM customer"); // Parse the SQL command Step 6 s.bind_cstring(1,szName,30); Step 7 s.bind_cstring(2,sAddress,30); // Binds the host variables to the column results Step 8 while(s.next()) { cout &lt;&lt; szName &lt;&lt; " " &lt;&lt; szAddress &lt;&lt; endl; } // Fetches the records from the data source Step 9, 10 // Automatic constructor will do the job </a>

  2.2.2. OTL vs. DBTools.h++ (by Rogue Wave)

  Sample Program in DBTools.h++

  <a name="sec222"> // This example establishes a connection to a SYBASE database, creates // a table on the server, and uses the reader to read back the values. // The values are then stored on a Memory Table and accessed row by // row. This example can run on both WINDOWS and SUN/SOLARIS or // SUNOS. //#define UNIX // To run examples on Unix SunOS/Solaris #define WINDOWS // To run example on WINDOWS #include &lt;rw/db/db.h&gt; #include &lt;rw/db/dbmgr.h&gt; #ifdef WINDOWS #include &lt;windows.h&gt; #endif void write(const RWCString&amp; w) { #ifdef WINDOWS MessageBox(0,w.data(),"DBTOOLS_BOX",MB_OK); #elif defined(UNIX) cout &lt;&lt; w.data() &lt;&lt; endl; #endif } void errorHandler(const RWDBStatus&amp; s) { char buf[1040]; sprintf(buf,"Message: %s ",s.message().data()); write(RWCString(buf)); } #ifdef WINDOWS #pragma argsused int PASCAL WinMain(HINSTANCE , HINSTANCE, LPSTR, int ) { RWCString serverType("bdbsdld.dll"); #elif defined(UNIX) int main() { RWCString serverType("SYBASE"); #endif // Set the Error Handler RWDBManager::setErrorHandler(errorHandler); // Establish Connection RWDBDatabase adb = RWDBManager::database(serverType, "SYBASE100", // to the database. "henri", "meli12", "SUPPORT"); if(!adb.isValid()) write("Hmmm !!! Cannot Connect. Exit..."); else { RWDBConnection conn=adb.connection(); if(adb.table("TABLE1").exists()) adb.table("TABLE1").drop(); RWDBSchema mySchema; mySchema.appendColumn("id_num",RWDBValue::Int); mySchema.appendColumn("id_type", RWDBValue::String, 15); mySchema.appendColumn("input_date", RWDBValue::DateTime); mySchema.appendColumn("price", RWDBValue::Float); adb.createTable("TABLE1",mySchema, conn); RWDBTable tab=adb.table("TABLE1"); if(tab.exists()) write("Table 'TABLE1' successfully created."); // Insert about 10 Values int numberOfEntries=5; char buffer[1040]; RWDBDateTime date; float price=1.5f; RWDBInserter ins = tab.inserter(); for(int i=0; i&lt;numberOfEntries; i++) { sprintf(buffer,"Item%d",i); date.addDays(1); ins &lt;&lt; i &lt;&lt; RWCString(buffer) &lt;&lt; date &lt;&lt; &lt;&lt; i*price; ins.execute(conn); } // Select all of items RWDBSelector sel = adb.selector(); sel &lt;&lt; tab; RWDBResult res = sel.execute(conn); RWDBReader rdr=res.table().reader(conn); RWDBMemTable memTab(rdr,numberOfEntries); for(i=0;i&lt;memTab.entries(); i++) { sprintf(buffer,"ROW[%d] ID_NUMBER %d ID_TYPE %s \n INPUT_DATE %s INPUT_PRICE %f\n", i, (memTab[i][0]).asInt(), (memTab[i][1]).asString().data(), (memTab[i][2]).asString().data(), (memTab[i][3]).asFloat()); write(RWCString(buffer)); } return 0; } return 0; } </a>

  The Same Sample Program in OTL

  <a name="sec222"> // This example establishes a connection to an Oracle database, creates // a table on the server, and reads the values back. #include &lt;stdio.h&gt; #include &lt;iostream.h&gt; #include &lt;otl.h&gt; otl_connect db; // connect object void write(const char* w) { cout &lt;&lt; w &lt;&lt; endl; // Write a message } int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec ( db, "drop table TABLE1", otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table TABLE1" "( " " id_num number(8)," " id_type varchar2(15)," " input_date date," " price number" ")" ); // create table write("Table 'TABLE1' successfully created."); // Insert about 10 Values { int numberOfEntries=5; char buffer[1040]; int date=0; float price=1.5f; otl_stream ins(50, // buffer size (in rows) "insert into TABLE1 values(" " :id_num&lt;int&gt;," " :id_type&lt;char[64]&gt;," " sysdate+:input_date&lt;int&gt;," " :price&lt;float&gt;" ")", db // connect object ); for(int i=0; i&lt;numberOfEntries; i++){ sprintf(buffer,"Item%d",i); ++date; ins &lt;&lt; i &lt;&lt; buffer &lt;&lt; date &lt;&lt; (float) i*price; // insert one row into the table } } // Select all of items { char buffer[1040]; int count=0; int id_num; char id_type[64]; char input_date[32]; float price; otl_stream sel(10, // buffer size (in rows) "select * TABLE1" db // connect object ); while(!sel.eof()){ sel &gt;&gt; id_num &gt;&gt; id_type &gt;&gt; input_date &gt;&gt; price; // get one row sprintf(buffer, "ROW[%d] ID_NUMBER %d ID_TYPE %s \n" "INPUT_DATE %s INPUT_PRICE %f\n", ++count, id_num, id_type, input_date, price ); } } } catch(otl_exception&amp; p){ // intercept OTL exceptions write(p.msg); } db.logoff(); // disconnect from Oracle return 0; } </a>

  3. Library structure

  OTL falls into two parts: template classes to create host variables and arrays and non-template classes to provide programming interface to the Oracle Call Interface.

  3.1. Host variable and array template classes

  Two types of host objects are distinguished: scalar variables and arrays. OTL has two generic template classes (otl_variable and otl_array) from which the following specialized classes are derived:

  • Numerical data types
    • otl_double, otl_double_array
    • otl_signed_char, otl_signed_char_array
    • otl_short_int, otl_short_int_array
    • otl_int, otl_int_array
    • otl_long_int, otl_long_int_array
    • otl_unsigned, otl_unsigned_array
  • String data types
    • otl_ctring, otl_cstring_array
    • otl_varchar2, otl_varchar2_array
    • otl_long, otl_long_array
    • otl_varchar, otl_varchar_array
    • otl_varraw, otl_varraw_array
    • otl_raw, otl_raw_array
    • otl_char, otl_char_array
    • otl_charz, otl_charz_array
  • Data types for Oracle LONG and LONG RAW columns
    • otl_long_varchar
    • otl_long_varraw
  • Oracle internal data types (DATE, ROWID, VARNUM and NUMBER)
    • otl_date, otl_date_array
    • otl_rowid, otl_rowid_array
    • otl_varnum, otl_varnum_array;
    • otl_number, otl_number_array

  The otl_variable and otl_array classes define the following kinds of buffers which are necessary for handling host variables:

  • value buffer (data member v)
  • indicator buffer (data member ind)
  • returned length buffer (data member rlen)
  • returned code buffer (data member rcode)

  These data members are defined to be public, so the user has access to them and may freely assign and change their values.

  The otl_variable and otl_array classes have a common parent (otl_generic_variable) which contains information about the buffer addresses, dimensions and data type codes. When a host variable or array is constructed from an instantiated template, constructors of the corresponding template classes initialize the data members of the otl_generic_variable class. otl_cursor has a couple of the bind functions which have the second parameter of the otl_generic_variable type. Any template instantiated variables or arrays may be substituted as actual parameters into those bind finctions.

  Class otl_generic_variable

  <a name="sec100"> class otl_generic_variable{ public: </a>
  • Default constructor otl_generic_variable();
  • Destructor virtual ~otl_generic_variable();
  • Assigning a name to the variable void copy_name(const char* aname);
  • Assigning a position (number) to the select list item (column) void copy_pos(const int apos);
  • For input variable/array
    
    
    • Set variable's value as NULL virtual void set_null(int ndx=0);
    • Set variable's buffer length virtual void set_len(int len, int ndx=0);
    • Set variable's value as NOT NULL virtual void set_not_null(int ndx=0);
  • For output variable/array
    
    
    • Check if variable's value is NULL virtual int is_null(int ndx=0);
    • Check if variable's value has been fetched OK virtual int is_success(int ndx=0);
    • Check if variable's value is truncated virtual int is_truncated(int ndx=0);
    • Get pointer to variable's buffer virtual void* val(int ndx=0);
  • Only for output arrays, defined in SELECT statement
    
    
    
    • Check if during fetch conversion error occurred virtual int is_invalid_conversion(int ndx=0);
    • Check if during fetch real overflow occurred virtual int is_real_overflow(int ndx=0);
    • Check if variable has unsupported data type virtual int is_unsupported_datatype(int ndx=0);

• pointer to buffer ub1* p_v;
• pointer to indicator variable/array sb2* p_ind;
• pointer to column's returned length ub2* p_rlen;
• poinetr column's returned code ub2* p_rcode;
• external data type's code of the host variable/array int ftype;
• array element/variable size int elem_size;
• host array size (=1 in case of scalar host variable) int array_size;
• variable name; char* name;
• select list item position int pos;

Class otl_variable

This is the OTL template variable class. It is the base class for constructing specialized host variable classes.

• Default constructor otl_variable();
• Construct variable by the name of aname, e.g. ":F1" otl_variable(const char* aname);
• Construct variable/column, with its further use in a select list in the column_num position. otl_variable(const int column_num);
  

  ---

  
  
• host varibale of data type T T v;
• indicator sb2 ind;
• returned length ub2 rlen;
• returned code ub2 rcode;

Class otl_array

This is the OTL template host array class. It is the base class for constructing specialized template array classes.

• Default constructor otl_array();
• Construct array by the name of aname, e.g. ":F1" otl_array(const char* aname);
• Construct array-column, with its further use in a select list in the column_num position. otl_array(const int column_num);
  

  ---

  
  
• host array T v[size];
• indicator array sb2 ind[size];
• returned length array ub2 rlen[size];
• returned code array ub2 rcode[size];

3.1.1. Specialized host variable classes

• Numerical data types
  • otl_double, example: otl_double f1,f2;
  • otl_float, example: otl_float f1,f2;
  • otl_signed_char, example: otl_signed_char f1,f2;
  • otl_short_int, example: otl_short_int f1,f2;
  • otl_long_int, example: otl_long_int f1,f2;
  • otl_unsigned, example: otl_unsigned f1,f2;

• String data types
  • otl_cstring example: otl_ctring&ltSTR_SIZE> f1,f2;
  • otl_varchar2 example: otl_varchar2&ltSTR_SIZE> f1,f2;
  • otl_long example: otl_long&ltSTR_SIZE> f1,f2;
  • otl_varchar, example: otl_varchar&ltSTR_SIZE> f1,f2;
  • otl_varraw, example: otl_varraw&ltSTR_SIZE> f1,f2;
  • otl_raw, example: otl_raw&ltSTR_SIZE> f1,f2;
  • otl_char, example: otl_char&ltSTR_SIZE> f1,f2;
  • otl_charz, example: otl_charz&ltSTR_SIZE> f1,f2;

• Data types for Oracle LONG and LONG RAW columns

  These two classes are used for reading and writing objects of the Oracle LONG and LONG RAW data types. The classes define operator[] to access elements of the data array, the set_len() function to set up string length on input and the len() function to get string length on output.

  • otl_long_varchar, example: otl_long_varchar&ltSTR_SIZE> f1,f2;
  • otl_long_varraw, example: otl_long_varraw&ltSTR_SIZE> f1,f2;

• Oracle internal data types (DATE, ROWID, VARNUM, NUMBER)
  • otl_date, example: otl_date f1,f2;
  • otl_rowid, example: otl_rowid f1,f2;
  • otl_varnum, example: otl_varnum f1,f2;
  • otl_number, example: otl_number f1,f2;

3.1.2. Specialized host array classes

• Numerical data types
  • otl_double_array, example: otl_double_array&ltARR_SIZE> f1,f2;
  • otl_float_array, example: otl_float_array&ltARR_SIZE> f1,f2;
  • otl_signed_char_array, example: otl_signed_char_array&ltARR_SIZE> f1,f2;
  • otl_short_int_array, example: otl_short_int_array&ltARR_SIZE> f1,f2;
  • otl_int_array, example: otl_int_array&ltARR_SIZE> f1,f2;
  • otl_long_int_array, example: otl_long_int_array&ltARR_SIZE> f1,f2;
  • otl_unsigned_array, example: otl_unsigned_array&ltARR_SIZE> f1,f2;

• String data types
  • otl_ctring_array, example: otl_ctring_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_varchar2_array, example: otl_varchar2_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_long_array, example: otl_long_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_varchar_array, example: otl_varchar_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_varraw_array, example: otl_varraw_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_raw_array, example: otl_raw_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_char_array, example: otl_char_array&ltARR_SIZE,STR_SIZE> f1,f2;
  • otl_charz_array, example: otl_charz_array&ltARR_SIZE,STR_SIZE> f1,f2;

• Oracle internal data types (DATE, ROWID, VARNUM, NUMBER)
  • otl_date_array, example: otl_date_array&ltARR_SIZE> f1,f2;
  • otl_rowid_array, example: otl_rowid_array&ltARR_SIZE> f1,f2;
  • otl_varnum_array, example: otl_varnum_array&ltARR_SIZE> f1,f2;
  • otl_number_array, example: otl_number_array&ltARR_SIZE> f1,f2;

3.2. Oracle Call Interface "wrapper"

Class otl_exception

This is the OTL exception class. Exceptions of this type are raised by the library functions (default mode), unless it is prohibited explicitly in the otl_connect or otl_cursor class constructors. In case of disabled exceptions OTL functions return codes and it is the user's responsibility to check out the codes and handle errors. The main advantage of using this exception handling mechanism is that exceptions can be processed in one catch block, instead of checking return codes from every library function call.

• This "enum" defines two constants which are used in constructors of the otl_connect, otl_cursor and otl_select_cursor classes. enum{ disabled, enabled };
• Create exception from LDA otl_exception(Lda_Def&amp; lda);
• Create exception from amsg and acode otl_exception(const char* amsg,const int acode);
• Copy constructor otl_exception(const otl_exception&amp; p);
• Default constructor otl_exception();
  

  ---

  
  
• error message buffer unsigned char msg[1000];
• error code int code;

Class otl_object

This class is a parent of the otl_cursor and otl_connect classes. Its children inherit the following two properties:

• "connected" flag
• "exception enabled" flag

<a name="sec104"> class otl_object{ public: </a>

• Default constructor otl_object();
• Destructor virtual ~otl_object();
  

  ---

  
  
• "connected" flag int connected;

<a name="sec104"> protected: </a>

• "exception enabled" flag int ex_enabled;

<a name="sec104"> }; </a>

Class otl_connect

This class encapsulates the Oracle Call Interface functions which have Logon Descriptor Area as their first parameter. In other words, otl_connect is the class for creating "connect" objects.

• Create "connect" object. Exceptions are allowed to raise by default otl_connect(int exception_enabled=otl_exception::enabled);
• Create "connect" object and connect to Oracle using the "connect_str" connect string; by default, exceptions are allowed to raise otl_connect(const char* connect_str, int exception_enabled=otl_exception::enabled );
• Destructor ~otl_connect();
• Concurrent logon; OCI application is allowed to have more than one concurrent logon. Returns 1 on success, 0 on failure. int rlogon(const char* connect_str);
• Get valid LDA from Pro*C. Returns 1 on success, 0 on failure. int sqllda(void);
• Exclusive logon. there may be only one logon of this type performed in OCI application (for more details see OCI doc.). Returns 1 on success, 0 on failure. int logon(const char* connect_str);
• Disconnect from / log off Oracle. Returns 1 on success, 0 on failure. int logoff(void);
• Commit current transaction. Returns 1 on success, 0 on failure. int commit(void);
• Roll back current transaction. Returns 1 on success, 0 on failure. int rollback(void);
• Break current OCI call. Returns 1 on success, 0 on failure. int obreak(void);
• Set auto commit mode on. Returns 1 on success, 0 on failure. int auto_commit_on(void);
• Set auto commit mode off. Returns 1 on success, 0 on failure. int auto_commit_off(void);
  

  ---

  
  
• Logon Descriptor Area Lda_Def lda;
• reference to "V7 return code" ub2&amp; rc;

Class otl_column_desc

• field name sb1 name[241];
• field name length sb4 nlen;
• field size as the field is represented inside ORACLE sb4 dbsize;
• internal datatype code sb2 dbtype;
• numeric field scale: NUMBER(scale,precision) sb2 scale;
• numeric field precision sb2 prec;
• maximum display size of the field sb4 dsize;
• NULLs are allowed sb2 nullok;

Class otl_cursor

This class is a general-purpose cursor class.

• Create "cursor" object. by default, exceptions are allowed to raise. otl_cursor(int exception_enabled=otl_exception::enabled);
• Create "cursor" object and open cursor via "connect" otl_cursor(otl_connect&amp; connect, // reference to "connect" object int exception_enabled=otl_exception::enabled );
• Close cursor (if opened) and destruct object ~otl_cursor();
• Open cursor via "connect". Returns 1 on success, 0 on failure int open(otl_connect&amp; connect);
• Close cursor. Returns 1 on success, 0 on failure int close(void);
• Cancel a query after desired number of rows have been fetched. Returns 1 on success, 0 on failure int cancel(void);
• Set rollback options for non-fatal Oracle errors. For more info see the OCI manual, the "oopt" function. Returns 1 on success, 0 on failure int option(int rbopt, int waitopt);
• Fetch a portion of a LONG or LONG RAW column. For more info see the OCI manual, the "oflng" function. Returns 1 on success, 0 on failure int fetch_long(int column_num, // column number: 1,2,... void* buf, // pointer to buffer sb4 bufl, // buffer size int dtype, // buffer data type, see ext* "enum" ub4* retl, // returned length sb4 offset // offset );
• Parse sql statement. Returns 1 on success, 0 on failure int parse(const char* sqlstm);
• Parse sql statement; bind variable and select list items; variable list needs to be terminated with 0 pointer; Returns 1 on success, 0 on failure. if variable list contains variables which are SELECT statement output columns and if the variables don't have "column_num" defined, then the parse function enumerates the variables as follows:
  
  
  • eparse("select...",&f1,&f2,&f3,0);
    
    
    • f1 -- column 1
    • f2 -- column 2
    • f3 -- column 3
  
  int eparse(const char* sqlstm,...);
• Execute statement iters times. Returns 1 on success, 0 on failure int exec(short iters=1);
• Combined operation: Parse+Bind+Execute. Parse sqlstm. Bind variables. Execute statement iters times. Returns 1 on success, 0 on failure int exec(const char* sqlstm, // SQL statement short iters, // number of iterations ... // NULL terminated host variable list );
• Fetch iters number of rows. Returns 1 on success, 0 on failure int fetch(short iters=1);
• Combined operation -- execute statement + fetch iters number of rows. Returns 1 on success, 0 on failure int exfet(short iters=1);
• Functions to bind placeholders
  
  
  • Bind host variable/array (instantiated template) to placeholder. Returns 1 on success, 0 on failure int bind(const char* name, // placeholder name: ":F1", ":F2" otl_generic_variable&amp; v // reference to host variable/array );
  • Bind "ordinary" host variable/array to placeholder. Returns 1 on success, 0 on failure int bind(const char* name, // placeholder name: ":f1", ":f2" void* buf, // pointer to host variable/array int elem_size, //array element/ variable size in bytes int ftype, // Oracle external data type code sb2* indp=0 // pointer to indicator variable/array );
  • Bind template host variable/array with already defined name. Returns 1 on success, 0 on failure int bind(otl_generic_variable&amp; v);
  
  
  
• Functions to bind select list items (columns)
  
  
  • Bind host variable/array (instantiated template) to column. Returns 1 on success, 0 on failure int bind(int column_num, // column number: 1,2,... otl_generic_variable&amp; v // reference to variable/array );
  • bind "ordinary" host variable/array to column. Returns 1 on success, 0 on failure int bind(int column_num, // column number: 1,2,... void* buf, // pointer to host variable/array int elem_size, // array element/variable size in bytes int ftype, // Oracle external data type code sb2* indp=0, // pointer to indicator array/varibale ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array variable );
• Specialized function to bind columns
  
  
  • Bind C-style (null terminated) string variable/array to column. Returns 1 on success, 0 on failure int bind_cstring(int column_num, // column number: 1,2,... char* buf, // pointer to C-string variable/array int elem_size, // array element/variable size sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  • Bind int variable/array to column. Returns 1 on success, 0 on failure int bind_int(int column_num, // column number: 1,2,... int* buf, // pointer to int variable/array sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  • Bind short int variable/array to column. Returns 1 on success, 0 on failure int bind_short(int column_num,// column number: 1,2,... short* buf, // pointer to short int variable/array sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  • Bind long int variable/array to column. Returns 1 on success, 0 on failure int bind_long_int(int column_num, // column number: 1,2,... long* buf, // pointer to long int variable/array sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  • Bind float variable/array to column. Returns 1 on success, 0 on failure int bind_float(int column_num,// column number: 1,2,... float* buf, // pointer to float variable/array sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  • Bind double variable/array to column. Returns 1 on success, 0 on failure int bind_double(int column_num,// column number: 1,2,... double* buf, // pointer to double variable/array sb2* indp=0, // pointer to indicator array/variable ub2* rlen=0, // pointer to returned length array/variable ub2* rcode=0 // pointer to returned code array/variable );
  
  
  
• Specialized functions to bind placeholders
  
  
  • Bind C-style (null terminated) string variable/array to column. Returns 1 on success, 0 on failure int bind_cstring(const char* name, // placeholder name: ":F1", ":F2" char* buf, // pointer to C-string variable/array int elem_size, // array element/variable size sb2* indp=0 // pointer to indicator array/variable );
  • Bind int variable/array to placeholder. Returns 1 on success, 0 on failure int bind_int(const char* name, // placeholder name: ":F1", ":F2" int* buf, // pointer to int variable/array sb2* indp=0 // pointer to indicator array/variable );
  • Bind short int variable/array to placeholder. Returns 1 on success, 0 on failure int bind_short(const char* name, // placeholder name: ":F1", ":F2" short* buf, // pointer to short int variable/array sb2* indp=0 // pointer to indicator array/variable );
  • Bind long int variable/array to placeholder. Returns 1 on success, 0 on failure int bind_long_int(const char* name, // placeholder name: ":F1", ":F2" long* buf, // pointer to long int variable/array sb2* indp=0 // pointer to indicator array/variable );
  • Bind float variable/array to placeholder. Returns 1 on success, 0 on failure int bind_float(const char* name, // placeholder name: ":F1", ":F2" float* buf, // pointer to float variable/array sb2* indp=0 // pointer to indicator array/variable );
  • Bind double variable/array to placeholder. Returns 1 on success, 0 on failure int bind_double(const char* name, // placeholder name: ":F1", ":F2" double* buf, // pointer to double variable/array sb2* indp=0 // pointer to indicator array/variable );
  
  
  
• Static (in class) function to immediately execute a constant SQL statement. Returns 1 on success, 0 on failure static int direct_exec(otl_connect&amp; db, // connect object const char* stm, // statement int exception_enabled=otl_exception::enabled // exception_enabled flag );
• Check "end-of-file" condition when fetching rows from select statement. Returns 1 when "EOF", 0 otherwise int eof(void);
• Describe select item (column) int describe_column(otl_column_desc&amp; col_desc, // column descriptor structure int column_num // column number: 1,2,... );
• "End-of-description" condition check int end_of_desc(void);
• Parse statement and bind variables int parse(const char* sqlstm, // SQL statement otl_generic_variable** v // pointer to variable list );
  

  ---

  
  
• Cursor Descriptor Area Cda_Def cda;
• convenient references to some of the CDA fields
  
  
  • reference to "rows processed count" ub4&amp; rpc;
  • reference to "OCI function code" ub2&amp; ft;
  • reference to "V7 return code" ub2&amp; rc;
  • reference to "parse error offset" ub2&amp; peo;

Class otl_select_cursor

• General constructor otl_select_cursor(otl_connect&amp; db, // connect object short arr_size=1, // attached host array size int exception_enabled=otl_exception::enabled // exception enabled flag );
• Fetch first row. rows are fetched in batches. "cur_row" points to current row in host array attached to select statement. cur_row==-1 if no rows have been fetched. Returns 1 on success, 0 on failure. int first(void);
• Fetch next row. "cur_row" points to current row. cur_row==-1 after fetch sequence is complete. Returns 1 on success, 0 on failure. int next(void);
  

  ---

  
  
• index of current row in host array int cur_row;
• number of rows in the buffer after most recent fetch int cur_size;
• row count -- total number of rows fetched int row_count;
• size of host array attached to select statement int array_size;

Class otl_dynamic_variable

This class is used in the otl_select_stream class to dynamically allocate a list of output columns of SELECT statement and any other automatically created bind variables (e.g. in a SQL stream).

• Dynamically construct a select list item (column) otl_dynamic_variable( const int column_num, // column number/position in select // list const ftype, // external data type of the column const elem_size, // host array element/variable size const short array_size=1 // array size );
• Dynamically construct host variable/array otl_dynamic_variable( const char *aname, // varable/array name const ftype, // external data type const elem_size, // variable/array element size const short array_size=1 // array size );
• Default constructor otl_dynamic_variable();
• Destructor ~otl_dynamic_variable();
• Allocate memory and initialize variable/array void init(const int ftype, // external data types const int elem_size, // array element/variable size const short array_size // array size );
• return dynamic variable's external data type int get_ftype(void);
• return dynamic variable's elem_size int get_elem_size(void);

Class otl_err_info

• Default constructor otl_err_info();
• Create error info object from otl_connect object otl_err_info(otl_connect&amp; connect);
• Create error info object from otl_cursor object otl_err_info(otl_cursor&amp; cur);
• Read error info into existing error info descriptor void get_info(otl_connect&amp; connect); void get_info(otl_cursor&amp; cur);

3.3. OTL stream interface

In OTL, SQL streams are introduced. The idea here is to combine streams and SQL. Such a combination provides new quality and simplicity in programming interface to SQL. The Oracle Array Interface naturally transforms into buffered stream operations.

The SQL streams are intended for SQL or PL/SQL statements which have input and/or output bind variables. Any statement can be treated as a functional element with input/output parameters. There are functions to put objects into a stream, that is, to assign values to input variables. Also, there are functions to get objects from the stream, that is, to get values from output variables.

When values of all input variables of the functional element are filled out then the element is executed. Resulting values are assigned to the output variables right after the execution. Sets of input and output variables are allowed to overlap (see the picture).

Logically, a SQL stream is a structured stream with input and output rows. The format of the input row is defined by a set of output variables of the stream. Similarly, the output row is defined by input variables of the stream. When objects are written into the stream, values are actually assigned to the input variables. Likewise, when objects are read from the stream, values are read from the output variables of the stream.

SQL streams are similar to buffered files. A SQL statement or PL/SQL block is opened as an ordinary buffered file. The logic of the SQL stream operations remains the same as the file operations with the only exception -- the SQL stream has separate input and output buffers which may overlap.

The SQL stream in C++ has a flush function for flushing its input buffer when the buffer gets full and a collection of >> and << operators for reading and writing objects of different data types. The most important advantage of the SQL streams is their unified interface to SQL statements and PL/ SQL blocks of any kind. This mean that application developers need to remember just a few syntactical constructs and function names which they already got familiar with when they started working with C++ streams.

Inside the SQL stream there is a small parser for parsing declarations of bind variables and their data types. There is no need to declare C/C++ host variables and bind them with placeholders by special bind function calls. All necessary buffers are created dynamically inside the stream. The stream just needs to be opened for reading input values and writing output values.

The OTL stream interface requires use of the OTL exceptions. This means that potentially any OTL stream operation can throw an exception of the otl_exception type. In order intercept the exception and prevent the program from aborting, wrap up the OTL stream code with the corresponding try & catch block.

For more detail on the stream class hierarchy, see Appendix A.

Class otl_select_stream

This is the OTL select stream class. The user does not need to manually attach output columns to SELECT statement because the statement is automatically parsed and the output columns are allocated in the class constructor.

This class may issue the following otl_exceptions:

• Incompatible data types in stream operation, code=32000
• Not all input variables have been initialized, code=32009
• No input variables have beed defined in SELECT statement, code=32004

• General conctructor. SELECT statement is parsed, all input host variables and output columns are automatically bound. otl_select_stream(otl_connect&amp; db, // connect object const char* sqlstm, // SELECT statement const short arr_size, // output host arrays size ... // NULL terminated list of pointers to input host // variables. );
• General conctructor. SELECT statement is parsed, all input host variables and output columns are automatically bound. The difference between this constructor and the constuctor above is that this constuctor takes a pointer to an array of pointer to the host variable/array list, instead of taking them from stack, as the above constuctor does. This allows the user to dynamically create host variables, say, in a loop, and collect pointers to the variables into an array. otl_select_stream( otl_connect&amp; db, // connect object const char* sqlstm, // SELECT statement otl_p_generic_variable* avp, // Pointer to NULL terminated list of // pointers to input hots // variables const short arr_size=1 // output host arrays size );
• General conctructor. SELECT statement is parsed, all input host variables and output columns are automatically allocated and bound. This constructor allows the user to use extended place-holder declarations. otl_select_stream( const short arr_size, // output host arrays size const char* sqlstm, // SELECT statement otl_connect&amp; db // connect object );
• Destructor ~otl_select_stream();
• Rewind stream, SQL statement is re-executed. Input host variables of SELECT statement may be re-assigned before calling this function. void rewind(void);
• Test if NULL has been fetched during last stream operation int is_null(void);
• Test if "end-of-file" has been reached. Returns 1 when "end-of-file". int eof(void);
• Read objects from stream otl_select_stream&amp; operator&gt;&gt;(char&amp; c); otl_select_stream&amp; operator&gt;&gt;(unsigned char&amp; c); otl_select_stream&amp; operator&gt;&gt;(char* s); otl_select_stream&amp; operator&gt;&gt;(unsigned char* s); otl_select_stream&amp; operator&gt;&gt;(int&amp; n); otl_select_stream&amp; operator&gt;&gt;(unsigned&amp; u); otl_select_stream&amp; operator&gt;&gt;(short&amp; sh); otl_select_stream&amp; operator&gt;&gt;(long int&amp; l); otl_select_stream&amp; operator&gt;&gt;(float&amp; f); otl_select_stream&amp; operator&gt;&gt;(double&amp; d);
• Write input values to the stream (initialize input variables) otl_select_stream&amp; operator&lt;&lt;(const char c); otl_select_stream&amp; operator&lt;&lt;(const unsigned char c); otl_select_stream&amp; operator&lt;&lt;(const char* s); otl_select_stream&amp; operator&lt;&lt;(const unsigned char* s); otl_select_stream&amp; operator&lt;&lt;(const int n); otl_select_stream&amp; operator&lt;&lt;(const unsigned u); otl_select_stream&amp; operator&lt;&lt;(const short sh); otl_select_stream&amp; operator&lt;&lt;(const long int l); otl_select_stream&amp; operator&lt;&lt;(const float f); otl_select_stream&amp; operator&lt;&lt;(const double d);
• Get info on SELECT list items int select_list_len(void); int column_ftype(int ndx=0); int column_size(int ndx=0);
• Get column's internal info otl_column_desc* sl_desc; // column descriptor array
• Set flag "delete input host variables" void set_delete_var(const int should_delete=0);
• Special constructor. It parses SELECT statement and gets SELECT list information. This constructor can be used when the user does not really want to fetch rows via this stream but wants to get information on the SELECT list (output columns). otl_select_stream(const char* sqlstm, // SELECT statement otl_connect&amp; db, // connect object const char* dummy_par // dummy parameter (needed for // making the function // prototype unique );

Class otl_out_stream

This is the OTL output class. This class is used for the following SQL statements:

• DELETE
• UPDATE
• INSERT
• PL/SQL block with input host variables only

This class may issue the following otl_exceptions:

• Incompatible data types in stream operation, code=32000
• Row must be full for flushing output stream, code=32004

• Default constructor otl_out_stream(otl_connect&amp; db):otl_cursor(db){}
• General conctructor. SQL statement is parsed, all ouput host variables are automatically bound. otl_out_stream(otl_connect&amp; db, // connect object const char* sqlstm, // SQL statement ... // NULL terminated list of pointers to input hots // variables. );
• General conctructor. SQL statement is parsed, all ouput host variables are automatically bound. otl_out_stream(otl_connect&amp; db, // connect object const char* sqlstm, // SQL statement otl_p_generic_variable* avp // Pointer to NULL terminated list of pointers // to input hots variables. );
• General conctructor. SQL statement is parsed, all host variables are automatically allocated and bound. This constructor allows the user to use extended place-holder declarations. otl_out_stream( short arr_size, // host array size const char* sqlstm, // SQL statement otl_connect&amp; db // connect object );
• Destructor ~otl_out_stream();
• Write objects into stream otl_out_stream&amp; operator&lt;&lt;(const char c); otl_out_stream&amp; operator&lt;&lt;(const unsigned char c); otl_out_stream&amp; operator&lt;&lt;(const char* s); otl_out_stream&amp; operator&lt;&lt;(const unsigned char* s); otl_out_stream&amp; operator&lt;&lt;(const int n); otl_out_stream&amp; operator&lt;&lt;(const unsigned u); otl_out_stream&amp; operator&lt;&lt;(const short sh); otl_out_stream&amp; operator&lt;&lt;(const long int l); otl_out_stream&amp; operator&lt;&lt;(const float f); otl_out_stream&amp; operator&lt;&lt;(const double d); otl_out_stream&amp; operator&lt;&lt;(const otl_null n); // write Oracle NULL into stream
• Flush stream buffer. SQL statement is executed as many times as rows have been entered into the stream buffer. virtual void flush(void);
• Clean up stream buffer without flushing it. virtual void clean(void);
• Set "auto-commit" flag. When the buffer is flushed, current transaction is automatically commited, if the flag is set. When otl_out_stream is opened, by default, "auto-commit" flag is set, and when the stream buffer is flushed, current transaction commits. In order to unset the flag, use this function. void set_commit(int auto_commit=0);
• Set flag "delete host variables". This flag is used, when, for example, the user allocate host variables in dynamic memory and wants the stream destuctor to automatically deallocate the occupied memory. void set_delete_var(const int should_delete=0);

Class otl_inout_stream

This is the OTL input/output stream class. It is used primarily for PL/SQL blocks with input and output parameters. Though, this stream class can be used for SQL statements and PL/SQL blocks with input or output parameters only.

This class may issue the following otl_exceptions:

• Incompatible data types in stream operation, code=32000
• Row must be full for flushing output stream, code=32001

• General conctructor. SQL statement is parsed, all host input and output hostvariables are automatically allocated and bound.This constructor allows the user to use extended place-holder declarations. otl_inout_stream( short arr_size, // host array size const char* sqlstm, // SQL statement otl_connect&amp; db // connect object );
• Destructor ~otl_inout_stream();
• Test if all data has been already read from the stream int eof(void);
• Flush stream's output buffer. It actually means to execute the SQL statement as many times as rows entered to the output buffer. The stream is automatically flushed when the buffer gets full. void flush(void);
• Clean up buffer without flushing it. void clean(void);
• Rewind stream void rewind(void);
• Test if NULL was fetched from the stream int is_null(void);
• Read objects from stream otl_inout_stream&amp; operator&gt;&gt;(char&amp; c); otl_inout_stream&amp; operator&gt;&gt;(unsigned char&amp; c); otl_inout_stream&amp; operator&gt;&gt;(char* s); otl_inout_stream&amp; operator&gt;&gt;(unsigned char* s); otl_inout_stream&amp; operator&gt;&gt;(int&amp; n); otl_inout_stream&amp; operator&gt;&gt;(unsigned&amp; u); otl_inout_stream&amp; operator&gt;&gt;(short&amp; sh); otl_inout_stream&amp; operator&gt;&gt;(long int&amp; l); otl_inout_stream&amp; operator&gt;&gt;(float&amp; f); otl_inout_stream&amp; operator&gt;&gt;(double&amp; d);

Class otl_stream

This is the OTL stream class. It is a general-purpose and most advanced stream class, unified for streams of all types. This class may issue the following otl_exceptions:

• Incompatible data types in stream operation, code=32000
• Row must be full for flushing output stream, code=32001
• Not all input variables have been initialized, code=32004
• No input variables have been defined in SQL statement, code=32004

• General conctructor. SQL statement is parsed, all host input and output host variables are automatically allocated and bound. This constructor allows the user to use extended place-holder declarations. otl_stream( short arr_size, // host array size const char* sqlstm, // SQL statement otl_connect&amp; db // connect object );
• Default constructor otl_stream();
• Desctructor ~otl_stream();
• Test if all data has been already read from the stream int eof(void);
• Flush stream's output buffer. It actually means to execute the SQL statement as many times as rows entered to the output buffer. The stream is automatically flushed when the buffer gets full. void flush(void);
• Clean up buffer without flushing it. void clean(void);
• Rewind stream void rewind(void);
• Test if NULL was fetched from the stream int is_null(void);
• Set "auto-commit" flag. When the buffer is flushed, current transaction is automatically commited, if the flag is set. By default, the flag is set. In order to prevent current transaction from "auto-commit", uset the flag using this function. void set_commit(int auto_commit=0);
• Open stream void open( short arr_size, // host array size const char* sqlstm, // SQL statement otl_connect&amp; db // connect object );
• Close stream void close(void);
• Test if the stream was opened okay int good(void);
• Read objects from stream otl_stream&amp; operator&gt;&gt;(char&amp; c); otl_stream&amp; operator&gt;&gt;(unsigned char&amp; c); otl_stream&amp; operator&gt;&gt;(char* s); otl_stream&amp; operator&gt;&gt;(unsigned char* s); otl_stream&amp; operator&gt;&gt;(int&amp; n); otl_stream&amp; operator&gt;&gt;(unsigned&amp; u); otl_stream&amp; operator&gt;&gt;(short&amp; sh); otl_stream&amp; operator&gt;&gt;(long int&amp; l); otl_stream&amp; operator&gt;&gt;(float&amp; f); otl_stream&amp; operator&gt;&gt;(double&amp; d);
• Write objects into stream otl_stream&amp; operator&lt;&lt;(const char c); otl_stream&amp; operator&lt;&lt;(const unsigned char c); otl_stream&amp; operator&lt;&lt;(const char* s); otl_stream&amp; operator&lt;&lt;(const unsigned char* s); otl_stream&amp; operator&lt;&lt;(const int n); otl_stream&amp; operator&lt;&lt;(const unsigned u); otl_stream&amp; operator&lt;&lt;(const short sh); otl_stream&amp; operator&lt;&lt;(const long int l); otl_stream&amp; operator&lt;&lt;(const float f); otl_stream&amp; operator&lt;&lt;(const double d); otl_stream&amp; operator&lt;&lt;(const otl_null n); // write Oracle NULL into stream
• C-style printf/scanf functions void printf(const char* fmt,...); void scanf(const char* fmt,...);

  The following format specifiers are supported:

  • %d -- int
  • %u -- unsigned
  • %ld -- long int
  • %f -- float
  • %lf -- double
  • %c -- char
  • %s -- string
  • %N -- specifier for writing NULL into streams

Stream bind variable declarations

This section explains in detail how to declare bind variables (or extended place-holders) in the SQL streams.

A SQL statement or PL/SQL block may have placeholders which are usually connected with the corresponding bind variables in the program. In Pro*C the user needs to declare such variables directly in the program. OTL provides the same functionality in another way. There is a small parser which parses a SQL statament or PL/SQL block declaration and allocates corresponding bind variables dynamically inside the stream.

The following data types for extneded place-holder declarations are available:

• int
• unsigned
• short
• long -- (long integer)
• float
• double
• char[length]

For PL/SQL blocks, special qualifiers are introduced to distinguish between input and output variables: