OTL connect class

class otl_connect{ 
public:

• Connection flag which shows whether the object is connected to the database or not.

      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 and the OTL environment needs to be thread-safe. 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);

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

• Get the maximum buffer size for operations with Large Objects: varchar_long, raw_long, clob and blob.

      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/OCI7, OTL 4.0/OCI8. 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/OCI7, OTL 4.0/OCI8. 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. In the future, if the OCI8 provides similar functionality, this function may be implemented. In OCIx, a different technique is used to simulate timeouts.

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

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

• 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 only, and it helps to make integration of OTL/OCI8 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 environment handle, which needs to be obtained by calling the SQLEnvGet function.
svchp is a pointer to the OCI8 service context handle, which needs to be obtained by calling the SQLSvcCtxGet function.

        void rlogon(OCIEnv* envhp, OCISvcCtx* svchp);

• Logs off / disconnects from the database. In case if the primary connection was done in Pro*C/C++ (OTL/OCI7 or OTL/OCI8), this function just detaches from that connection without actually disconnecting from the database.

      void logoff(void);

• Attach to / detach from Oracle (OTL/OCI8 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); 
         // tnsname (TNS alias from TNSNAMES.ORA file) can be specified 
         // in order to connect to a remote Oracle 8 server. 
         // For local connections, tnsname gets defaulted to 0.

     void server_detach(void); // detach from the Oracle 8 server

• Begin / end Oracle 8 session (OTL/OCI8 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
                          );

     void session_end(void);

• Session reopen (OTL/OCI8 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. For this reason, OTL 4.0 does not have the auto_commit_off() and auto_commit_on() functions in the conenct object.

       void commit(void);

• Roll back transaction

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

}; // end of otl_connect

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

Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies.