Very Computer

by **Sergei Kuchi** » Wed, 05 Mar 1997 04:00:00

Announcement: Oracle Call Interface Template Library (OTL)

Check out the following Web page:

http://home.sprynet.com/sprynet/skuchin/otl.htm

===============================================================
Table of Contents

1. Introduction
2. SQL Streams
3. Exception handling
4. Prosto*C
5. Class hierarchy

1. Introduction
===============

This document is a short introduction into the Oracle Call
Interface Template Library.

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.

OTL has a simplified set of functions, called
Prosto*C. It provides basic set of functions, such as
connect/disconnect, printf/scanf, commit/rollback, etc.

OTL compiles 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;

2. SQL streams
==============

OTL introduces the concept of SQL streams. The idea here is
to combine streams and SQL. Any SQL statement or PL/SQL
block can be treated as a functional element (see the
picture) with input/output parameters. There are functions
to put objects into a stream, that is, to assign values to
input variables of the SQL statement. Also, there are
functions to get objects from the stream, that is, to get
values from output variables of the SQL statement.

+--> I1 I2 ... In
| | | |
| V V V
| +------------------+
+--| SQL statement or |
| PL/SQL block |
+-+-----+------+---+
| | |
V V V
O1 O2 ... Ok

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.

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 has a flush function for flushing its input
buffer when the buffer gets full and a collection of >> and
<< operators for reading and writing object 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 the application developer
needs to remember just a few syntactical constructs and
function names which he already got familiar with when he
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.

Example 1
=========

otl_stream s(50, // fetch 50 rows per one fetch operation
"select state_name, state_code "
"from state "
"where state_name like :v1<char[33]>",
db // database connect
);
char name[33];
int code;

s<<"M%"; // assign value "M%" to :v1
while(!s.eof()){ // read while not "end-of-data"
s>>name>>code; // reading one row
cout<<"State="<<name<<", Code="<<code<<endl;
}

Example 2
=========

otl_stream o(10, // insert in batches of 10 rows
"insert into test_tab values(:f1<int>,:f2<int>)",
db // database connect
);

for(int i=1;i<=100;++i)
o<<i<<i+1; // insert <i,i+1> row

Example 3
=========

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;
}

3. Exception handling
=====================

The library has a mechanism of raising and handling
exceptions from Oracle operations. The exception handling
mechanism was implemented on top of regular C++ exceptions
via the otl_exception class.

Example
=======

try{

otl_stream s(50, // fetch 50 rows per one fetch operation
"select state_name, state_code "
"from state "
"where state_name like :v1<char[33]>",
db
);
char name[33];
int code;

s<<"M%";
while(!s.eof)){
s>>name>>code;
cout<<"State="<<name<<", Code="<<code<<endl;
}

}catch(otl_exception& p){ // intercept exception

cerr<<p.msg<<endl; // print out error messgae

}

4. Prosto*C
===========

The name Prosto*C is originated in the author's native
language -- "prosto" means "simple". Prosto*C provides a
simplified set of procedures for interfacing with SQL or
PL/SQL. The set of procedures is very similar to the C
"stdio" interface: scanf(), printf(), etc.

Example
=======

otl_stream* s=otl_stream_open( // open stream
50, // buffer size
"select state_name,state_code "
"from state "
"where state_name like :v1<char[33]>",
db //connect object
);
char name[33];
int code;

otl_printf(s,"%s","M%"); // :v1 = "M%"
while(!otl_eof(s)){
otl_scanf(s,"%s %d",name,&code); // fetch one row
printf("State=%s, Code=%d\n",name,code);
}
otl_stream_close(s); // close stream

In Prosto*C, the mechanism of handling errors is slightly
different from the otl_exception mechanism. Each connect
object is supplied with the error handler -- a procedure,
which is invoked each time when an error occurs. Error
message and code are passed to the handler.

5. Class hierarchy
==================

OTL falls into a few parts: host variable template classes,
Oracle Call Interface wrapper and SQL stream classes.

Ordinary classes
================

otl_exception
|
+---->otl_err_info

Template classes
================

otl_generic_variable
|
+-->otl_dynamic_variable
|
+-->otl_variable
| |
| +--->otl_cstring, otl_varchar2, otl_long, otl_varchar
| otl_varraw, otl_raw, otl_long_raw, otl_char,
| otl_charz, otl_long_varchar, otl_long_varraw
|
+->otl_array
|
+---->otl_date_array, otl_rowid_array, otl_varnum_array,
otl_number_array, otl_double_array, otl_float_array
otl_signed_char_array, otl_short_int_array, otl_int_array
otl_long_int_array, otl_unsigned_array, otl_cstring_array
otl_varchar2_array, otl_long_array, otl_varchar_array
otl_varraw_array, otl_raw_array, otl_long_raw_array
otl_char_array, otl_charz_array