otl1.htm

ISO_C++:C++_OTL开发文档

<h3><a name="sec218">2.1.8. Example 8 (with the otl_stream class)</h3>

<h4>Source code</h4>
<xmp>

#include <iostream.h>
#include <stdio.h>
#include <otl.h>

otl_connect db; // connect object

void insert()
// insert rows into table
{ 
 otl_stream o(50, // buffer size
	      "insert into test_tab values(:f1<float>,:f2<char[31]>)", 
	         // SQL statement
	      db // connect object
	     );
 char tmp[32];

 for(int i=1;i<=100;++i){
  sprintf(tmp,"Name%d",i);
  o<<(float)i<<tmp;
 }
} /* insert */

void select()
{ 
 otl_stream i(50, // buffer size
	      "select * from test_tab where f1>=:f<int> and f1<=:f*2",
	         // SELECT statement
	      db // connect object
	     ); 
   // create select stream
 
 int f1;
 char f2[31];

 i<<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>>f1>>f2;
  cout<<"f1="<<f1<<", f2="<<f2<<endl;
 }
 
 i<<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>>f1>>f2;
  cout<<"f1="<<f1<<", f2="<<f2<<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& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
 }

 db.logoff(); // disconnect from Oracle

 return 0;

} /* main */

</xmp>
<h4>Output</h4>
<xmp>

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

</xmp>


<h3><a name="sec219">2.1.9. Example 9 (with PL/SQL block)</h3>

<h4>Source code</h4>
<xmp>

#include <iostream.h>
#include <stdio.h>
#include <otl.h>

otl_connect db; // connect object

void plsql()
// invoking PL/SQL block
{ 
 otl_stream o(5, // buffer size
	      "begin "
	      " :A<int,inout> := :A+1; "
	      " :B<char[31],out> := :C<char[31],in>; "
	      "end;",
	         // PL/SQL block
	      db // connect object
	     );
 o<<1<<"Test String1"; // assigning :A = 1, :C = "Test String1"
 o<<2<<"Test String2"; // assigning :A = 2, :C = "Test String2"
 o<<3<<"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>>a>>b;
  cout<<"A="<<a<<", B="<<b<<endl;
 }

} /* plsql */

int main()
{
 try{
  db.rlogon("scott/tiger"); // connect to Oracle
  plsql(); // invoking PL/SQL block
 }
 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
 }
 db.logoff(); // disconnect from Oracle
 return 0;
} /* main */

</xmp>
<h4>Output</h4>
<xmp>

A=2, B=Test String1
A=3, B=Test String2
A=4, B=Test String3

</xmp>


<h3><a name="sec2110">2.1.10. Example 10 (with printf/scanf functions)</h3>

<h4>Source code</h4>
<xmp>

#include <iostream.h>
#include <stdio.h>
#include <otl.h>

otl_connect db; // connect object

void insert()
// insert rows into table
{ 
 otl_stream o(50, // buffer size
	      "insert into test_tab values(:f1<int>,:f2<char[31]>)", 
	         // SQL statement
	      db // connect object
	     );
 char tmp[32];

 for(int i=1;i<=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>=:f<int> and f1<=:f*2",
	         // SELECT statement
	      db // connect object
	     ); 
   // create select stream
 
 int f1;
 char f2[31];

 i<<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",&f1,f2); // read one row from stream
  cout<<"f1="<<f1<<", f2="<<f2<<endl;
 }
 
 i<<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",&f1,f2); // read one row from stream
  cout<<"f1="<<f1<<", f2="<<f2<<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& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
 }

 db.logoff(); // disconnect from Oracle

 return 0;

} /* main */

</xmp>
<h4>Output</h4>
<xmp>

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

</xmp>


<h3><a name="sec2111">2.1.11. Example 11 (with Prosto*C)</h3>

<h4>Source code</h4>
<xmp>

#include <iostream.h>
#include <stdlib.h>
#include <stdio.h>
#include <otl.h>

otl_connect* db=0; // pointer to connect object

void my_handler(char* msg, int code)
// my error handler
{
 cout<<msg<<endl;
 if(db)db->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<int>,:f2<char[31]>)", // SQL statement
   50 // buffer size
  );

 char tmp[32];

 for(int i=1;i<=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>=:f<int> and f1<=: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",&f1,f2); // fetch a row from the stream
  cout<<"f1="<<f1<<", f2="<<f2<<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",&f1,f2); // fetch a row from the stream
  cout<<"f1="<<f1<<", f2="<<f2<<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 */

</xmp>
<h4>Output</h4>
<xmp>

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

</xmp>

<h3><a name="sec22">2.2. Comparison with the other C++/database 
libraries</h3> 
<p>
In this section, OTL is compared with the other C++/database
libraries, namely:
</p>
<ul>
<li>
<a href="http://www.intelligent-objects.com./sqlobj.html">SQLObjects
(by Intelligent Objects)</a>
</li>
<li>
<a
href="http://www.roguewave.com/products/dbtools/dbtools.html">DBTools.h++ (by
Rogue Wave)</a>
</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
<p>
It is not such a bad idea to harness most of the power Oracle
provides:
</p>
<ul>
<li>PL/SQL functions/procedures/packages</li>
<li>Other Oracle SQL extentions</li>
<li>Oracle Array Interface</li>
</ul>
<p>
Typical problem of accessing Oracle via ODBC is that no ODBC
driver provides transparent access to PL/SQL, especially with
<b>OUT</b> or <b>IN OUT</b> parameters.
</p>
<p>
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.
</p>
<p>
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.
</p>
<p>
Let's consider a typical C++ interface to a database. It provides
a set of functions like:
</p>
<ul>
<li>Connect/Disconnect</li>
<li>Open/Close cursor</li>
<li>Parse/Execute SQL statement</li>
<li>Bind host variables</li>
<li>Fetch rows from a SELECT statement</li>
<li>Get and process database errors</li>
</ul>
<p>
Usually, such a layer of code is called access library. Some
vendors go far beyond that and provide <i>factories</i>, 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.
</p>
<p>
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 <a href="#sec33">SQL stream</a> interface.
</p>
<p>
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.
</p>
<p>
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 <a href="#sec4">Pro*OTL / Pre-Pro*C preprocessor</a>
may be the first step toward such a factory.
</p>
<p>
</p>
<h4><a name="sec221">2.2.1. OTL vs. SQLObjects (by Intelligent Objects)</h4>

<h4>Sample Program in SQLObjects</h4>
<xmp>

Step 1 char szName[31]="";
Step 2 char szAddress[31]="";