OTL 4.0, OTL stream class

OTL stream class

The otl_stream class is the actual implementation 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 be inserted into a table, selected from a table / view in one round-trip to the database (a.k.a. batch size, array size).

Note: the stream buffer size can be set to 0 when #define OTL_TIMESTEN_UNIX / OTL_TIMESTEN_WINDOWS is used against TimesTen 7.0.2 or higher. The buffer size of 0 in this context means that the default prefetch size would be used. The stream's internal buffers become arrays of 1 element in this special case. For more information on the TimesTen Prefetch read the corresponding manual.

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 data type is int (the old data type was short int). If the old data type is needed for backward compatibility, #define OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE has to be enabled.

When #define OTL_UNICODE is used, all OTL stream string operators << / >> become Unicode (UTF-16) string operators. That is, single byte character strings need to be converted to / from Unicode strings before using otl_stream string operators << / >>.

The otl_stream class has the following public functions:

class otl_stream{
public:

Function
Description


otl_stream(...);

Parameter
Description
const int arr_size stream buffer size
const char* sqlstm SQL statement or anonymous 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, so the actual text of the SQL statement will not be visible and will be replaced with its label


Oracle 7/8/9/10/11/12 only.

General constructor. This constructor creates an otl_stream object and then calls the open() function.


otl_stream(...);

Parameter
Description
const int arr_size stream buffer size
const char* sqlstm SQL statement or stored procedure call
otl_connect& db OTL connect object
const int implicit_select =
otl_explicit_select
If the stream returns a result set via a stored 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, so the actual text of the SQL statement will not be visible and will be replaced with its label

ODBC/DB2-CLI only.

General constructor. This constructor creates an otl_stream object, and then calls the open() function.

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 one result set.

When otl_implicit_select is used, only input parameters are allowed in the corresponding stored procedure call that returns an implicit result set, otherwise the following otl_exception is thrown: This type of otl_stream can only have input variables (error code 32040).

Essentially, the stream, that processes an implicit result set, works as an extension of the internal SELECT stream.

void open(...);

Parameter
Description
const int arr_size stream buffer size
const char* sqlstm, SQL statement or anonymous PL/SQL block connect object
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, so the actual text of the SQL statement will not be visible and will be replaced with its label.

Oracle 7/8/9/10/11/12 only

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.


void open(...);

Parameter
Description
const int arr_size stream buffer size
const char* sqlstm SQL statement or stored procedure call
otl_connect& db OTL connect object
const int implicit_select=
otl_explicit_select
If the stream returns a result set via a stored  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, so the actual text of the SQL statement will not be visible and will be replaced with its label.

ODBC/DB2-CLI only.

This function open an SQL statement and the statement
gets parsed, all input and output variables get dynamically allocated inside the stream and automatically bound to
the placeholders.

int eof();

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

int setBufSize(const int buf_size);

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

void flush();

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

Parameter
Description
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,8i,9i/10g/11g 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.

int get_auto_commit_flag(); Get the stream's "auto-commit" flag. 1 means the flag is set, 0 - not set.
void get_error(...);

Parameter
Description
const int error_ndx
error index (in the range of [0..get_number_of_errors_in_batch()]
int& dml_row_offset 0 based row offset (index) to the beginning of the batch.
otl_exception& exc
error code and error message as otl_exception of the row that caused the error.

Under Oracle 8/9/10/11/12 only.

This function returns a 0 based row offset to the beginning of the batch, and the error code / message of the row that caused the error. See also code example 232.
bool get_error_state();
Get whether the stream is in the "error state" or not (return true or false). The stream gets into the error state if an otl_exception has been thrown. If the stream in the error state, otl_stream::clean() should be called to clean the stream.
int get_number_of_errors_in_batch();
Under Oracle 8/9/10/11/12 only.

This function returns the total number of errors when the stream is in the batch error mode. See also code example 232.

int get_stream_type();
OTL/OCI8/9/10/11/12 only. Get the OTL stream type.

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_mixed_refcur_stream_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 reference cursor(s) as well as scalar (input/) output parameters (mixed reference cursor stream). If create_stored_proc_call() returns this constant in its stm_type parameter, and when create_stored_proc_call()'s sql_stm parameter is instantiated, the corresponding otl_stream's buffer size needs to be set to 1, and otl_refcur_stream should be used to fetch rows from the refcur bind variables of the sql_stm's text. Also, otl_stream::describe_in_vars() and otl_stream::describe_out_vars() should be used to describe the stream input and output variables in the case of general-purpose C++ code to handle PL/SQL procedure calls that get generated by the create_stored_proc_call() function. See example 153 for more detai
static void create_stored_proc_call(...);

Parameter
Description
otl_connect& db OTL 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 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/RAW 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]> / :var<raw[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.
const int refcur_buf_size=1 If the stored procedure call contains reference cursors, this parameter can be used to specify the buffer size of the reference cursors,for example: f4<refcur,out[100]>. This can be important for performance.

OTL/OCI8/9/10/11/12 only. Not implemented under #define OTL_UNICODE. Creates 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/11g 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 data types. 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 types of stored procedures/functions:

  • stored procedures / functions from PL/SQL packages from the current / specified schema, or global procedures / functions from the current / specified schema.

  • stored procedures / functions from PL/SQL packages available via private / public synonyms, or global procedures / functions available via private / public synonyms.

  • procedures / functions, whose names are not overloaded.
  • procedures / functions, with scalar input and/or output parameters, and maybe with output reference cursor(s).


For more detail, also see #define OTL_ORA_CREATE_STORED_PROC_CALL_MAPS_RAW_TO_RAW, and #define OTL_ORA_CREATE_STORED_PROC_CALL_MAPS_RAW_TO_RAW_LONG.

void clean(const int clean_up_error_flag=0);
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 a database 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 rewind();

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

operator int();

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;

  }

void cancel();

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.
bool get_next_diag_rec(...);
Parameter
Description
short int& rec_ndx
Diagnostic record index
SQLCHAR* sqlstate_buf
SQL State buffer. When Unicode is enabled, the type is SQLWCHAR*
SQLCHAR* msg_buf
Message buffer. When Unicode is enabled, the type is SQLWCHAR*
short int msg_buf_size
Message buffer size
int& native_error
Native error

OTL/ODBC only. Under #define OTL_ODBC_SQL_STATEMENT_WITH_DIAG_REC_OUTPUT.

This function should  be used to get diagnostic records from such MS SQL Server commands as BACKUP, DBCC, etc. The function calls SQLGetDiagRec() inside.. The record index needs to be started with 1. The function increments the record index (rec_ndx++). For more detail, see also examples 688, 689.
SQLHSTMT get_stm_handle();
OTL/ODBC only. Under #define OTL_ODBC_SQL_STATEMENT_WITH_DIAG_REC_OUTPUT.

This function should  be used to get the underlying ODBC statement handle, when, say, calls to SQLMoreResults() need to be made in order to get all output diagnostic records for a BACKUP command.

int is_null();

Test if NULL was fetched from the stream.

void set_lob_stream_mode
(const bool mode=false);

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.

long get_rpc();

Get the ROWS PROCESSED COUNT (RPC). The count is defined for INSERT, UPDATE, DELETE statements, and it shows how many 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.

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


void set_batch_error_mode
(const bool batch_error_mode)

Under Oracle 8/9/10/11/12 only.

This function sets (true) / unsets (false) the stream's "batch error mode". It means that if a batch operation failed (typically, INSERT / UPDATE / DELETE), per-row error codes and error messages are available as well as row offsets (row indexes) from the beginning of the batch. When the stream is in the batch error mode, ALL good rows in the batch do succeed / make it to the database, as opposed to the non-batch error mode, when the execution of the underlying SQL statement stops on the first row that has an error.

See also code example 232.

void set_commit(int auto_commit=0);

Set the stream auto-commit flag. When the output buffer is flushed, the current transaction is automatically committed, 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_flush(const bool auto_flush=true);

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.

otl_var_desc* describe_out_vars
(int& desc_len);  

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:

Data member
Description
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 array of variables: 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_in_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_next_out_var();


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


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.

(1) void close();

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


(2) void close
(const bool save_in_stream_pool=true);



            
Under #define OTL_STREAM_POOLING_ON

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

int good();

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

otl_column_desc* describe_select
(int& desc_len);

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:

Data member
Description
char* name; column name
int  dbtype; database dependent, column data type code for more detail, see the OCIx and the ODBC manuals.
int  otl_var_dbtype; OTL defined, column data type 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
int charset_form; under #define OTL_UNICODE and #define OTL_ORA9I / OTL_ORA10G

SQLCS_IMPLICIT (1) for on-byte character sets, SQLCS_NCHAR (2) for NLS multi-byte character sets; 0 otherwise.
int char_size; under #define OTL_UNICODE and #define OTL_ORA9I / OTL_ORA10G

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 data types, which the native database data types are mapped 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
data type that is mapped into BLOB in Oracle 8/9/10/11/12
otl_var_char 1
null terminated string
otl_var_clob
11
data type that is mapped into CLOB in Oracle 8/9/10/11/12
otl_var_db2date
17
DB2 DATE data type
otl_var_db2time 16
DB2 TIME data 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 (for 32-bit, and LLP64 C++ compilers), signed 64-bit integer (for LP-64 C++ compilers)
otl_var_ltz_timestamp
19
Oracle 9i/10g/11g TIMESTAMP WITH LOCAL TIME ZONE type
otl_var_raw 23
RAW, BINARY, VARBINARY, BYTEA, VARCHAR BYTE, CHAR BYTE, etc.
otl_var_raw_long 10
data type that is mapped into LONG RAW in Oracle, IMAGE in MS SQL Server and Sybase, BLOB in DB2
otl_var_short 6
signed 16-bit integer
otl_var_timestamp 8
data type that is mapped into Oracle date/timestamp, DB2 timestamp, MS SQL datetime/datetime2/time/date, Sybase timestamp, etc.
otl_var_tz_timestamp 18
Oracle timestamp with timezone type
otl_ubigint
27
unsigned 64-bit integer
otl_var_unsigned_int 5
unsigned 32-bit integer
otl_var_varchar_long 9
data type that is mapped into LONG in Oracle 7/8/9/10/11/12, TEXT in MS SQL Server and Sybase, CLOB in DB2

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 destruction, so the user does not need to do any memory deallocation operations with the pointer.
void reset_to_last_valid_row();
When OTL throws "Incompatible data types in stream operation" exception, the exception is contained to the OTL code itself. The exception gets thrown before the underlying INSERT/UPDATE/DELETE statement is executed. reset_to_last_valid_row() can be called to reset the stream's output buffer to the last valid state / row. After the stream's state is reset, otl_stream::flush() can be safely called, for example:

   otl_stream str
   (100,
    "INSERT INTO test_tab VALUES(:f1<int>,:f2<char[31]>)",
   db);
   ...
   try{
     // writing rows into the stream
     ...
   }catch(const otl_exception& ex){
      if(ex.code==32000){
       str.reset_to_last_valid_row();
       str.flush();
      }
   }

otl_stream& operator>>(unsigned char* s);

Read objects from the stream.

under #define OTL_UNICODE

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

under #define OTL_UNICODE
under #define OTL_UNICODE_CHAR_TYPE

reads the Unicode LOB from the stream.

otl_stream& operator>>(OTL_UNICODE_CHAR_TYPE& c);

under #define OTL_UNICODE
under #define OTL_UNICODE_CHAR_TYPE

reads a Unicode character.

otl_stream&
operator>>(OTL_UNICODE_CHAR_TYPE* s);

under #define OTL_UNICODE
under #define OTL_UNICODE_CHAR_TYPE

reads a Unicode string
otl_stream& operator>>(OTL_UNICODE_STRING_TYPE& s);

under #define OTL_UNICODE
under #define OTL_UNICODE_CHAR_TYPE
under #define OTL_UNICODE_STRING_TYPE

reads Unicode string into a string class variable. VARCHAR/CHAR can be  read as well as Large Text Objects (NTEXT, CLOB, NCLOB, etc.)
otl_stream& operator>>(char& c);
reads a single one-byte character
otl_stream& operator>>(unsigned char& c);
reads a single one-byte unsigned character
otl_stream& operator>>(char* s);
reads a string of one-byte characters
otl_stream& operator>>(unsigned char* s);
reads a string of one-byte unsigned characters
otl_stream&
operator>>(
otl_long_string& s);
reads a LOB from the stream
otl_stream& operator>>(std::string& s); reads the following types into ANSI C++ std::strings (under #define OTL_STL): VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also read RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream& operator>>(ACE_TString& s); reads the following types into ACE_TStrings (under #define OTL_ACE): VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also read RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream& operator>>(USER_DEFINED_STRING_CLASS & s); under #define USER_DEFINED_STRING_CLASS
under #define OTL_USER_DEFINED_CLASS_ON

reads the following types into a string class (std::string compliant): VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also read RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream& operator>>(int& n); reads a signed 32-bit integer
otl_stream& operator>>(unsigned& u); reads an unsigned 32-bit integer
otl_stream& operator>>(short& sh); reads a signed 16-bit integer
otl_stream& operator>>(long int& l); reads a signed long integer (32, or 64-bit, depending on whether it's a 32-bit, LLP64 or LP64 platform)
otl_stream& operator>>(float& f); reads a 4-byte floating point value
otl_stream& operator>>(double& d); reads an 8-byte floating point value
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++, when #define  OTL_STR_TO_BIGINT, and and #define OTL_BIGINT_TO_STR are defined.

otl_stream& operator>>(OTL_UBIGINT& d); when #define OTL_UBIGINT is enabled.
otl_stream& operator>>(OTL_NUMERIC_TYPE_1& n);
otl_stream& operator>>(OTL_NUMERIC_TYPE_2& n);
otl_stream& operator>>(OTL_NUMERIC_TYPE_3& n);
when #define OTL_NUMERIC_TYPE_X are enabled. OTL can be extended with up to three numeric data types that are no directly supported by the underlying database APIs via char[XXX] bind variables. When OTL_NUMERIC_TYPE_X #defines are enabled, OTL adds the corresponding operators >> to the otl_stream class.
otl_stream& operator>>(otl_datetime& dt); reads date/time info from the stream
otl_stream& operator>>(otl_XXX_tab<>& tab); reads PL/SQL tables from the stream (OCIx)
otl_stream&
operator>>(
otl_lob_stream& lob);
reads reference to CLOB/BLOB/TEXT/IMAGE from otl_stream into otl_lob_stream. In other words, initializes otl_lob_stream for reading CLOB/BLOB/TEXT/IMAGE  in stream mode.
otl_stream&
operator>>(otl_refcur_stream& refcur);
reads a reference cursor descriptor to a variable of  the otl_refcur_stream type. That is, initializes otl_refcur_stream for reading rows from the reference cursor.
otl_stream&
operator<<(const unsigned char* s);
Write objects into the stream

under #define OTL_UNICODE

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);
writes a Unicode LOB to the stream
otl_stream&
operator<<(const OTL_UNICODE_CHAR_TYPE& c);

under #define OTL_UNICODE_CHAR_TYPE

Writes a Unicode character
otl_stream&
operator<<(const OTL_UNICODE_CHAR_TYPE* s);
Writes Unicode string
otl_stream&
operator>>
(const OTL_UNICODE_STRING_TYPE& s);
under #define OTL_UNICODE
under #define OTL_UNICODE_CHAR_TYPE
under #define OTL_UNICODE_STRING_TYPE

Writes Unicode string from a string class variable into the stream. [N]VARCHAR/[N]CHAR can be written as well  as Large Text Objects (NTEXT, CLOB, NCLOB, etc.)
otl_stream& operator<<(const char c); Writes a single character into the stream
otl_stream&
operator<<(const unsigned char c);
Writes a single unsigned character into the stream
otl_stream& operator<<(const char* s); Writes a null terminated string of characters into the stream
otl_stream&
operator<<(const unsigned char* s);
Writes a null terminated string of unsigned characters into the stream
otl_stream&
operator<<(const
otl_long_string& d);
Writes the LOB into the stream
otl_stream&
operator<<(const std::string& s); 
under #define OTL_STL

Writes ANSI C++ std::strings into the following types: VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also write RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream&
operator<<(const ACE_TString &s); 
under #define OTL_ACE

Writes ACE_TStrings into the following types: VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also write RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream& operator<<
(const
USER_DEFINED_STRING_CLASS &s);
under #define USER_DEFINED_STRING_CLASS
under #define OTL_USER_DEFINED_CLASS_ON

Writes USER_DEFINE_STRING_CLASS values into the following types: VARCHAR/CHAR, Large Text Objects (TEXT, CLOB, LONG). This operator can also write RAW / BINARY / VARBINARY values or bind variables declared as raw[XXX].
otl_stream& operator<<(const int n); Writes an signed int into the stream
otl_stream& operator<<(const unsigned u); Writes an unsigned int int the stream
otl_stream& operator<<(const short sh); Writes a short int into the stream
otl_stream& operator<<(const long int l); Writes a signed long int into the stream
otl_stream& operator<<(const float f); Writes a float into the stream
otl_stream& operator<<(const double d); Writes a double into the stream
otl_stream&
operator<<(const
OTL_BIGINT d);
Under #define OTL_BIGINT. 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, when #define  OTL_STR_TO_BIGINT, and and #define OTL_BIGINT_TO_STR are defined.
otl_stream& operator<<(const otl_null &n); Writes NULL into the stream.

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_stream&
operator<<(const OTL_NUMERIC_TYPE_1& n);


otl_stream&
operator<<(const OTL_NUMERIC_TYPE_2& n);

otl_stream&
operator<<(const OTL_NUMERIC_TYPE_3& n);
when #define OTL_NUMERIC_TYPE_X are enabled. OTL can be extended with up to three numeric data types that are no directly supported by the underlying database APIs via char[XXX] bind variables. When OTL_NUMERIC_TYPE_X #defines are enabled, OTL adds the corresponding operators << to the otl_stream class.
otl_stream&
operator<<(const
otl_datetime& dt);
Writes date/time info into the stream.

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

class otl_datetime{
public:

Data member
Description
int year;
year
int month; month
int day; day
int hour; hour
int minute; minute
int second; second
unsigned long fraction; second's fractional part. Equals 0 by default.
int frac_precision; second's precision. Equals 0 by default
short int tz_hour;

under #define OTL_ORA_TIMESTAMP or
under #define OTL_ODBC_TIME_ZONE

time zone hour;

short int tz_minute under #define OTL_ORA_TIMESTAMP or
under #define OTL_ODBC_TIME_ZONE

time zone minute

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

otl_stream&
operator<<(const
otl_XXX_tab<>& tab);
Reads PL/SQL tables from the stream (OCIx)
otl_stream&
operator<<(
otl_lob_stream& lob); 
Writes otl_lob_stream descriptor into the otl_stream (OCI8). In other words, initializes otl_lob_stream for writing CLOB/BLOB in stream mode.
void set_all_column_types
(const unsigned int amask=0);
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_column_type
       (const int column_ndx,
        const int col_type,
        const int col_size=0);

Set a SELECT output column data type. In other words, override the default mapping of output column data types.

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

col_type is one of the data type constants, defined by OTL.

col_size is the size, associated with the new data type of the column. It has be to specified for the otl_var_char type only. Sizes 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 data type compatibility matrix:


Database  data type Default data type Data type 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)

It is recommended that this function and data type overrides be used 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.

int get_dirty_buf_len(); Gets the stream dynamic "dirty buffer length". For INSERT / UPDATE / DELETE statements, when the stream buffer size is set to a value > 1, the stream buffer logical rows. When the buffer gets full, the stream automatically flushes the buffer (executes the SQL statement, the stream is instantiated with). If the stream buffer is not full yet, the stream buffer can be flushed by calling flush(). Sometimes, it is useful to get the actual number of rows in the stream buffer.This function returns the actual number of rows in the stream buffer.

When the stream buffer size is set to 1, this function always returns 0, because when the stream accumulates a whole row (all the columns of a logical row) in the buffer, the stream automatically flushes the buffer, and the buffer gets reset right after the auto-flush.

The function also returns the number of "dirty" (yet to be read) rows in the otl_stream buffer of a SELECT statement / PL/SQL reference cursor, or a stored procedure that returns an implicit result set. The number of dirty rows in the stream buffer can be less or equal to the otl_stream buffer size.

For any other type (not INSERT/UPDATE/DELETE/SELECT) of SQL statements / PL/SQL blocks / stored procedure calls, the result of this function is undefined.

int get_prefetched_row_count(); Returns the accumulative number of prefetched [by otl_stream] rows for SELECT statements, stored procedures that return implicit result sets, or Oracle reference cursors. For other types of SQL statements, stored procedure calls, or PL/SQL blocks, the function returns 0.

For example, the stream buffer is 50, and the stream fetches 50 rows in the first batch, 50 rows in the second batch, and 23 in the third batch. This function will return 50, 100, and 123, while the corresponding batch is being processed.
void skip_to_end_of_row(); Skips to the end of the current row, for example:
    
      while(!s.eof()){
     s>>f1;
     ...
     s.skip_to_end_of_row();
           ...
   }

otl_stream sets the internal pointers to the "end of the current row", so that the next call to operator >> will fetch the first value of the next logical row, or reach the end of the fetch sequence.
void check_end_of_row();
Checks the "end-of-row" condition. The function throws the "END-OF-ROW check failed" otl_exception if the condition is not met. The function addresses the concern about "row-tearing" in a SELECT statement. Besides the SELECT row-tearing, the function addresses a similar "row-tearing" problem for INSERT / UPDATE / DELETE statements as well as calls to stored procedure / anonymous PL/SQL blocks.
otl_stream&
operator>>(otl_stream& (*pf) (otl_stream&));
This operator calls the (*pf) stream manipulator function. The operator can be called in a chain of operator>>()'s, for example:

     s>>f1>>f2>>endr; // calls check_end_of_row()

otl_stream manipulator functions are global functions,  defined in the same namespace as the otl_stream class itself. The following stream manipulators are available:

     otl_stream& endr(otl_stream&); // "end-of-row" check

This stream manipulator calls the otl_stream::check_end_of_row() function.
otl_stream& operator<<(otl_stream& (*pf) (otl_stream&));
This operator calls the (*pf) stream manipulator function. The operator can be called in a chain of operator<<()'s, for example:

     o<<f1<<f2<<endr; // calls check_end_of_row()

The OTL stream can be running an INSERT, or an UPDATE, or a DELETE statement, or a stored procedure call / anonymous PL/SQL block. endr stream manipulator is meant to be similar to C++ standard stream manipulators: std::endl and std::ends.

}; // end of otl_stream

Prev NextContentsGo Home

Copyright 1996-2014, Sergei Kuchin, email: skuchin@gmail.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. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.