OTL stream class

Traditionally, a database API has functions to bind host variables with placeholders in the SQL statement. So, the developer has to declare host arrays in the program, parse the statement, call the bind functions, fill out the input variables, execute the statement, read the output variables, etc. After the cycle is done, again, fill out the input variables, execute the statement, read the output. All that is done automatically in the otl_stream class. The class provides full automation of interation with the database as well as performance. Performance is controlled by a single parameter -- the stream buffer size.

A SQL statement in the otl_stream needs to have at least one placeholder / bind variable. SQL statements without placeholders are referred to as constant SQL statements. and processed differently.

The otl_stream class has the following structure:

class otl_stream {
public:

• General constructor. This constructor creates an otl_stream object and then calls the open() function. There two flavors of the otl_streams:

for Oracle 7/8/9:

     otl_stream(const short arr_size, // stream buffer size
                const char* sqlstm, // SQL statement or anynonymous PL/SQL block
                otl_connect& db, // OTL connect object
                const char* ref_cur_placeholder=0
                  // If the stream returns a referenced cursor,
                  // this parameter is used to specify the name of the 
                  // referenced cursor placeholder.
               );

for ODBC/DB2-CLI:

otl_stream(const short arr_size, // stream buffer size
           const char* sqlstm, // SQL statement or stored procedure call
           otl_connect& db, // connect object
           const int implicit_select=otl_explicit_select
              // If the stream returns a result set via a strored 
              // procedure call, this parameter needs to be set to 
              // otl_implicit_select
          );

OTL 4.0/ODBC and OTL 4.0/DB2-CLI define the following global constant to be used with this constructor:
• otl_explicit_select -- to indicate that if the stream is a SELECT statement then the statement is a simple SELECT.
• otl_implicit_select -- to indicate that the stream is a stored procedure call that returns a result set.

This function opens the SQL statement: the statement gets parsed, all input and output variables get dynamically allocated inside the stream and automatically bound to the placeholders. There are two flavors of the function:
 

For Oracle 7/8/9:

     void open(const short arr_size, // stream buffer size
               const char* sqlstm, // SQL statemnet or anynonymous PL/SQL block
               otl_connect& db, // connect object
               const char* ref_cur_placeholder=0
                  // If the stream returns a referenced cursor,
                  // this parameter is used to specify the name of the 
                  // referenced cursor placeholder.
              );

For ODBC/DB2-CLI:

     void open(const short arr_size, // stream buffer size
               const char* sqlstm, // SQL statemnet or stored procedure call
               otl_connect& db, // connect object
               const int implicit_select=otl_explicit_select
                 // If the stream returns a result set via a strored 
                 // procedure call, this parameter needs to be set to 
                 // otl_implicit_select
              );

• Test if all data has been already read from the stream. This function has the same meaning as the eof() funtion in C++ streams

     int eof(void);

• Flush the stream output buffer. It actually means to execute the SQL statement in bulk as many times as rows entered into the stream output buffer. The stream is automatically flushed when the buffer gets full. This function has the same meaning as the flush() function in C++ streams. Also, if the stream auto-commit flag is set then the stream, after flushing the output buffer, commits the current transaction. For more detail, see the set_commit() function.

     void flush(void);

• OTL/OCI8/9 only. Get the OTL stream type.

 

 

 int get_stream_type(void);

The following global  constants (int's) are defined:
 

• otl_no_stream_type -- stream is not instantiated with any SQL statement yet.
• otl_select_stream_type -- stream is instantiated with a straight SELECT statement.
• otl_inout_stream_type -- stream is instantiated with an anonymous PL/SQL block which has input/output bind variables, possibly, parameters in a stored procedure call.
• otl_refcur_stream_type -- stream is instantiated with a PL/SQL block, which returns a reference cursor. In this type of a PL/SQL block (possibly, a stored procedure call), only input bind variables are allowed. The output of the PL/SQL block is the reference cursor itself.
• otl_constant_sql_type -- special constant that is only used in create_stored_proc_call(), and not returned by get_stream_type(). However, the constant completes this logical group of constants, that represent types of  OTL streams. In the context of create_stored_proc_call(), this constant indicates a call to a stored procedure, which has no parameters, and it needs to be executed via otl_cursor::direct_exec().

• OTL/OCI8/9 only. Create an otl_stream compatible call (string) to a stored procedure by the stored procedure name. This function is static in class and intended for some automation in generating Oracle 8/8i/9i stored procedure calls. The function uses Oracle system data dictionary directly (ALL_ARGUMENTS system view), in order to retrieve information about stored procedure's parameters, and their datatypes. Potentially, the function may raise the OTL defined excepations with the folllowing error codes: 32014, 32015, 32016.


The function is able to handle the following stored procedures/functions:


• stored procedures / functions from PL/SQL packages only
• procedures / functions, whose names are not overloaded in the package
• procedures / functions, with scalar input and/or output parameters
• procedures / functions with scalar input parameters and one output reference cursor:
• in the case of a procedure, the reference cursor should be an OUT or IN/OUT parameter in the procedure
• in the case of a function, the reference cursor should be a function return value.


 static void create_stored_proc_call
 (otl_connect& db, // connect object
  otl_stream& args_strm, // an instance of otl_stream that is external to this
       // function. In other words, an otl_stream variable that needs to
       // be defined externally. The variable is used to instantiate the stream
       // with "SELECT...FROM ALL_ARGUMENTS...", in order for the function to
       // be able to access the Oracle system data dictionary. The stream gets
       // instantiated once, and can be reused in subsequent calls to
       // the function, especially, in a high volume environment.
  char* sql_stm, // output, otl_stream compatible SQL statement, which has
                 // a call to the stored procedure, with all of the stored
                 // proc's parameters expanded.
  int& stm_type, // output, stream/statement type
  char* refcur_placeholder, // output, in case of a stored procedure, returnig a
       // a reference cursor, this parameter returns a "reference cursor
       // placeholder" name, that can be used in otl_stream::otl_stream(), or
       // in otl_stream::open() calls.
  const char* proc_name, // stored procedure name. this should a stored
                         // procedure from a PL/SQL package
  const char* package_name, // PL/SQL package name, which the stored procedure
                              // belongs to
  const char* schema_name=0, // Oracle schema name, which the stored procedure
                             // and/or the PL/SQL package belong to
  const bool schema_name_included=false, // indicator of whether the call to
                                         // the stored procedure needs to be
                                         // prefixed with the schema name or not
  const int varchar_size=2001, // VARCHAR parameters in stored procedure
                               // don't have any sizes. This parameter defines
                               // what size needs to be used in the definitions
                               // of :var<char[XXX] bind variables.
  const int all_num2type=otl_var_double // This parameter defines how NUMBER
                                        // parameters of the stored procedure
                                        // will mapped to the corresponding
                                        // bind variable definitions.
);
 

• Clean up the stream output buffer without flushing it. The clean_up_error_flag parameter, if set to 1, cleans up the otl_stream's internal error flag that usually gets set when the stream throws an otl_exception.See examples 65, 66, 67 for more detail on this parameter. The main purpose for introducing this parameter was to provide the stream a capability to recover from adatabase error without closing out the stream.

     void clean(const int clean_up_error_flag=0);

• Rewind the stream. If the stream does not have any input variables, this function forces the stream to execute its SQL statement.

     void rewind(void);

• Test if NULL was fetched from the stream

     int is_null(void);

• Set the LOB stream mode. This function sets the "lob stream mode" flag in the otl_stream, that is, this tells the otl_stream that otl_lob_stream operations will be used. It is not required that this function be used in case of OTL/OCI8. For OTL/ODBC and OTL/DB2-CLI, it is required. However, for writing portable [across multiple database] code, the function may be called.

     void set_lob_stream_mode(const bool mode=false);

• Get the ROWS PROCESSED COUNT (RPC). The count is defined for INSERT, UPDATE, DELETE statements only, and it shows how rows have been processed in the last execution of the statement. For INSERT statements, it may beless or equal the stream buffer size.For DELETE or UPDATE statements, it may be anything, depending upon what how may rows are being updated or deleted.

     long get_rpc(void);

• Set the stream auto-commit flag. When the output buffer is flushed, the current transaction is automatically commited, if the flag is set. By default, the flag is set. In order to prevent the current transaction from "auto-committing", unset the flag using this function. The stream auto-commit flag has nothing to do with the database auto-commit mode. The auto-commit is specific to the otl_stream class.

 

 
 
 

If it is more convenient to have the stream "auto-commit off" by default, then the otl_nocommit_stream can be used. otl_nocommit_stream is a class derived directly from otl_stream with auto-commit turned off by default, so it does not commit transactions.

     void set_commit(int auto_commit=0);

• Set the stream's auto-flush flag. Default value is true. By default, the stream's destructor tries to flush the buffer, if the buffer is dirty. It is called auto-flushing. The auto-flushing can be turned off or on explicitly with the help of the set_flush() function (see below). If the auto-flush flag was turned off, the stream's buffer needs to be flushed either by calling the otl_stream::close() function or the otl_stream::flush() function, because the destructor would not flush even if the dirty flag is true. This function could be especially useful, when OTL is used in the environment with exceptions get thrown left and right, to prevent the otl_stream's destructor from auto-flushing the buffer in the stack unwinding.

     void set_flush(const bool auto_flush=true);

• A group of functions for describing otl_stream's bind variables, both input and output. The functions return a pointer to the otl_var_desc structure:

     class otl_var_desc{
       public:
         int  param_type; // 0 - IN variable, 1 - OUT variable, 2 - INOUT variable
         int  ftype; // see the OTL codes for mapped datatypes
         int  elem_size; // [array] element size in bytes.
         int  array_size; // array size, in case if the variable is scalar, the size
                          // is equal to 1
         int  pos;        // In SELECT statements, pos shows a relative position
                          // of the output column: 1,2,3,...
         int  name_pos;   // In case if the variable is defined via the placeholder
                          // notation (:var<...>), name_pos shows a relative position
                          // of the variable in the arrays of varaibles: 0,1,2,...
         char name[128]; // First 127 bytes of the variable name, in case if the 
                         // variable was defined as a placeholder.
         int  pl_tab_flag; // In OTL/OCIx, this field is equal to 1 in case if the 
                           // variable is defined as a PL/SQL table, 0 - otherwise.
       };

•  Describe OUT variables. desc_len returns the size of the array of otl_var_desc structures. The function returns a pointer to the array of OUT variable descriptors. In case if the SQL statement does not contain any output variables, the functions returns 0. If a variable was declared as INOUT, it is presented as part of the array of the variable descriptors, returned by this function.

 

 
 
 

OUT variables are the variables that get read FROM the stream.

             otl_var_desc* describe_out_vars(int& desc_len);

•  Describe IN variables. desc_len returns the size of the array of otl_var_desc structures. The function returns a pointer to the array of IN variable descriptors. In case if the SQL statement does not contain any input variables, the functions returns 0. If a variable was declared as INOUT, it is presented as part of the array of the variable descriptors, returned by this function.

IN variables are the variables that get written TO the stream.

             otl_var_desc* describe_in_vars(int& desc_len);

•  Describe next output variable. Next means "next to be read from the stream." That is, before calling one of otl_stream::operator<<(), sometimes it is necessary to know what is the type of the next variable to be read. In case if the stream does not have any output variables, the function returns 0.

             otl_var_desc* describe_next_out_var(void);

•  Describe next input variable. Next means "next to be written to the stream." That is, before calling one of otl_stream::operator>>(), sometimes it is necessary to know what is the type of the next variable to be written. In case if the stream does not have any input variables, the function returns 0.

             otl_var_desc* describe_next_in_var(void);

• Close the stream. This function has the same meaning as the close() function in C++ streams. The close() function has two implemenations: the ordinary one (1), and the extended one (2), under #define OTL_STREAM_POOLING_ON.

     (1) void close(void);

save_in_stream_pool is an initialized parameter in the function. When it's set to true, and if #define OTL_STREAM_POOLING_ON is on, the stream doesn't really get closed. The stream gets saved to the pool of unused OTL streams, which can be reused later, when a similar stream (SQL statement + buffer size) gets opened again. The maximum size of the OTL stream pool can be set by otl_connect::set_stream_pool_size().

When the save_in_stream_pool parameter is set to false, the stream DOES get closed, and doesn't get saved in any stream pool. This setting of the parameter can be used to override the default behavior of the otl_stream under #define OTL_STREAM_POOLING_ON. For example, a stream with huge SQL statement and big buffers, which would be are a drag of the system resources, and would need to be deallocated as soon as the use of the stream is finished.

For more detail, see examples 113, 114, 115.

#ifdef OTL_STREAM_POOLING_ON
     (2) void close(const bool save_in_stream_pool=true);
#endif

• Test if the stream is open. This function has the same meaning as the good() function in C++ streams.

     int good(void);

• Describe the output column list in:

• straight SELECT statement (OCIx, ODBC, and DB2-CLI)
• Referenced cursor (OCIx)
• Result set returned via a stored procedure call (ODBC for MS SQL Server and Sybase, DB2-CLI for DB2)
This function returns a pointer of the otl_column_desc type to the descriptor of the output column list:

     class otl_column_desc{
     public:
       char name[512]; // column name
       int  dbtype; // database dependent, column datatype code.
                    // for more detail, see the OCIx and the ODBC manuals.
       int  otl_var_dbtype; // OTL defined, column datatype code
       int  dbsize; // column length
       int  scale; // for numeric columns, column scale
       int  prec; // for numeric columns, column precision
       int  nullok; // indicator whether column is nullable or not
     };

OTL 4.0 defines the following datatypes which the OTL maps the native database datatypes to:
• otl_var_char -- null terminated string (code=1)
• otl_var_double -- 8-byte floating point number (code=2)
• otl_var_float -- 4-byte floating point number (code=3)
• otl_var_int -- 32-bit signed integer (code=4)
• otl_var_unsigned_int -- 32-bit unsigned integer (code=5)
• otl_var_short -- 16-bit signed integer (code=6)
• otl_var_long_int -- 32-signed integer 9code=7)
• otl_var_timestamp -- datatype that is mapped into TIMESTAMP_STRUCT, ODBC and DB2-CLI only (code=8)
• otl_var_varchar_long -- datatype that is mapped into LONG in Oracle 7/8, TEXT in MS SQL Server and Sybase, CLOB in DB2 (code=9),
• otl_var_raw_long -- datatype that is mapped into LONG RAW in Oracle 7/8, IMAGE in MS SQL Server ad Sybase, BLOB in DB2 (code=10)
• otl_var_clob -- datatype that is mapped into CLOB in Oracle 8 (code=11)
• otl_var_blob -- datatype that is mapped into BLOB in Oracle 8 (code=12)
Here is the function prototype:

     otl_column_desc* describe_select(int& desc_len);

Besides the pointer to be returned, the function has an output parameter: desc_len. The length of the output column list is returned via this parameter. The pointer points to an internal structure inside the stream which gets deallocated at the moment of the stream desctruction, so the user does not need to do any memory deallocation operations with the pointer.

• Read objects from the 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); // read the LOB from the stream
     otl_stream& operator>>(TIMESTAMP_STRUCT& s); // read the timestamp from the stream
                                                  // (OTL 4.0/ODBC and OTL 4.0/DB2-CLI 
                                                  //  only)
     otl_stream& operator>>(otl_datetime& dt); // read date/time info from the stream
     otl_stream& operator>>(otl_XXX_tab<…>& tab); // read PL/SQL tables from the stream (OCIx)
     otl_stream& operator>>(otl_lob_stream& lob); 
                         // read reference to CLOB/BLOB from otl_stream 
                         // into otl_lob_stream (OCI8). In other words,
                         // initialize otl_lob_stream for reading CLOB/BLOB 
                         // in stream mode

     otl_stream& operator>>(otl_refcur_stream& refcur);
                         // read a reference cursor descriptor to a variable of 
                         // the otl_refcur_stream type. That is, initialize 
                         // otl_refcur_stream for reading rows from the
                         // reference cursor.

     otl_stream& operator>>(std::string& s); // read the ANSI C++ std::string

• Write objects into the 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 otl_null n); // write NULL into the stream
     otl_stream& operator<<(const otl_long_string& d); // write the LOB into the stream
     otl_stream& operator<<(const TIMESTAMP_STRUCT& d); // write the timestamp into the stream
                                                        // (OTL 4.0/ODBC and OTL 4.0/DB2-CLI only)
     otl_stream& operator<<(const otl_datetime& dt); // write date/time info into the stream
     otl_stream& operator<<(const otl_XXX_tab<…>& tab); // read PL/SQL tables from the stream (OCIx)
     otl_stream& operator<<(otl_lob_stream& lob); 
                         // write otl_lob_stream descriptor intoto otl_stream (OCI8).
                         // In other words, initialize otl_lob_stream 
                         // for writing CLOB/BLOB in stream mode.


     otl_stream& operator<<(const std::string& s); // write the ANSI C++ std::string

OTL 4.0 defines a dummy class to allow NULLs to be written into the stream:

         class otl_null{
         public:
           otl_null(){}
           ~otl_null(){}
         };

OTL 4.0 also defines the otl_datetime class to allow date/time information to written into / read from the stream:

• Set data types of a group of SELECT output columns. This function can override data types of column groups: all numeric columns to string, all date columns to string, or the combination of both. amask parameter can be set to the following values:
• otl_all_num2str, e.g.: set_all_column_types(otl_all_num2str);
• otl_all_date2str, e.g.: set_all_column_types(otl_all_date2str);
• otl_all_num2str | otl_all_date2str, e.g.: set_all_column_types(otl_all_num2str | otl_all_date2str);

void set_all_column_types(const unsigned int amask=0);

• Set a SELECT output column datatype. In other words, override the default mapping of output column datatypes.

     void set_column_type(const int column_ndx,
                          const int col_type,
                          const int col_size=0);

column_ndx is the relative index of the columns in the query: 1,2,3...

col_type is one of the datatype constants, defined by OTL.

col_size is the size, associated with the new datatype of the column. It has be to specified for the otl_var_char type only. Sizess of all numeric types are calculated.

This function can be called for straight SELECT statements (both Oracle and ODBC), referenced cursor SELECT statements (Oracle), and implicit SELECT statements / result sets (ODBC for MS SQL Server and Sybase).

The usability of this function is limited by the following datatype compatibility matrix.
 

 

Database  datatype	Default datatype	Datatype override
NUMBER (Oracle)	otl_var_double	otl_var_char, otl_var_int, otl_var_float, otl_var_short, otl_var_unsigned_int
NUMERIC, FLOAT, REAL, MONEY, DECIMAL (MS SQL Server, Sybase, DB2)	otl_var_double	otl_var_char, otl_var_int, otl_var_float, otl_var_short, otl_var_unsigned_int, otl_var_long_int
INT (MS SQL Server, Sybase, DB2)	otl_var_int	otl_var_char, otl_var_double, otl_var_float, otl_var_short, otl_var_unsigned_int, otl_var_long_int
SMALLINT, TINYINT (MS SQL Server, Sybase, DB2)	otl_var_short	otl_var_char, otl_var_int, otl_var_float, otl_var_double, otl_var_unsigned_int, otl_var_long_int
DATE (Oracle), DATETIME (MS SQL Server, Sybase)	otl_timestamp	otl_var_char
LONG (Oracle)	otl_var_varchar_long	otl_var_char (<=32000 bytes)
TEXT (MS SQL Server, Sybase)	otl_var_varchar_long	otl_var_char(<= max. size of varchar, e.g. <=8000 in MS SQL 7.0)

It is recommended to use this function and datatype overrides with caution. This feature is introduced to address issues like: NUMBER is too large to fit into the otl_var_double container and it is necessary to convert the NUMBER into otl_var_char. Or, for small enough LONG or TEXT columns, sometimes it is more convenient to use the otl_var_char container.
 

}; // end of otl_stream

Copyright © 1996, 2001, Sergei Kuchin, email: skuchin@sprynet.com, kuchin@hotmail.com .

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.