SQL_ACCESS_MODE

Values 

SQL_MODE_READ_ONLY | SQL_MODE_READ_WRITE

Description

Define access mode to database using this variable to wither R/W or R/O mode.
When the access mode is SQL_MODE_READ_ONLY then the connection is not required to support SQL statements that involve updates to the database. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source.

Default

SQL_MODE_READ_WRITE

Example

SQL_ACCESS_MODE = SQL_MODE_READ_WRITE

SQL_AUTOCOMMIT

Values 

SQL_AUTOCOMMIT_ON | SQL_AUTOCOMMIT_OFF

Description

Set to commit each statement immediately after executed or manually commit and roll back transactions.

When SQL_AUTOCOMMIT_OFF , the driver uses manual-commit mode, and the application must explicitly commit or roll back transactions. When SQL_AUTOCOMMIT_ON , the driver uses auto-commit mode. Each statement is commit immediately after it is executed. Note : When a driver is in autocommit mode, the DB COMMIT and DB ROLLBACK are not processed.

When the SQL_AUTOCOMMIT = SQL_AUTOCOMMIT_OFF then issuing the DB EXIT command may return errors like "SQLDisconnect failed". The cause is that there is an attempt to disconnect from the Driver Manager when there is still a SQL statement(s) in line to be committed to the Data Source when the EXIT command is issued. That is the SQL_AUTOCOMMIT is explicit in that it issues the SQL statement as soon as it is issued, whereas SQL_AUTOCOMMIT_OFF issues the SQL commands on request.

Default

SQL_AUTOCOMMIT_ON

Example

SQL_AUTOCOMMIT = SQL_AUTOCOMMIT_OFF

SQL_LOGIN_TIMEOUT

Values 

a number | unset

Description

A value corresponding to the number of seconds to wait for a login request to complete.   The default is driver-dependent and must be nonzero. If the value is 0, the timeout is disabled and a connection attempt will wait indefinitely.

Default

unset

Example

 SQL_LOGIN_TIMEOUT=5

SQL_TXN_ISOLATION

Values 

SQL_TXN_VERSIONING| SQL_TXN_READ_UNCOMMITTED| SQL_TXN_READ_COMMITTED | SQL_TXN_REPEATABLE_READ | SQL_TXN_SERIALIZABLE

Description

SQL_TXN_ISOLATION Transaction isolation level refers to the degree to which multiple interleaved transactions are prevented from interfering with each other in a multi-user database system. Ideally, one would like to have "serialised" transactions - that is, the interleaved execution of any set of concurrent transactions will produce the same effect as some (unspecified) serial execution of those same transactions. The ANSI SQL 2 standard defines three specific ways in which the serialisation of a transaction may be violated (with the implication that these are the only permitted violations):

1. Dirty Read: Transaction T1 modifies a row. T2 then reads the row. Now T1 performs a rollback - so, T2 has seen a row that never really existed.
2. Non-repeatable Read: T1 retrieves a row; then T2 updates that row and T1 retrieves the "same" row again. T1 has now effectively retrieved the "same" row twice and has seen two different values for it.
3. Phantoms: T1 reads a set of rows that satisfy certain search conditions. T2 then insert one or more rows that satisfy the same search condition. If T1 repeats the read, it will see rows that did not exist previously - "phantoms".

These phenomena are referred to as P1, P2 and P3, respectively. The various isolation levels are defined by SQL2 in terms of which of these violations of serialisation they permit. They are:

1. READ_UNCOMMITTED - Permits P1, P2 and P3.
2. READ_COMMITTED - Permits P2 and P3. Does not permit P1.
3. REPEATABLE_READ - Permits P3. Does not permit P1 and P2.
4. SERIALIZABLE - Does not permit any of P1, P2 and P3.

ODBC defines five isolation levels: SQL_TXN_READ_UNCOMMITTED , SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ , SQL_TXN_SERIALIZABLE , and SQL_TXN_VERSIONING . The first four correspond to ANSI isolation levels 1-4 respectively. SQL_TXN_VERSIONING provides SERIALIZABLE transactions, but does so without a significant impact on concurrency.

Transaction isolation is achieved by locking protocols. The various tables are or parts thereof are locked so that two writers cannot access it at the same time, or preventing reader access when writing is being done, and so on. One of the side effects of this is to drastically reduce concurrency. Typically, isolation levels 3 and 4 are achieved by locking protocols which drastically reduce concurrency. SQL_TXN_VERSIONING refers a non-locking way of achieving levels 3 and 4, thereby increasing concurrency.

As a result of these differences in implementation, an inter-operable ODBC application must explicitly set the transaction isolation level, instead of using the various implementations defined locking levels.

Default

SQL_TXN_VERSIONING

Example

SQL_TXN_ISOLATION=SQL_TXN_SERIALIZABLE

SQL_ERROR

Values 

an error code

Description

When a function did not complete because of an error. Call SQLError to get more information on the problem.

Default

driver generated

Example

SQL_ERROR=00000 , 0

SQL_CONNECT

Values 

a string | unset

Description

Enable addition of special connection options when connecting to SQL server

This is used in connection with PATCH3240.  When connecting with ORACLE DB through the direct specification of the database name the connection failed.  Comment: this was due to the lack of extension in the database_name  specification. A "workaround" solves the problem and this can be activated setting an environment variable setenv patch3240 yes  The default is to not use this patch.

Default

unset

Example

SQL_CONNECT=UID=root;PWD=%4£HNg

SQL_CURSOR_TYPE

Values 

SQL_CURSOR_DYNAMIC | unset

Description

Set to allow multiple cursors for ODBC connection on some databases such as SQL Server.  This option relates to the introduction of DB CURSORS which allows for multiple database cursors.  This variable enables the operation of either these multiple cursors or the previous behaviour.

Default

unset

Example

SQL_CURSOR_TYPE=SQL_CURSOR_DYNAMIC

MAX_DB_COL

Values 

a number

Description

Extend the number of columns for query set of any DB command

Default

100

Example

MAX_DB_COL=100:

DECODE_SEPERATOR

Values 

a character

Description

User definable decode separator for both for ENCODE and DECODE commands.

Default

unset

Example

DECODE_SEPERATOR :