OTL 4.0, OTL connect class
OTL connect class
This class encapsulates connect functions: connect,
disconnect,
commit, roll back, etc. In other words, the otl_connect class is the
class
for creating and handling connect objects together with
transaction
management.
class otl_connect{
public:
- Connection flag which shows
whether the object is
connected
to the
database or not. The flag only shows that the otl_connect object was
successfully connected to the database.If there has not been any
database calls on the otl_connect object, and, say, the database
connection was closed, the flag would still be set to 1 (true). In
order to check out whether the database connect still exists or not, a
"heartbeat" database round-trip needs to be made, say, a "SELECT
<current time> FROM <dual table>", or an actual,
application related SQL statement.
int connected;
- Static (in class) function to
initialize the
OTL environment. It needs to be called only once at the beginning of
the
program before making the very first connection to the database. The threaded_mode
is a parameter for specifying if the program is running in the
multi-threaded
mode but it does not automatically guarantee thread safety, because OTL
does not set any mutex locks or critical sections. Threaded_mode = 1
means
the multi-threaded mode, 0 -- the single threaded mode.
static int otl_initialize(const int threaded_mode=0);
- Static (in class) function to
terminate
the
Oracle 8i/9i OCI environment. It needs to be called only once at the
end
of the program after closing the very last database connection. This
function
is just a wrapper around the OCITerminate() call. Usually, in
multi-threaded
programs, in order to be able to terminate/end the main thread of
control,
otl_terminate needs to be called, because it detaches the process from
the OCI client side shared memory, and does something else, that is not
well documented.
static int otl_terminate(void);
OTL/OCI8, OTL/OCI9 only. Cancel
any
operation / database call, executing / active in the connect object /
database
session. Say, the database session is in the active state, or,
in
other words, has a running SQL statement in a thread. This function can
be called from another thread, to cancel the execution of the SQL
statement
asynchronously. Right after the cancel() call returns, the first thread
will raise an otl_exception with the following message: user
requested
cancel of current operation.
void cancel(void);
- OTL/OCI8i, OTL/OCI9i only.
This function
changes Oracle user's passwords. The program needs to attach to an
Oracle
server, and only then this function can be called.
void change_password
(const char* user_name,
const char* old_password,
const char* new_password
);
Set
transaction
isolation
level. OTL/DB2-CLI, OTL/ODBC only. The function allows the user
to set the following levels: READ COMMITTED, READ UNCOMMITTED,
REPEATABLE
READ, SERIALIZABLE. For more detail on the transaction isolation
levels,
see the regular database manuls.
void set_transaction_isolation_level(const long int level);
OTL/ODBC and OTL/DB2-CLI define the following global
constants,
which can be used as substitutes for the level parameter of
the
function:
- otl_tran_read_uncommitted - READ UNCOMITTED
- otl_tran_read_committed - READ COMITTED
- otl_tran_repetable_read -REPEATABLE READ
- otl_tran_serializable - SERIALIZABLE
- Set the maximum buffer size for
operations
with Large Objects: varchar_long,
raw_long, clob
and blob. This function
needs
to be called in order to increase the buffer size (default size is
32767).
void set_max_long_size(const int amax_size);
int get_max_long_size(void);
- This function works in OTL 4.0/ODBC and OTL 4.0/DB2-CLI only, and
it
has
not effect in OTL 4.0/OCIx. Set the cursor type. Once the cursor type
is
set, the setting will be propagated to all SELECT statements opened via
the otl_stream class in the
current
connect object.
void set_cursor_type(const int acursor_type=0);
The following cursor types are available (for more
detail,
see the ODBC Programmer's Guide, or the DB2 CLI Programmer's Guide):
- SQL_CURSOR_FORWARD_ONLY (default setting)
- SQL_CURSOR_STATIC
- SQL_CURSOR_KEYSET_DRIVEN
- SQL_CURSOR_DYNAMIC
- This function works in OTL 4.0/ODBC, OTL 4.0/DB2-CLI for Windows
only,
and has no effect in OTL 4.0/OCIx. Set the timeout for the current
connect
object. Once the timeout is set, the setting will be effective for all
SQL statements opened in the current connect object. The time unit for
the function is 1 second, e.g. setting 60 means the timeout of 1 minute.
If a timeout is set (this function gets called) before logging onto
/ connecting to the database, and if the logon / connect request is not
getting through to the database, the request will time out, according
to
the time interval that was set as a timeout.
In the future, if the OCI provides similar functionality, this
function
may be implemented. In OCIx, a different technique is used to simulate
timeouts: create a worker thread that will execute your SQL statement,
and a control thread with a timer. If the timer goes off and the SQL
statement
is still executing, the control thread can call otl_connect::cancel()
on the same connect object that is being used to execute the SQL
statement.
void set_timeout(const int atimeout=0);
- General constructor. It creates an
otl_connect
object and then calls the rlogon() function.
otl_connect(const char* connect_str,const int auto_commit=0);
- Log on / connect to the database.
void rlogon(const char* connect_str,const int auto_commit=0);
OTL 4.0/OCIx, OTL 4.0/ODBC, and OTL 4.0/DB2-CLI have
different styles of connect strings:
- OTL 4.0/OCIx style
- "USER/PASSWORD" (for local Oracle
connections)
- "USER/PASSWORD@TNS_ALIAS" (for remote
Oracle
connections
via SQL*Net)
- OTL 4.0/ODBC and OTL 4.0/DB2-CLI style
- "USER/PASSWORD/@DSN" (Oracle-like style for
ODBC or DB2-CLI
connections)
- "DSN=value;UID=value;PWD=value" (ODBC-like
style for ODBC
or DB2-CLI connection)
In OTL/ODBC/OCI8/OCI8i/OCI9i/OCI10g, it
is possible for a
password value to have a "@" (commercial at character) in it. In order
to specify a "@" in a password, the character has to be prefixed
with a "\", for example:
"userid/pass\\@word@DSN", or "UID=scott;PWD=ti\\@ger;DSN=mssql" (in
these examples, "\\" was used because the examples show C++ string
literals).
The auto_commit parameter in this function
is
used for setting the connection "auto-commit" mode. This means that
every
single SQL statement, executed in the connection, will be automatically
committed. This auto-commit mode has nothing to do with the otl_stream
auto-commit mode. In order to set the mode to "auto-commit", the
auto_commit
parameter needs to be set to 1. By default, it is 0, meaning that
"auto-commit"
is off.
In Oracle 7, ODBC and DB2-CLI, this mode is called "auto-commit".
In
Oracle 8/8i/9i, it is called "commit-on-success." It is not quite clear
why in Oracle 8 the term was changed to "commit-on-success" and pushed
from the level of the connection down to the level of the SQL statement
execution.
- Log on / connect to / attach to the
primary
database
connection, which was made in Pro*C/C++ or any other third-party
library.
This function works for OTL/OCI7 only, and it helps to
make
transition from the legacy environment to the OTL environment smoother.
See also example 58.
void
rlogon(Lda_Def* lda);
- Log on / connect to / attach to
the
primary
database connection, which was made in straight ODBC, DB2-CLI, or any
other
third-party (ODBC, or DB2-CLI based) library. This function works for OTL/ODBC,
DB2-CLI only, and it helps to make transition from the legacy
environment
to the OTL environment smoother.
void rlogon(HENV henv,
//
environment handle
HDBC hdbc, // database connect handle
const int auto_commit=0 // connect auto-commit mode
);
- Log on / connect to the
database (DSN / database alias). This function works for OTL/ODBC,
DB2-CLI only, when Unicode ODBC driver functions are enabled
(via #define UNICODE, #define
_UNICODE). The
function allows Unicode user names, passwords, and DSNs to be passed
into the ODBC layer.
#if defined(OTL_UNICODE_EXCEPTION_AND_RLOGON)
void rlogon(const OTL_UNICODE_CHAR_TYPE*
username, // user name
const OTL_UNICODE_CHAR_TYPE* passwd, // password
const OTL_UNICODE_CHAR_TYPE* dsn, // data source name
const int auto_commit=0 // connect
auto-commit mode
);
#endif
- General constructor: connect
to
the
primary database connection, which was made in straight ODBC, DB2-CLI,
or any other third-party (ODBC, or DB2-CLI based) library. This
function
works for OTL/ODBC, DB2-CLI only, and it helps to make
transition
from the legacy environment to the OTL environment smoother.
otl_connect(HENV henv, // environment handle
HDBC hdbc, // database connect handle
const int auto_commit=0 // connect auto-commit mode
);
- Log on / connect to / attach to the
primary
database
connection, which was made in Pro*C/C++ or any other third-party
library.
This function works for OTL/OCI8/8i/9i only, and it
helps
to make integration of OTL/OCI8/8i/9i and the Pro*C/C++ environment
smooth
and easy. In order to use this function in the Pro*C/C++ environment,
#define
OTL_ORA8_PROC needs to be set at the beginning of the program. For more
detail, see example 59
envhp is a pointer to the OCI8/8i/9i environment handle,
which
needs to be obtained by calling the SQLEnvGet function.
svchp is a pointer to the OCI8/8i/9i service context handle,
which needs to be obtained by calling the SQLSvcCtxGet function.
void rlogon(OCIEnv* envhp, OCISvcCtx* svchp);
- OTL/OCI8/8i/9i/10g, connect
to the database and set the following connection attributes that are
required by the Oracle XA Library: server external name, server
internal name.
void rlogon(const char* connect_str,
const int aauto_commit=0,
const char* xa_server_external_name=0,
const char* xa_server_internal_name=0,
#if defined(OTL_ORA_OCI_ENV_CREATE)
bool threaded_mode=false
#endif
);
- Logs off / disconnects from the database.
In
case
if the primary connection was done in Pro*C/C++ (OTL/OCIx), this
function
just detaches from that connection without actually disconnecting from
the database.
void logoff(void);
- Attach to / detach from Oracle (OTL/OCI8/8i/9i
only). These functions are wrappers around the ServerAttach and
ServerDetach
functions of the OCI8. The main reason to have separate functions to
attach
to the server and then begin a session is performance. For more detail,
see Oracle's regular manuals. These functions, along with the session_begin
and session_end functions, could be used
for
implementing a connection management mechanism that is more efficient
than
the traditional connection pooling. See also example 60.
void server_attach(const char* tnsname=0,
const char* xa_server_external_name=0,
const char* xa_server_internal_name=0,
#if defined(OTL_ORA_OCI_ENV_CREATE)
bool threaded_mode=false
#endif
)
// tnsname (TNS alias from TNSNAMES.ORA file) can be specified
// in order to connect to a remote Oracle 8 server.
// For local connections, tnsname defaults to 0.
// The function attaches to the database server (local or remote).
// The following "server attachment" attributes that are
// required by the Oracle XA Library can be also specified:
// server external name, server internal name. Also see rlogon().
void server_detach(void); // detach from the Oracle 8 server
- Begin / end Oracle 8 session (OTL/OCI8/8i/9i only).
These
functions complement the server attach/detach
suite of functions, and are used to begin / end an Oracle 8 session.
The
main reason to have separate server attach/detach and session begin/end
groups of function is performance. session_begin() is approximately
50-100
times faster than the normal rlogon(). These
functions,
along with the server_attach() and server_detach()
functions, could be used for implementing a connection management
mechanism
that is several times more efficient [thus, more scalable] than the
traditional
connection pooling.
void session_begin
(const char* username, // user name, e.g. "scott"
const char* password, // password, e.g. "tiger"
const int auto_commit=0, // session auto-commit mode
const int session_mode=OCI_DEFAULT
// OCI_SYSDBA -- in this mode, the user is authenticated for SYSDBA
// access.
// OCI_SYSOPER -- in this mode, the user is authenticated
// for SYSOPER access.
);
void session_end(void);
- Session reopen (OTL/OCI8/8i/9i only). If a session
was
started
via session_begin() for a given username/password
and then closed via session_end(), another
session
can be started faster (5-10%) for the same username/password via
session_reopen().
This function takes advantage of the fact that the username and
password
attributes of the OCI8 internal connect structure have been already set
by the previous session_begin() function call. If a session has been
started
via session_reopen(), it can be closed by calling session_end().
Also, see example 61
void session_reopen(const int aauto_commit=0 // session auto-commit mode
);
- Commit transaction. In the otl_connect object, Oracle, ODBC and
DB2-CLI
connections, by default, are set to auto-commit off. This
means
that all transactions need to be commited by explicitly calling the
commit()
function. It is not recommended to use connections that are set to auto-commit
on.
void commit(void);
- Commit transaction asynchronously, same as commit and forget /
commit with no wait. At the moment, avalaible only for Oracle 10g
Release 2 (Oracle 10.2). As soon as the rest of the database vendors
release a similar feature, it will be implemented in OTL.
#if defined(OTL_ORA10G_R2)
void commit_nowait(void);
#endif
void rollback(void);
Set otl_connect's auto-commit OFF and
ON,
within
one open session/connect. These two functions are for partial backward
compatibility only.
void auto_commit_off(void);
void auto_commit_on(void);
Set the otl_stream
pool size. This function can be activated with #define OTL_STREAM_POOLING_ON,
used together with #define OTL_STL.
If OTL_STREAM_POOLING_ON is defined in the program, the function body
gets
included into the target code. Also, a default stream pool gets
allocated
as part the otl_connect object, even if the function doesn't get
called.
Therefore, a call to the function will reallocate all the resources,
allocated
by the default stream pool, or by previous calls to
set_stream_pool_size().
void set_stream_pool_size
(const int max_size=otl_max_default_pool_size);
- This function is available for
OTL/OCI8i/9i/10g
under #define OTL_UNICODE
only.
In Unicode OTL, the Oracle database default or national character set
is
allowed to be set:
The default character set (SQLCS_IMPLICIT) needs to be set when
Unicode containers are used with the VARCHAR2, CHAR, LONG, and CLOB
column
types. In the case if Unicode containers are used with NVARCHAR2,
NCAHR,
and NCLOB datatypes, the national character set (SQLCS_NCHAR) needs to
be set.
SQLCS_IMPLICIT, and SQLCS_NCHAR are defined by the OCI header
files.
Since this function is part of the otl_connect class, all
otl_streams,
that gets opened via a concrete connect object, will use the character
set, that was set by the function.
The semantic of :var<char[...]...>
declaration of string bind variables was extended to accomodate the
Unicode
strings. For example, char[20] means a string of 19 double-byte Unicode
characters, plus 19 double-byte possible surrogate characters, plus one
double-byte null terminator. It sounds a little complicated, but that
is
just a reflection of a plethora of Unicode standards, coding
techniques.
It is strongly recommended to read the Unicode standards themselves,
then
the Oracle implementations the Unicode. By the way, Oracle 8i and 9i
differ
in that regard substantially.
- OTL/ODBC,
OTL/DB2-CLI only. Set the "OTL throws on SQL_SUCCESS_WITH_INFO"" flag.
When the flag is set, and SQLExecDirect() / SQLExecute() function
returns SQL_SUCCESS_WITH_INFO, OTL throws an otl_exception. This is
useful for communicating more information from the SQL call, especially
if the call is a call to a stored procedure, or a Transact-SQL batch
that may return more diagnostic information than usual. The function is
enabled only when #define OTL_THROWS_ON_SQL_SUCCES_WITH_INFO is defined.
#if defined(OTL_THROWS_ON_SQL_SUCCESS_WITH_INFO)
void set_throw_on_sql_success_with_info(const bool throw_flag=false);
#endif
Send a string to the otl_connect
object. If the object is not connected to a database then the string is
a connection
string in the "userid/passwd@db" format and it makes the object connect
to the database. If the object is already connected, the string is a
static SQL statement that the connect object executes immediately.
otl_connect& operator<<(const char* str);
Send a string to the otl_connect
object. The connect object saves the string for further use with
operator >>(). The string is an SQL
statement with placeholders that could be sent to an otl_stream object.
In other words, this operator is a deferred SQL statement.
otl_connect& operator<<=(const char* str);
Send the SQL statement that was saved
with operator <<= to an otl_stream
object. It makes the otl_stream object open the SQL statement. If there
was no SQL statement previously save by the connect object, the connect
object sends the following string to the otl_stream: "*** INVALID COMMAND ***".,
which causes an SQL parse error in the otl_stream, which ends up in an
otl_exception.
otl_connect& operator>>(otl_stream& s);
Direct execution of a static SQL
statement, the function returns the
Rows Processed Count (RPC)
long direct_exec(const char *sqlstm, // SQL Statement
int ignore_error = otl_exception::enabled
// "ignore error" flag
);
Parses a static SQL statement,
raises an otl_exception if
there is an SQL error.
void syntax_check(const char *sqlstm // SQL Statement
);
}; // end of otl_connect
Prev NextContentsGo
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.