# One-Page ODBC 2.5 API, ODBC Template Library (OTL), Version 2.1

Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.comgmail

    Copyright (C) Sergei Kuchin, 1996, 1997, 1998, 1999
    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.

# Table of Contents

- 1. Introduction
- 1.1. SQL streams
- 2. One-page ODBC API
  - 2.1. Connect object
  - 2.2. otl_stream class
    - 2.2.1. Stream bind variables declaration
    - 2.2.2. otl_long_string class
  - 2.3. Exception handling
  - 2.4. Constant SQL statement or stored procedure call
  - 2.5. Examples
    - Example 1 (with otl_stream class)
    - Example 2a (with an Oracle stored procedure call)
    - Example 2b (with an MS SQL Server stored procedure call)
    - Example 3 (with printf/scanf functions)
    - Example 4a (with otl_stream class, TIMESTAMP_STRUCT and Oracle)
    - Example 4b (with otl_stream class, TIMESTAMP_STRUCT and MS SQL Server)
    - Example 5a (with otl_stream class, CTime class and Oracle)
    - Example 5b (with otl_stream class, CTime class and MS SQL Server)
    - Example 6 (implicit SELECT/result set returned by a stored procedure, MS SQL Server)
    - Example 7a (with MS SQL Server's TEXT column)
    - Example 7b (with MS SQL Server's IMAGE column)
    - Example 7c (with Oracle's LONG column)
    - Example 7d (with Oracle's LONG RAW column)
    - Example 7e (with Oracle's CLOB column)
    - Example 7f (with Oracle's LOB column)
- 3. STL-compliant iterators for otl_stream
  - 3.1. Example
- Appendix A. How to download the source code
- Appendix B. OTL exception list

# 1. Introduction

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.

## 1.1. SQL stream classes

OTL introduces the concept of SQL streams. The idea here is to combine streams and SQL. Any SQL statement or stored procedure call can be treated as a functional element 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.

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 stored procedure call is opened as an ordinary buffered file. The logic of he 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 stored procedure call of any kind. This means 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.

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.

# 2. One-page ODBC API

## 2.1. Connect object

This class encapsulates the ODBC connect functions: connect, commit, roll back, etc. In other words, otl_connect is the class for creating "connect" objects.

    class otl_connect{
    public:

- Initialize ODBC program. This function needs to be called only once before connecting to the database for the first time. The function is static in class.

      static void otl_initialize(void);

- Set timeout (in seconds) for the connect object. If timeout is set then any SQL statement may time out.

      void set_timeout(const int atimeout=0);

- Set cursor type for all subsequent SELECT statements open under the connect object

      void set_cursor_type(const int acursor_type=0);

  The following cursor types are available:

  - SQL_CURSOR_FORWARD_ONLY
  - SQL_CURSOR_STATIC
  - SQL_CURSOR_KEYSET_DRIVEN
  - SQL_CURSOR_DYNAMIC
- Set Max Size for operations with LONGVARCHAR and LONGVARBINARY datatypes

      void set_max_long_size(const int amax_size);

- Get Max Size for LONGVARCHAR and LONGVARBINARY datatypes

      int get_max_long_size(void);

- Create "connect" object.

      otl_connect();

- Create "connect" object and connect to data source using the "connect_str" connect string. Both Oracle and MFC styles of connect strings are supported:
  - Oracle: "USERNAME/PASSWORD@DSN"
  - MFC: "DSN=value;UID=value;PWD=value"

  Both styles are case insensitive

       otl_connect(const char* connect_str);

- Destructor

      ~otl_connect();

- Concurrent logon; ODBC application is allowed to have more than one concurrent logon. Returns 1 on success, 0 on failure. The format of connect string is the same as connect(const char* connect_str).

      int rlogon(const char* connect_str);

- Set connect option: "option" is the same as 2nd parameter in SQLSetConnectOption, "param" is the same as 3rd parameter in SQLSetConnectOption. For more detail, see the ODBC programmer's Guide.

       void set_option(UWORD option, UDWORD param);

- Disconnect from / log off data source. Returns 1 on success, 0 on failure.

      int logoff(void);

- Commit current transaction. Returns 1 on success, 0 on failure.

      int commit(void);

- Roll back current transaction. Returns 1 on success, 0 on failure.

      int rollback(void);

    };

## 2.2. otl_stream class

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.

    class otl_stream{
    public:

- General conctructor. SQL statement is parsed, all host input and output host variables are automatically allocated and bound. This constructor allows the user to use extended placeholder declarations.

      otl_stream(
      	   short arr_size, // host array size
      	   const char* sqlstm, // SQL statement
      	   otl_connect& db, // connect object
                 const int implicit_select=otl_explicit_select
                  // "implicit" SELECT indicator. It defaults to "explicit"
                  // SELECT meaning that the SQL statement statement in the stream
                  // is "SELECT ...". "Implicit" SELECT means that a result set
                  // is returned by a stored procedure implicitly.
      	  );

- Default constructor

      otl_stream();

- Destructor

      ~otl_stream();

- Test if all data has been already read from the stream

      int eof(void);

- Flush stream's output buffer. It actually means to execute the SQL statement as many times as rows entered to the output buffer. The stream is automatically flushed when the buffer gets full.

      void flush(void);

- Clean up buffer without flushing it.

      void clean(void);

- Rewind stream

      void rewind(void);

- Test if NULL was fetched from the stream

      int is_null(void);

- Set "auto-commit" flag. When the buffer is flushed, current transaction is automatically commited, if the flag is set. By default, the flag is set. In order to prevent current transaction from "auto-committing", unset the flag using this function.
  This auto-commit flag has nothing to do with the database auto-commit mode. This auto-commit is otl_stream specific. If it is more convenient to have "auto-commit off" by default then it makes sense to derive another stream class from otl_stream and unset the flag in constructors of the derived class.

      void set_commit(int auto_commit=0);

- Open stream

      void open(
      	  short arr_size, // host array size
      	  const char* sqlstm, // SQL statement
      	  otl_connect& db, // connect object
                const int implicit_select=otl_explicit_select
                  // "implicit" SELECT indicator. It defaults to "explicit"
                  // SELECT meaning that the SQL statement statement in the stream
                  // is "SELECT ...". "Implicit" SELECT means that a result set
                  // is returned by a stored procedure implicitly.
      	 );

- Close stream

      void close(void);

- Test if the stream was opened okay

      int good(void);

- Read objects from stream

      otl_stream& operator>>(char& c);
      otl_stream& operator>>(unsigned char& c);
      otl_stream& operator>>(char* s);
      otl_stream& operator>>(unsigned char* s);
      otl_stream& operator>>(int& n);
      otl_stream& operator>>(unsigned& u);
      otl_stream& operator>>(short& sh);
      otl_stream& operator>>(long int& l);
      otl_stream& operator>>(float& f);
      otl_stream& operator>>(double& d);
      otl_stream& operator>>(otl_long_string& s);
      otl_stream& operator>>(TIMESTAMP_STRUCT& t);
      otl_stream& operator>>(CTime& t);

- Write objects into stream

      otl_stream& operator<<(const char c);
      otl_stream& operator<<(const unsigned char c);
      otl_stream& operator<<(const char* s);
      otl_stream& operator<<(const unsigned char* s);
      otl_stream& operator<<(const int n);
      otl_stream& operator<<(const unsigned u);
      otl_stream& operator<<(const short sh);
      otl_stream& operator<<(const long int l);
      otl_stream& operator<<(const float f);
      otl_stream& operator<<(const double d);
      otl_stream& operator<<(const TIMESTAMP_STRUCT& t);
      otl_stream& operator<<(const CTime& t);
      otl_stream& operator<<(const otl_long_string& s);
      otl_stream& operator<<(const otl_null n);
        // write NULL into stream. otl_null is a dummy class
        // which has only a empty default constructor. The only purpose
        // of creating this class was providing a function to write
        // NULL into the database

- C-style printf/scanf functions

      void printf(const char* fmt,...);
      void scanf(const char* fmt,...);

  The following format specifiers are supported:

  - %d -- int
  - %u -- unsigned
  - %ld -- long int
  - %f -- float
  - %lf -- double
  - %c -- char
  - %t -- ODBC timestamp, requires TIMESTAMP_STRUCT or CTime class
  - %s -- string
  - %N -- specifier for writing NULL into streams

    };

## 2.2.1. Stream bind variables declaration

This section explains in detail how to declare bind variables (or extended placeholders) in the SQL streams.

A SQL statement or stored procedure call may have placeholders which are usually connected with the corresponding bind variables in the program. OTL has a small parser which parses a SQL statament or stored procedure call and allocates corresponding bind variables dynamically inside the stream.

In OTL, placeholder names represent positions starting with :1, :2, etc.

The following data types for extneded placeholder declarations are available:

- int
- unsigned
- short
- long -- (long integer)
- float
- double
- timestamp -- ODBC timestamp, requires TIMESTAMP_STRUCT or CTime class
- char[length] (length >= 3 and length <= datasource specific value)
- varchar_long -- corresponds to SQL_LONGVARCHAR (see 2.2.2.)
- raw_long -- corresponds to SQL_LONGVARBINARY (see 2.2.2.)

For stored procedure calls, special qualifiers are introduced to distinguish between input and output variables:

- in -- input variable
- out -- output variable
- inout -- input/output variable

#### Examples

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:

      insert into tab1 values(:1< double >, :2< char [ 32 ] > , :3< int>);
      :1< int, out > := ...;

## 2.2.2. otl_long_string class

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{

- Class constructor. It allocates a block of memory according to the specified size str_size.

       otl_long_string(const int str_size=32760)

- Class destructor

       ~otl_long_string()

- Set string's dynamic length

      void set_len(const int len=0)

- Get string's dynamic length

       int len()

- [] operator for accessing individual elements of the long string.

       unsigned char& operator[](int ndx)

    };

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.

## 2.3. Exception handling

In case of database failure or inconsistent use of SQL streams, exceptions of the otl_exception type are raised by the library functions. The main advantage of using this exception handling mechanism is that exceptions can be processed in one catch block, instead of checking return codes from every library function call.

    class otl_exception{
    public:

- This enum defines two constants which may be used in the direct_exec function

      enum{ disabled, enabled };

- Create exception out of either environment or database or statement handle

      otl_exception(HENV henv,HDBC hdbc,HSTMT hstmt,const char* sqlstm=0);

- Create exception from amsg, acode and sqlstm

      otl_exception(const char* amsg,const int acode,const char* sqlstm=0);

- Copy constructor

      otl_exception(const otl_exception& p);

- Default constructor

       otl_exception();

- native error message buffer

      unsigned char msg[1000];

- native error code

      int code;

- SQL statement or stored procedure call that caused the error

       unsigned char* stm_text;

- SQLSTATE message buffer

      unsigned char sqlstate[1000];

    };

### Example

     try{

      otl_stream s(50, // fetch 50 rows per one fetch operation
    	      "select state_name, state_code "
                  "from state "
                   "where state_name like :1<char[33]>",
                   db // connect object
                 );
      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.code<<endl; // print out error code
      cerr<<p.msg<<endl; // print out error message
      cerr<<p.sqlstate<<endl; // print out SQLSTATE
      if(p.stm_text)
       cerr<<p.stm_text<<endl; // print out SQL that caused the error

     }

## 2.4. Constant SQL statement or stored procedure call

SQL statement or stored procedure call is considered to be constant if it does not have any bind variables. OTL has a static (in class) function to execute constant statements or blocks, e.g.

      otl_cursor::direct_exec
       (db, // connect object
        "create table test_tab(f1 numeric, f2 varchar(30))"
        );  // create table

otl_cursor is one of OTL internal classes. There is another format of the direct_exec function call:

      otl_cursor::direct_exec
       (db, // connect object
        "drop table test_tab", // SQL statement or stored procedure call
        otl_exception::disabled // disable OTL exceptions,
                                // in other words, ignore any
                                // database error
       ); // drop table

## 2.5. Examples

All examples below assume Oracle 8.x or SQL Server 6.5/7.0 ODBC drivers as their data sources.

### Example 1 (with otl_stream class)

This example works with both Oracle 8.x and SQL Server 6.5/7.0 without any modifications to the table structure or datatypes.

#### Source code

####

####

####

    #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(: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;

    }

#### Output

    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

### Example 2a (with an Oracle stored procedure call)

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.

#### Source code

####

####

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

    otl_connect db; // connect object

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

#### Output

    A=2, B=Test String1

### Example 2b (with an MS SQL Server stored procedure call)

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

#### Source code

####

####

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

    otl_connect db; // connect object

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

#### Output

    A=2, B=Test String1

### Example 3 (with printf/scanf functions)

This example works with Oracle 8.x and SQL Server 6.5/7.0 without any modifications to the table structure or datatypes.

#### Source code

####

####

####

    #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(: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;

    }

#### Output

    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

### Example 4a (with otl_stream class, TIMESTAMP_STRUCT and Oracle)

This example works with Oracle 8.x ODBC drivers only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 4b (with otl_stream class, TIMESTAMP_STRUCT and MS SQL Server)

This example works with MS SQL Server 6.5/7.0 only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 5a (with otl_stream class, CTime class and Oracle)

#### Source code

####

####

####

    #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 object

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

    }

#### Output

    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

### Example 5b (with otl_stream class, CTime class and MS SQL Server)

This example works with MS SQL Server only.

#### Source code

####

####

####

    #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 object

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

    }

#### Output

    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

### Example 6 (implicit SELECT/result set return by a stored procedure)

This example works MS SQL Server 6.5/7.0 only. In order to do the same thing with Oracle via an Oracle ODBC driver, you need the Oracle ODBC driver 8.0.5.x and the Oracle Client 8.0.5.x. In other words, it is possible to return Oracle's normal referenced cursor in the format of an implicit result set. Oracle's stored procedure call would look like this:

      {call my_proc(:1<int,in>,?)}

where ? is used to tell the Oracle ODBC driver 8.0.5.x that the second parameter in the my_proc procedure is the referenced cursor to be returned. The driver allocates the referenced cursor variable inside and returns the fetch sequence in the format of the implicit result set. For more info, refer to the Oracle ODBC driver 8.0.5.x's manual.

#### Source code

####

####

####

    #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(: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;

    }

#### Output

    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

### Example 7a (with MS SQL Server's TEXT column)

This example works with SQL Server 6.5/7.0 only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 7b (with MS SQL Server's IMAGE column)

This example works with SQL Server 6.5/7.0 only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 7c (with Oracle's LONG column)

This example works with Oracle 8.x ODBC drivers only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 7d (with Oracle's LONG RAW column)

This example works with Oracle 8.x ODBC drivers only.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 7e (with Oracle's CLOB column)

This example works with Oracle 8.0.5.x ODBC drivers only and requires the Oracle Client 8.0.5.x as well.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

### Example 7f (with Oracle's LOB column)

This example works with Oracle 8.0.5.x ODBC drivers only and requires the Oracle Client 8.0.5.x as well.

#### Source code

####

####

####

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

    otl_connect db; // connect object

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

    }

#### Output

    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

# 3. STL-compliant iterators for otl_stream

OTL provides two otl_stream based STL-compliant iterator classes:

- otl_output_iterator
- otl_input_iterator

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.

## 3.1. Example

#### Source code

####

####

####

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

    }

#### Output

    vo.size=100
    inp_par.size=2
    Size=9
    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
    Size=5
    f1=4, f2=Name4
    f1=5, f2=Name5
    f1=6, f2=Name6
    f1=7, f2=Name7
    f1=8, f2=Name8

# Appendix A. How to download the source code

In order to obtain a copy of the OTL header files, send email to skuchin@ispwest.com, skuchin@gmail.comgmail

Here is the list of the OTL header files:

- otlodbc.h -- the OTL source code for ODBC 2.5. Save the file as otl.h.
- otl_iter.h -- STL-compliant iterators for otl_stream

Besides, you need to have standard ODBC32 and MFC header files and object libraries. See the corresponding manuals for more detail.

The header files to be included are as follows:

- afx.h
- sql.h
- sqlext.h

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.

# Appendix B. OTL exception list

The following OTL exceptions can be raised by the OTL functions:

Code=32000: Incompatible data types in stream operation
  Cause: The data type of a variable used in the current stream operation is not compatible with the declared stream format.
  Action: Check placeholders and their data types declaration.
Code=32001: Row must be full for flushing output stream
  Cause: Stream is open for output and has a format of output rows. An output row is a tuple of all output variables put together. The current output row is not filled yet but the flush function is invoked. The stream buffer cannot be flushed until the current row of the output buffer is full.
  Action: Fill the row first, then flush the stream.
Code=32004: Not all input variables have been initialized
  Cause: stream has input variables but not all the variables have been initialized. An attempt to read data from the stream was made.
  Action: Assign all the input variables first.
Code=32004: No input variables have been defined in SQL statement
  Cause: Stream has no input variables. An attempt to write objects to the stream via one of the << operators was made.
  Action: Do not call the << operators for streams which have no input variables defined.