# OTL 4.0, OTL stream class

# OTL stream class

The otl_strean class is the actual implemention the OTL stream concept. Any SQL statement, anonymous PL/SQL block or stored procedure with input and/or output parameters can be programmed in C++ with the 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 interaction with the database as well as performance. Performance is controlled by a single parameter -- the stream buffer size. The buffer size is defined in logical rows to inserted into a table, selected from a table / view in one round-trip to the database (a.k.a. batch size, array 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.

Starting with OTL 4.0.115 and on, the buffer size datatype is int (the old datatype was short int). If the old datatype is needed for backward compatibility, #define OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE has to be enabled.

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/10:

         otl_stream(const int 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.
                     const char* sqlstm_label=0
                      // SQL statement label. When specified, it is used
                      // to populate otl_exception::stm_text with, so
                      // the actual text of the SQL statement will not be visible
                      // and will be replaced with its label.
                   );

for ODBC/DB2-CLI:

    otl_stream(const int 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
                const char* sqlstm_label=0
                  // SQL statement label. When specified, it is used
                  // to populate otl_exception::stm_text with, so
                  // the actual text of the SQL statement will not be visible
                  // and will be replaced with its label.
              );

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/10:

         void open(const int 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.
                   const char* sqlstm_label=0
                     // SQL statement label. When specified, it is used
                     // to populate otl_exception::stm_text with, so
                     // the actual text of the SQL statement will not be visible
                     // and will be replaced with its label.
                  );

    For ODBC/DB2-CLI:

         void open(const int 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
                   const char* sqlstm_label=0
                     // SQL statement label. When specified, it is used
                     // to populate otl_exception::stm_text with, so
                     // the actual text of the SQL statement will not be visible
                     // and will be replaced with its label.
                  );

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

- Set the stream buffer size. The only thing that this function does is saves a new buffer size for further use with otl_connect::operator>>(otl_stream&).

         int setBufSize(const int buf_size);

- 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.

OTL/OCI8,8i,9i/10g has a version of the flush function with 2 parameters: row_offset, force_flushing This version of the flush function makes the process of, say, inserting a big batch of rows more efficient, after, say, duplicate rows were discovered in the batch, and an otl_exception was raised. For more detail, see OTL examples.

         void flush(void);

         void flush // OTL/OCI8,8i,9i,10g only
         (const int row_offset=0, // Specify the first row in the
                                  // stream's buffer for flushing
          const bool force_flush=false // Force flushing regardless of
                                       // previous errors
         );

- OTL/OCI8/9/10 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/10 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/10g 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 exceptions with the following 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.

In OTL 4.0.6, and later, the function is extended to work with SELECT statements, explicit (Oracle reference cursors) and implicit (in DB2, MS SQL Server, or Sybase stored procedures) result sets. An unfinished fetch sequence can be canceled by calling the function. Also, the function cleans up the error flags, that get set inside the stream, in the case of, say, a database error.

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

- OTL-stream-to-int conversion operator It returns the !eof() status of the stream. It allows the operators >> to return the !EOF status of the stream, and to be used in a while() loop as follows:

`while(s>>f1>>f2){`
`cout<<"f1="<<f1<<", f2="<<f2<<endl;`
`}`

         operator int(void);

- OTL/ODBC, OTL/DB2-CLI only. If the stream has a running SELECT statement, or a call to a stored procedure that returns a result set, in one worker thread (not the main thread), then the statement can be asynchronously canceled, by calling this function from another worker thread. The actual cancellation of the statement depends on how the SQLCancel() call (ODBC, or DB2-CLI) was implemented in the underlying database API. Also, the error code, that otl_exception gets initialized with, depends on the underlying database API. In some cases, the error code is 0, but an otl_exception gets thrown, right after the SQLCancel call returns.

OTL/OCI8,8i,9i,10g have a similar function but at the level of otl_connect: cancel(). OCI and ODBC/DB2-CLI are different in that regard.

         void cancel(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, or when the otl_stream_read_iterator is used, it is required. However, for writing portable [across multiple database] code, the function should 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, and it shows how rows have been processed in the last execution of the statement. For INSERT statements, it may be less or equal to the stream buffer size.For DELETE or UPDATE statements, it may be anything, depending upon what how may rows are being updated or deleted.

In OTL 4.0.6 and higher, the function was extended to return an accumulative rows processed count for SELECT statements (all databases), reference cursors (Oracle), stored procedures that return implicit result sets.

         long get_rpc(void);

> There is a substantial difference in the way the get_rpc() function works in Oracle and ODBC, or DB2-CLI, in the case of an error, when the underlying INSERT/UPDATE/DELETE statement errored out with an otl_exception. In Oracle, in case of error, get_rpc() returns a number of successfully processed rows, so it's easy to calculate which row caused the error. In ODBC, or DB2-CLI, the behavior of the SQLGetRowCount() function is undefined, so get_rpc() always returns 0.

> Conclusion: for portable, muti-database OTL based code, it's not recommended to use the database specific behavior of the get_rpc() function. For Oracle OTL based code, it's okay to use it, since this kind of behavior is consistent with all versions of Oracle, that are supported by OTL.

- 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.

This function disables ONLY auto-flushing in the otl_stream destructor, not the buffer flushing in general. When the stream buffer gets full, it gets flushed automatically REGARDLESS. If set_flush(false) call was made, it disables the automatic buffer flushing ONLY in the DESTRUCTOR, in order to prevent potential cascading otl_exception's.

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

    -- under OTL_UNICODE and OTL_ORA9I / OTL_ORA10G

           int charset_form;
             // SQLCS_IMPLICIT (1) for on-byte character sets,
             // SQLCS_NCHAR (2) for NLS multi-byte character sets
             // 0 otherwise.
           int char_size; // column size in characters, not in bytes
                          // this column size is set to 0 for OTL_ORA8I,
                          // because the corresponding attribute
                          // is not available in OCI8i

         };

OTL 4.0 defines the following datatypes which the OTL maps the native database datatypes to:

OTL defined integer constant | Integer code | Explanation
otl_var_bigint | 20 | MS SQL Server, DB2, MySQL, PostgreSQL, etc. BIGINT (signed 64-bit integer) type
otl_var_blob | 12 | datatype that is mapped into BLOB in Oracle 8
otl_var_char | 1 | null terminated string
otl_var_clob | 11 | datatype that is mapped into CLOB in Oracle 8
otl_var_db2date | 17 | DB2 DATE type
otl_var_db2time | 16 | DB2 TIME type
otl_var_double | 2 | 8-byte floating point number
otl_var_float | 3 | 4-byte floating point number
otl_var_int | 4 | signed 32-bit integer
otl_var_long_int | 7 | signed 32-bit integer
otl_var_ltz_timestamp | 19 | Oracle 9i/10g TIMESTAMP WITH LOCAL TIME ZONE type
otl_var_raw | 23 | RAW, BINARY, VARBINARY, BYTEA, VARCHAR BYTE, CHAR BYTE, etc.
otl_var_raw_long | 10 | datatype that is mapped into LONG RAW in Oracle 7/8, IMAGE in MS SQL Server ad Sybase, BLOB in DB2
otl_var_short | 6 | signed 16-bit integer
otl_var_timestamp | 8 | datatype that is mapped into TIMESTAMP_STRUCT, ODBC and DB2-CLI only
otl_var_tz_timestamp | 18 | Oracle 9i/10g TIMESTAMP WITH TIME ZONE type
otl_var_unsigned_int | 5 | unsigned 32-bit integer
otl_var_varchar_long | 9 | datatype that is mapped into LONG in Oracle 7/8, TEXT in MS SQL Server and Sybase, CLOB in DB2

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

    #if defined(OTL_UNICODE)

        // OTL_UNICODE is enabled (2-byte Unicode)

         otl_stream& operator>>(unsigned char* s);
           // returns a null terminated array of unsigned short's
           // (double-byte Unicode characters). Therefore, "unsigned char*"
           // needs to be type cast to "unsigned short*". Also, it's recommended
           // to allocate 2 bytes per each Unicode character. For Oracle Unicode,
           // add extra 2 bytes per possible surrogate character.

         otl_stream& operator>>(otl_long_unicode_string& s);
            // read the Unicode LOB from the stream

      #if defined(OTL_UNICODE_CHAR_TYPE)
         otl_stream& operator>>(OTL_UNICODE_CHAR_TYPE& c); // read Unicode character
         otl_stream& operator>>(OTL_UNICODE_CHAR_TYPE* s); // read Unicode string
      #endif

      #if defined(OTL_UNICODE_STRING_TYPE)
         otl_stream& operator>>(OTL_UNICODE_STRING_TYPE& s);
           // read Unicode string into a string class variable. VARCHAR/CHAR can be
           // read as well as Large Text Objects (NTEXT, CLOB, NCLOB, etc.)
      #endif

    #else

        // default, one-byte character sets

         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>>(otl_long_string& s); // read the LOB from the stream
         otl_stream& operator>>(std::string& s); // read the ANSI C++ std::string

    #endif

         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_BIGINT& d);
             // when #define OTL_BIGINT is enabled.
             // For ODBC drivers that do not support bigint binding
             // of host variables natively, this operator can convert
             // a numeric string value to a bigint numeric value. That is, when
             // a numeric value is returned from the SQL statement as a string,
             // this operator will convert the string to a signed 64-bit integer
             // in C++.
         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/TEXT/IMAGE from otl_stream
                             // into otl_lob_stream. In other words,
                             // initialize otl_lob_stream for reading CLOB/BLOB/TEXT/IMAGE
                             // 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.

- Write objects into the stream

    #if defined(OTL_UNICODE)

        // OTL_UNICODE is enabled (2-byte Unicode)

      otl_stream& operator<<(const unsigned char* s);
           // writes a null terminated array of unsigned short's
           // (double-byte Unicode characters) into the stream .
           // Therefore, "unsigned short*"
           // needs to be type cast to "unsigned char*". Also, it's recommended
           // to allocate 2 bytes per each Unicode character. For Oracle Unicode,
           // add extra 2 bytes per possible surrogate character.

        otl_stream& operator<<(otl_long_unicode_string& s);
           // write a Unicode LOB to the stream

      #if defined(OTL_UNICODE_CHAR_TYPE)
         otl_stream& operator<<(const OTL_UNICODE_CHAR_TYPE& c); // write Unicode character
         otl_stream& operator<<(const OTL_UNICODE_CHAR_TYPE* s); // write Unicode string
      #endif

      #if defined(OTL_UNICODE_STRING_TYPE)
         otl_stream& operator>>(const OTL_UNICODE_STRING_TYPE& s);
           // write Unicode string from a string class variable into the stream.
           // VARCHAR/CHAR can be written as well
           // as Large Text Objects (NTEXT, CLOB, NCLOB, etc.)
      #endif

    #else

        // default, one-byte character sets

         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 otl_long_string& d); // write the LOB into the stream
         otl_stream& operator<<(const std::string& s);
            // write the ANSI C++ std::string.

    #endif

         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_BIGINT d);
             // When #define OTL_BIGINT is enabled.
             // For ODBC drivers that do not support bigint binding
             // of host variables natively, this operator can convert
             // a C++ bigint numeric value to a string value. That is, when
             // a numeric value is being written to the stream and the
             // corresponding host variable has a string binding,
             // this operator will convert a signed 64-bit integer
             // in C++ to a string, which will get passed to
             // the SQL statement.

         otl_stream& operator<<(const otl_null n); // write NULL 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 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:

           class otl_datetime{
             public:
               int year;
               int month;
               int day;
               int hour;
               int minute;
               int second;

               unsigned long fraction; // second's fractional part. Equals 0 by default.
               int frac_precision; // second's precision. Equals 0 by default

          #if defined(OTL_ORA_TIMESTAMP)
               short int tz_hour; // time zone hour
               short int tz_minute; // time zone minute
          #endif

             };

> Fraction can be up to nine significant decimal digits long, depending on what the second's precision (frac_precision) is supported by the database (MS SQL Server down to milliseconds, Sybase down to milliseconds, DB2 down to microseconds, etc.), and what precision is specified in the timestamp datatype (Oracle 9i/10g: decimal digits in the range of [1..6] digits). Fraction does not have any effect in case if the database supports the timestamp datatype with whole seconds only.

- 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

Prev Next Contents Go Home

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.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.