| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Announcement: Oracle Call Interface Template Library (OTL)
Announcement: Oracle Call Interface Template Library (OTL)
Check out the following Web page:
http://home.sprynet.com/sprynet/skuchin/otl.htm
Table of Contents
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:
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
);
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
);
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
);
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_object
|
+------>otl_connect
|
+------>otl_cursor
|
+------>otl_select_cursor
| |
| +------>otl_select_stream
|
|
| otl_stream
+------>otl_out_stream
|
+------>otl_inout_stream
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
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Sun Mar 02 1997 - 00:00:00 CST
 |
 |