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:
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.
);
otl_stream(const int arr_size, // stream buffer sizeOTL 4.0/ODBC and OTL 4.0/DB2-CLI define the following global constant to be used with this constructor:
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.
);
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.
);
int eof(void);
int setBufSize(const int buf_size);
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
);
int get_stream_type(void);
The following global constants (int's) are defined:
The function is able to handle the following stored
procedures/functions:
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.
);
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);
void rewind(void);
operator int(void);
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);
int is_null(void);
void set_lob_stream_mode(const bool mode=false);
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.
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);
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);
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.
};
OUT variables are the variables that get read FROM the stream.
otl_var_desc* describe_out_vars(int& desc_len);
otl_var_desc* describe_in_vars(int& desc_len);
otl_var_desc* describe_next_out_var(void);
otl_var_desc* describe_next_in_var(void);
(1) void close(void);
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.
#ifdef OTL_STREAM_POOLING_ON
(2) void close(const bool save_in_stream_pool=true);
#endif
int good(void);
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_column_desc* describe_select(int& desc_len);
#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.
#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(){}
};
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.
void set_all_column_types(const unsigned int amask=0);
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, 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.