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

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

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

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

- SQLCS_IMPLICIT -- database default (implicit) character set
- SQLCS_NCHAR -- database national (second) character set.

`void set_character_set(const int char_set=SQLCS_IMPLICIT);`

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 Next Contents Go Home

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.com.

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