📄 otl1.htm
字号:
<html>
<head>
<title>Oracle Call Interface Template Library 1.0.5 (OTL), Pro*OTL /
Pre-Pro*C preprocessor 1.0.0 (PPC)</title>
</head>
<body>
<h1>Oracle Call Interface Template Library 1.0.5 (OTL), Pro*OTL /
Pre-Pro*C preprocessor 1.0.0 (PPC)</h1>
<p><i>Sergei Kuchin, email:
<a href="mailto:skuchin@aceweb.com">skuchin@aceweb.com</a>,
<a href="mailto:skuchin@gmail.com">skuchin@gmail.comgmail</i>
</p>
<xmp>
Copyright (C) Sergei Kuchin, 1996, 1997,1998
Permission to use, copy, modify and redistribute this
document for any purpose is hereby granted without fee,
provided that the above copyright notice appear in all
copies.
</xmp>
<h2>Table of Contents</h2>
<ul>
<li><a href="#sec1">1. Introduction</a>
<li><a href="#sec2">2. Getting started with OTL</a>
<ul>
<li><a href="#sec21">2.1. Examples</a>
<ul>
<li><a href="#sec211">2.1.1. Example 1 (with ordinary, non-template host variables)</a>
<li><a href="#sec212">2.1.2. Example 2 (with template instantiated host variables)</a>
<li><a href="#sec213">2.1.3. Example 3 (with the extended "parse" function)</a>
<li><a href="#sec214">2.1.4. Example 4 (with Oracle LONG columns)</a>
<li><a href="#sec215">2.1.5. Example 5 (with the otl_select_stream class)</a>
<li><a href="#sec216">2.1.6. Example 6 (with the otl_out_stream class)</a>
<li><a href="#sec217">2.1.7. Example 7 (with the otl_inout_stream class)</a>
<li><a href="#sec217">2.1.8. Example 8 (with the otl_stream class)</a>
<li><a href="#sec219">2.1.9. Example 9 (with PL/SQL block)</a>
<li><a href="#sec2110">2.1.10. Example 10 (with printf/scanf functions)</a>
<li><a href="#sec2111">2.1.11. Example 11 (with Prosto*C)</a>
</ul>
<li><a href="#sec22">2.2. Comparison with the other C++/database libraries</a>
<ul>
<li>
<a href="#sec221">2.2.1. OTL vs. SQLObjects (by Intelligent Objects)</a>
</li>
<li>
<a href="#sec222">2.2.2. OTL vs. DBTools.h++ (by Rogue Wave)</a>
</li>
</ul>
<li><a href="#sec3">3. Library structure</a>
<ul>
<li><a href="#sec31">3.1. Host variable and array template classes</a>
<ul>
<li><a href="#sec311">3.1.1. Specialized host variable classes</a>
<li><a href="#sec312">3.1.2. Specialized host array classes</a>
</ul>
<li><a href="#sec32">3.2. Oracle Call Interface "wrapper"</a>
<ul>
<li><a href="#sec103">Class otl_exception</a>
<li><a href="#sec104">Class otl_object</a>
<li><a href="#sec105">Class otl_connect</a>
<li><a href="#sec106">Class otl_column_desc</a>
<li><a href="#sec107">Class otl_cursor</a>
<li><a href="#sec108">Class otl_select_cursor</a>
<li><a href="#sec200">Class otl_dynamic_variable</a>
<li><a href="#sec109">Class otl_err_info</a>
</ul>
<li><a href="#sec33">3.3. OTL stream interface</a>
<ul>
<li><a href="#sec201">Class otl_select_stream</a>
<li><a href="#sec202">Class otl_out_stream</a>
<li><a href="#sec203">Class otl_inout_stream</a>
<li><a href="#sec204">Class otl_stream</a>
<li><a href="#sec205">Stream bind variable declarations</a>
</ul>
<li><a href="#sec34">3.4. Prosto*C</a>
</ul>
<li><a href="#sec4">4. Pro*OTL / Pre-Pro*C preprocessor (PPC)</a>
<ul>
<li><a href="#sec41">4.1. Getting started with PPC</a>
<ul>
<li><a href="#sec411">Example in Pro*C</a>
<li><a href="#sec412">Example in C++</a>
</ul>
<li><a href="#sec42">4.2. Directives</a>
<ul>
<li><a href="#sec421">4.2.1. #sql-select</a>
<li><a href="#sec422">4.2.2. #sql-out-stm</a>
<li><a href="#sec423">4.2.3. #sql-plsql</a>
<li><a href="#sec424">4.2.4. #sql-init-module</a>
<li><a href="#sec425">4.2.5. #sql-init-main</a>
<li><a href="#sec426">4.2.6. #sql-str-type</a>
</ul>
<li><a href="#sec43">4.3. Command line parameters</a>
</ul>
<li><a href="#sec5">5. Acknowledgements</a>
<li><a href="#sec6">6. Bibliography</a>
<li><a href="#secA">Appendix A. OTL class hierarchy</a>
<li><a href="#secB">Appendix B. Error message list</a>
<li><a href="#secC">Appendix C. OTL source code (otl.h)</a>
<li><a href="#secD">Appendix D. Pro*OTL / Pre-Pro*C
preprocessor's source code (ppc.C or ppc.cpp)</a>
<li><a href="#secE">Appendix E. How to install the OTL
library and Pro*OTL/Pre-Pro*C preprocessor</a>
<li><a href="#secF">Appendix F. Modules, generated by PPC for the
example from Chapter 4.</a>
<ul>
<li><a href="#secF1">Pro*C module (ppc_test.pc)</a>
<li><a href="#secF2">C++ module (ppc_test.C)</a>
<li><a href="#secF3">Interface header file (ppc_test.h)</a>
<li><a href="#secF4">Command line for the example from Chapter 4.</a>
</ul>
</ul>
<h2><a name="sec1">1. Introduction</h2>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
<p>
In OTL, a concept of <a href="#sec33">SQL streams</a> is
introduced. The SQL programming interface becomes unified and
homogeneous.
</p>
<p>
OTL has a simplified set of functions, called <a
href="#sec34">Prosto*C</a>. 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.
</p>
<p>
OTL is a database access function library. Also, this document
describes the <a href="#sec4">Pro*OTL / Pre-Pro*C
preprocessor</a> (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.
<p>
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.
</p>
<p>
OTL and PPC compile with the following 32-bit C++ compilers:
</p>
<ul>
<li>IBM AIX, C++ (xlC), 1.x and higher</li>
<li>SunOS/Solaris, Sun C++, 4.x</li>
<li>Unix, GNU C++ (g++), 2.7.x </li>
<li>Windows 95, NT, Visual C++, 4.x, 32-bit</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
The OTL source code resides in <a href="#secC">Appendix C</a>, PPC --
in <a href="#secD">Appendix D</a>. 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 <a
href="mailto:skuchin@gmail.com">skuchin@gmail.com<gmailr <a
href="mailto:skuchin@aceweb.com">skuchin@aceweb.com</a>.
</p>
<h2><a name="sec2">2. Getting started with OTL</h2>
<h2><a name="sec21">2.1. Examples</h2>
<p>
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.
</p>
<h3><a name="sec211">2.1.1. Example 1 (with ordinary, non-template host variables)</h3>
<h4>Source code</h4>
<xmp>
#include <iostream>
using namespace std;
#include <stdio.h>
#include <otl.h>
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<=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>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>=:f and f1<=: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",&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<<"f1="<<f1[k]<<", f2="<<f2[k]<<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){
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
</xmp>
<h3><a name="sec212">2.1.2. Example 2 (with template instantiated host variables)</h3>
<h4>Source code</h4>
<xmp>
#include <iostream.h>
#include <stdio.h>
#include <otl.h>
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<BUF_SIZE> f1; // float host array f1
otl_cstring_array<BUF_SIZE,STR_SIZE> 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<=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>0) o.exec(n);
db.commit(); // commit transaction
} /* insert */
void select()
{
otl_float_array<BUF_SIZE> f1; // float host array f1
otl_cstring_array<BUF_SIZE,STR_SIZE> 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>=:f and f1<=: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<<"f1="<<f1.v[k]<<", f2="<<f2.v[k]<<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
</xmp>
<h3><a name="sec213">2.1.3. Example 3 (with the extended "parse" function)</h3>
<h4>Source code</h4>
<xmp>
#include <iostream.h>
#include <stdio.h>
#include <otl.h>
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<BUF_SIZE> f1(":f1"); // float host array f1
otl_cstring_array<BUF_SIZE,STR_SIZE> 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)",&f1,&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<=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>0) o.exec(n);
db.commit(); // commit transaction
} /* insert */
void select()
{
otl_float_array<BUF_SIZE> f1; // float host array f1
otl_cstring_array<BUF_SIZE,STR_SIZE> 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>=:f and f1<=:f*2",&f1,&f2,&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<<"f1="<<f1.v[k]<<", f2="<<f2.v[k]<<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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -