Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com
This document describes the ODBC Template Library, Version 2.1 (OTL). The library introduces a one-page ODBC API in the form of SQL streams.
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 inline functions. There is no need to link additional object modules 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 automatically uses ODBC bulk operations (SQL streams are buffered by definition) and has a higher runtime performance than scalar operations. Runtime performance can be improved by increasing the stream buffer size.
OTL provides an exception handling mechanism in the format of the otl_exception class. This mechanism takes advantage of C++ exception handling 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 straight ODBC code or even with the MFC database classes.
Also, OTL integrates with the Standard Template Library by means of STL-compliant iterator classes. This feature is very powerful because it allows to combine the power of generic programming and a database backend into compact, reliable, top performance and easy-to-maintain C++ database applications.
This version of OTL available for ODBC drivers, versions 2.5 or higher. It was tested at least with ODBC drivers for MS SQL Server 6.5/7.0 and for Oracle 8.x. Examples for both Oracle 8.x and MS SQL Server can found in chapter 2 of this manual.
The fact that OTL is available in source code adds more value to this material -- folks new to ODBC can use the OTL source code to learn it
Besides the OTL for ODBC, there is two versions of OTL for Oracle 7 and Oracle 8. They use native Oracle APIs: OCI7 and OCI8. OTL ODBC was designed with Oracle OTL in mind, to keep OTL applications portable across databases.
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 to intercept the exception and prevent the program from aborting, wrap up the OTL stream code with the corresponding try & catch block.
The following cursor types are available:
Both styles are case insensitive
OTL introduces the otl_stream class which is the actual implementation of the SQL Stream concept. It is unified for any SQL statement or stored procedure call which potentially may have input/output bind variables (placeholders).
An OTL stream needs to have at least one bind variable. See 2.4. for more information on how to execute a constant SQL statement or stored procedure call.
The following format specifiers are supported:
In OTL, placeholder names represent positions starting with :1, :2, etc.
The following data types for extneded placeholder declarations are available:
For stored procedure calls, special qualifiers are introduced to distinguish between input and output variables:
Here is some examples:
{
call :1<int,out> := my_func(:2<float,in>,
:3<int,inout>,
:4<char[32],out>
)
}
Invoke the my_func function; return the function result into the :1 variable; the function has three parameters: :1 (input), :2 (iput/output), :3 (output)
select * from tab1 where f1 > :1<double>
Select all columns from the tab1 table where f1 is greater than :1
insert into tab1 values( :1<double>, :2<char[32]>, :3<int> )
Insert row { :1(double), :2(string), :3(integer) } into the tab1 table.
In the extended placeholder declaration, spaces in the data type section and in the access qualifier section ARE NOT allowed. The following code is invalid:
This class represents a data container for SQL_LONGVARCHAR and SQL_LONGVARBINARY datatypes. These two datatypes are defined in ODBC to map them to the TEXT/IMAGE datatypes in MS SQL Server 6.5/7.0, LONG/LONG RAW and CLOB/LOB in Oracle 8.x. Respectively, OTL has varchar_long and raw_long datatypes for defining extended placeholders which get bound to the SQL_LONGVARCHAR/SQL_LONGVARBINARY table columns.
class otl_long_string{
};
Before writing the string to a stream, a dynamic length of the string needs to be set by calling set_len() function. When reading the string from a stream, the dynamic length field is updated with an actual string length. It is not guaranteed that for "varchar_long" on the output otl_long_string is null terminated: always check the length of the string. Besides, before opening a stream with such columns the otl_connect::set_max_long_size() function needs to be called in order to set the maximum size of long columns for the connect object. See example 7a for more detail.
enum{ disabled, enabled };
otl_cursor is one of OTL internal classes. There is another format of the direct_exec function call:
All examples below assume Oracle 8.x or SQL Server 6.5/7.0 ODBC drivers as their data sources.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:1<float>,:2<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;
}
}void select()
{ otl_stream i(50, // buffer size "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
char f2[31];
i<<8<<8; // assigning :1 = 8, :2 = 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<<4; // assigning :1 = 4, :2 = 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;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB 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 int, f2 varchar(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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
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
This example works with Oracle 8.x ODBC drivers only. Check out example 2b to see how to call a stored procedure in an MS SQL Server 6.5/7.0.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid stored_proc()
// invoking stored procedure { otl_stream o(1, // buffer size should be equal to 1 in case of stored procedure call "{call my_proc(" " :1<int,inout>, " " :2<char[31],out>, " " :3<char[31],in> " ")}",
// stored procedure call db // connect object ); o<<1<<"Test String1"; // assigning :1 = 1, :3 = "Test String1" int a;
char b[31];
o>>a>>b;
cout<<"A="<<a<<", B="<<b<<endl;
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("uid=scott;pwd=tiger;dsn=my_db"); // connect to data source MY_DB otl_cursor::direct_exec ( db, "CREATE OR REPLACE PROCEDURE my_proc " " (A IN OUT NUMBER, " " B OUT VARCHAR2, " " C IN VARCHAR2) " "IS " "BEGIN " " A := A+1; " " B := C; " "END;" ); // create stored procedure stored_proc(); // invoking stored procedure } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
A=2, B=Test String1
This example works with Oracle 8.x ODBC drivers only. Check out example 2a to see how to call a stored procedure in Oracle 8.x
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid stored_proc()
// invoking stored procedure { otl_stream o(1, // buffer size should be equal to 1 in case of stored procedure call "{call my_proc(" " :1<int,inout>, " " :2<char[31],out>, " " :3<char[31],in> " ")}",
// stored procedure call db // connect object ); o<<1<<"Test String1"; // assigning :1 = 1, :3 = "Test String1" int a;
char b[31];
o>>a>>b;
cout<<"A="<<a<<", B="<<b<<endl;
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("uid=sa;pwd=;dsn=mssql"); // connect to data source MSSQL otl_cursor::direct_exec ( db, "DROP PROCEDURE my_proc",
0 // ignore any errors ); // drop stored procedure otl_cursor::direct_exec ( db, "CREATE PROCEDURE my_proc " " @A int out, " " @B varchar(60) out, " " @C varchar(60) " "AS " "BEGIN " " SELECT @A=@A+1" " SELECT @B=@C " "END" ); // create stored procedure stored_proc(); // invoking stored procedure } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
A=2, B=Test String1
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:1<int>,:2<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 } }void select()
{ otl_stream i(50, // buffer size "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
char f2[31];
i<<8<<8; // assigning :1 = 8, :2 = 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<<4; // assigning :1 = 4, :2 = 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;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=SCOTT;PWD=TIGER;DSN=MY_DB"); // connect to data source MY_DB 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 int, f2 varchar(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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
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
This example works with Oracle 8.x ODBC drivers only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(3, // buffer size "insert into test_tab2 values(:1<float>,:2<timestamp>)",
// SQL statement db // connect object ); TIMESTAMP_STRUCT tm; for(int i=1;i<=10;++i){
tm.year=1998;
tm.month=10;
tm.day=19;
tm.hour=23;
tm.minute=12;
tm.second=12;
tm.fraction=0;
o<<(float)i<<tm;
}
}void select()
{ otl_stream i(3, // buffer size "select * from test_tab2 where f2=:1<timestamp>",
// SELECT statement db // connect object ); // create select stream int f1;
TIMESTAMP_STRUCT tm,f2;
tm.year=1998;
tm.month=10;
tm.day=19;
tm.hour=23;
tm.minute=12;
tm.second=12;
tm.fraction=0;
i<<tm; // assigning :1 = tm // 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.month<<"/"<<f2.day<<"/" <<f2.year<<" "<<f2.hour<<":"<<f2.minute<<":" <<f2.second<<"."<<f2.fraction
<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB otl_cursor::direct_exec ( db, "drop table test_tab2",
otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab2(f1 int, f2 date)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=1, f2=10/19/1998 23:12:12.0 f1=2, f2=10/19/1998 23:12:12.0 f1=3, f2=10/19/1998 23:12:12.0 f1=4, f2=10/19/1998 23:12:12.0 f1=5, f2=10/19/1998 23:12:12.0 f1=6, f2=10/19/1998 23:12:12.0 f1=7, f2=10/19/1998 23:12:12.0 f1=8, f2=10/19/1998 23:12:12.0 f1=9, f2=10/19/1998 23:12:12.0 f1=10, f2=10/19/1998 23:12:12.0
This example works with MS SQL Server 6.5/7.0 only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(3, // buffer size "insert into test_tab2 values(:1<float>,:2<timestamp>)",
// SQL statement db // connect object ); TIMESTAMP_STRUCT tm; for(int i=1;i<=10;++i){
tm.year=1998;
tm.month=10;
tm.day=19;
tm.hour=23;
tm.minute=12;
tm.second=12;
tm.fraction=0;
o<<(float)i<<tm;
}
}void select()
{ otl_stream i(3, // buffer size "select * from test_tab2 where f2=:1<timestamp>",
// SELECT statement db // connect object ); // create select stream int f1;
TIMESTAMP_STRUCT tm,f2;
tm.year=1998;
tm.month=10;
tm.day=19;
tm.hour=23;
tm.minute=12;
tm.second=12;
tm.fraction=0;
i<<tm; // assigning :1 = tm // 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.month<<"/"<<f2.day<<"/" <<f2.year<<" "<<f2.hour<<":"<<f2.minute<<":" <<f2.second<<"."<<f2.fraction
<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=sa;PWD=;DSN=mssql"); // connect to data source MSSQL otl_cursor::direct_exec ( db, "drop table test_tab2",
otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab2(f1 int, f2 datetime)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=1, f2=10/19/1998 23:12:12.0 f1=2, f2=10/19/1998 23:12:12.0 f1=3, f2=10/19/1998 23:12:12.0 f1=4, f2=10/19/1998 23:12:12.0 f1=5, f2=10/19/1998 23:12:12.0 f1=6, f2=10/19/1998 23:12:12.0 f1=7, f2=10/19/1998 23:12:12.0 f1=8, f2=10/19/1998 23:12:12.0 f1=9, f2=10/19/1998 23:12:12.0 f1=10, f2=10/19/1998 23:12:12.0
#define USE_MFC // needs to be defined if CTime and MFC are used #include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(3, // buffer size "insert into test_tab2 values(:1<float>,:2<timestamp>)",
// SQL statement db // connect object ); CTime tm=CTime(1998,10,19,23,12,12); for(int i=1;i<=10;++i){
o<<(float)i<<tm;
}
}void select()
{ otl_stream i(3, // buffer size "select * from test_tab2 where f2=:1<timestamp>",
// SELECT statement db // connect object ); // create select stream int f1;
CTime tm=CTime(1998,10,19,23,12,12);
CTime f2;
i<<tm; // assigning :1 = tm // 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.GetMonth()<<"/"<<f2.GetDay()<<"/" <<f2.GetYear()<<" "<<f2.GetHour()<<":"<<f2.GetMinute()<<":" <<f2.GetSecond() <<endl; } }int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB otl_cursor::direct_exec ( db, "drop table test_tab2",
otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab2(f1 int, f2 date)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=1, f2=10/19/1998 23:12:12 f1=2, f2=10/19/1998 23:12:12 f1=3, f2=10/19/1998 23:12:12 f1=4, f2=10/19/1998 23:12:12 f1=5, f2=10/19/1998 23:12:12 f1=6, f2=10/19/1998 23:12:12 f1=7, f2=10/19/1998 23:12:12 f1=8, f2=10/19/1998 23:12:12 f1=9, f2=10/19/1998 23:12:12 f1=10, f2=10/19/1998 23:12:12
This example works with MS SQL Server only.
#define USE_MFC // needs to be defined if CTime and MFC are used #include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(3, // buffer size "insert into test_tab2 values(:1<float>,:2<timestamp>)",
// SQL statement db // connect object ); CTime tm=CTime(1998,10,19,23,12,12); for(int i=1;i<=10;++i){
o<<(float)i<<tm;
}
}void select()
{ otl_stream i(3, // buffer size "select * from test_tab2 where f2=:1<timestamp>",
// SELECT statement db // connect object ); // create select stream int f1;
CTime tm=CTime(1998,10,19,23,12,12);
CTime f2;
i<<tm; // assigning :1 = tm // 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.GetMonth()<<"/"<<f2.GetDay()<<"/" <<f2.GetYear()<<" "<<f2.GetHour()<<":"<<f2.GetMinute()<<":" <<f2.GetSecond() <<endl; } }int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=sa;PWD=;DSN=mssql"); // connect to data source MSSQL otl_cursor::direct_exec ( db, "drop table test_tab2",
otl_exception::disabled // disable OTL exceptions ); // drop table otl_cursor::direct_exec ( db, "create table test_tab2(f1 int, f2 datetime)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=1, f2=10/19/1998 23:12:12 f1=2, f2=10/19/1998 23:12:12 f1=3, f2=10/19/1998 23:12:12 f1=4, f2=10/19/1998 23:12:12 f1=5, f2=10/19/1998 23:12:12 f1=6, f2=10/19/1998 23:12:12 f1=7, f2=10/19/1998 23:12:12 f1=8, f2=10/19/1998 23:12:12 f1=9, f2=10/19/1998 23:12:12 f1=10, f2=10/19/1998 23:12:12
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:1<float>,:2<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;
}
}void select()
{ otl_stream i(50, // buffer size "{call my_proc(:1<int,in>)}",
// implicit SELECT statement db, // connect object otl_implicit_select // implicit SELECT statement ); // create select stream int f1;
char f2[31];
i<<8; // assigning :1 = 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 :1 = 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;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=sa;PWD=;DSN=mssql"); // connect to data source MSSQL 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 int, f2 varchar(30))" ); // create table otl_cursor::direct_exec ( db, "DROP PROCEDURE my_proc",
0 // ignore any errors ); // drop stored procedure otl_cursor::direct_exec ( db, "CREATE PROCEDURE my_proc " " @F1 int " "AS " "SELECT * FROM test_tab " "WHERE f1>=@F1 AND f1<=@F1*2 " ); // create stored procedure 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
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
This example works with SQL Server 6.5/7.0 only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<varchar_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=sa;PWD=;DSN=mssql"); // connect to data source MSSQL 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 int, f2 text)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
This example works with SQL Server 6.5/7.0 only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<raw_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=sa;PWD=;DSN=mssql"); // connect to data source MSSQL 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 int, f2 image)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
This example works with Oracle 8.x ODBC drivers only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<varchar_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB 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 int, f2 long)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
This example works with Oracle 8.x ODBC drivers only.
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<raw_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB 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 int, f2 long raw)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<varchar_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB 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 int, f2 clob)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream o(1, // buffer size needs to be set to 1 for long strings "insert into test_tab values(:1<int>,:2<raw_long>)",
// SQL statement db // connect object ); for(int i=1;i<=20;++i){
for(int j=0;j<50000;++j)
f2[j]='*';
f2[50000]='?';
f2.set_len(50001);
o<<i<<f2;
}
}void select()
{ otl_long_string f2(70000); // define long string variable db.set_max_long_size(70000); // set maximum long string size for connect object otl_stream i(1, // buffer size needs to be set to 1 "select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement db // connect object ); // create select stream int f1;
i<<8<<8; // assigning :1 = 8, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
i<<4<<4; // assigning :1 = 4, :2 = 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[0]<<f2[50000]<<", len="<<f2.len()<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize ODBC environment try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // connect to data source MY_DB 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 int, f2 lob)" ); // 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 cerr<<p.code<<endl; // print out error code cerr<<p.sqlstate<<endl; // print out SQLSTATE message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from the data source return 0;
}
f1=8, f2=*?, len=50001 f1=9, f2=*?, len=50001 f1=10, f2=*?, len=50001 f1=11, f2=*?, len=50001 f1=12, f2=*?, len=50001 f1=13, f2=*?, len=50001 f1=14, f2=*?, len=50001 f1=15, f2=*?, len=50001 f1=16, f2=*?, len=50001 f1=4, f2=*?, len=50001 f1=5, f2=*?, len=50001 f1=6, f2=*?, len=50001 f1=7, f2=*?, len=50001 f1=8, f2=*?, len=50001
OTL provides two otl_stream based STL-compliant iterator classes:
These two iterator classes make it possible to combine the power of generic programming and ODBC into compact, reliable, top performance and easy-to-maintain C++ database applications.
#include <iostream.h>
#include <stdio.h>
#include <otl_iter.h>
#include <vector.h>
#include <hash_map.h>
#include <iterator.h>
otl_connect db; // connect object
// row container class
class row{
public:
int f1;
char f2[32];
// default constructor
row(){f1=0; f2[0]=0;}
// destructor
~row(){}
// copy constructor
row(const row& row)
{
f1=row.f1;
strcpy(f2,row.f2);
}
// assignment operator
row& operator=(const row& row)
{
f1=row.f1;
strcpy(f2,row.f2);
return *this;
}
};
// redefined operator>> for reading row& from otl_stream
otl_stream& operator>>(otl_stream& s, row& row)
{
s>>row.f1>>row.f2;
return s;
}
// redefined operator<< for writing row& into otl_stream
otl_stream& operator<<(otl_stream& s, const row& row)
{
s<<row.f1<<row.f2;
return s;
}
// redefined operator<< writing row& into ostream
ostream& operator<<(ostream& s, const row& row)
{
s<<"f1="<<row.f1<<", f2="<<row.f2;
return s;
}
void insert()
// insert rows into table
{
otl_stream o(50, // buffer size
"insert into test_tab values(:1<int>,:2<char[31]>)",
// SQL statement
db // connect object
);
row r; // single row buffer
vector<row> vo; // vector of rows
// populate the vector
for(int i=1;i<=100;++i){
r.f1=i;
sprintf(r.f2,"Name%d",i);
vo.push_back(r);
}
cout<<"vo.size="<<vo.size()<<endl;
// insert vector into table
copy(vo.begin(),
vo.end(),
otl_output_iterator<row>(o)
);
}
void select()
{
otl_stream i(50, // buffer size
"select * from test_tab where f1>=:1<int> and f1<=:2<int>*2",
// SELECT statement
db // connect object
);
// create select stream
vector<int> inp_par; // vector of 2 elements to demonstrate OTL iterators
vector<row> v; // vector of rows
// assigning :1 = 8, :2 = 8
// this example demonstrates how both input
// and output iterators may be attached to the
// same otl_stream
inp_par.push_back(8); // populate the vector with two elements
inp_par.push_back(8); // populate the vector with two elements
cout<<"inp_par.size="<<inp_par.size()<<endl;
// copy the vector into the input variable of the select statement
copy(inp_par.begin(),inp_par.end(),otl_output_iterator<int>(i));
// SELECT automatically executes when all input variables are
// assigned. First portion of out rows is fetched to the buffer
// copy all rows to be fetched into the vector
copy(otl_input_iterator<row,ptrdiff_t>(i),
otl_input_iterator<row,ptrdiff_t>(),
back_inserter(v));
cout<<"Size="<<v.size()<<endl;
// send the vector to cout
copy(v.begin(), v.end(), ostream_iterator<row>(cout, "\n"));
// clean up the vector
v.erase(v.begin(),v.end());
// OTL traditional technique
i<<4<<4; // assigning :1 = 4, :2 = 4
// SELECT automatically executes when all input variables are
// assigned. First portion of out rows is fetched to the buffer
// copy all rows to be fetched to the vector
copy(otl_input_iterator<row,ptrdiff_t>(i),
otl_input_iterator<row,ptrdiff_t>(),
back_inserter(v));
cout<<"Size="<<v.size()<<endl;
// send the vector to cout
copy(v.begin(), v.end(), ostream_iterator<row>(cout, "\n"));
}
int main()
{
otl_connect::otl_initialize(); // initialize ODBC environment
try{
db.rlogon("DSN=MY_DB;PWD=TIGER;UID=SCOTT"); // connect to data source MY_DB
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 int, f2 varchar(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
cerr<<p.code<<endl; // print out error code
cerr<<p.sqlstate<<endl; // print out SQLSTATE message
if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error
}
db.logoff(); // disconnect from the data source
return 0;
}
In order to obtain a copy of the OTL header files,
send email to
Here is the list of the OTL header files:
The header files to be included are as follows:
If you want to use the STL-compliant iterators then you are going to need the Standard Template Library (adaption by Boris Fomitchev) or the original STL SGI, version 3.11.
The following OTL exceptions can be raised by the OTL functions: