SQLSetConnectAttr--Set options related to a connection

Purpose

Specification:

DB2 CLI

ODBC 1.0

ISO CLI

SQLSetConnectAttr() sets options related to a connection.

Syntax

SQLRETURN   SQLSetConnectAttr   (SQLHDBC        ConnectionHandle,   
                              SQLINTEGER        Attribute,         
                              SQLPOINTER        ValuePtr,          
                              SQLINTEGER        StringLength);

Function arguments

ǥ 80. SQLSetConnectAttr arguments

Data type	Argument	Use	Description
SQLHDBC	`ConnectionHandle`	input	Connection handle.
SQLINTEGER	`Attribute`	input	Option to set.
SQLPOINTER	`ValuePtr`	input	If `Attribute` is an ODBC-defined attribute and `ValuePtr` points to a character string or a binary buffer, this argument should be the length of `ValuePtr`. If `Attribute` is an ODBC-defined attribute and ValuePtr is an integer, `StringLength` is ignored. If `Attribute` is a DB2 CLI attribute, the application indicates the nature of the attribute by setting the `StringLength` argument. `StringLength` can have the following values: If `ValuePtr` is a pointer to a character string, `StringLength` is the length of the string or SQL_NTS. If `ValuePtr` is a pointer to a binary buffer, the application places the result of the SQL_LEN_BINARY_ATTR(length) macro in `StringLength`. This places a negative value in `StringLength`. If `ValuePtr` is a pointer to a value other than a character string or a binary string, `StringLength` should have the value SQL_IS_POINTER. If `ValuePtr` contains a fixed-length value, `StringLength` is either SQL_IS_INTEGER or SQL_IS_UINTEGER, as appropriate.
SQLINTEGER	`StringLength`	input	If `ValuePtr` points to a character string or a binary buffer, this argument should be the length of `ValuePtr`. If `ValuePtr` is a pointer, but not to a string or binary buffer, `StringLength` should have the value SQL_IS_POINTER. If `ValuePtr` is not a pointer, `StringLength` should have the value SQL_IS_NOT_POINTER.

Usage

Connection attributes for a connection remain in effect until they are changed by another call to SQLSetConnectAttr() or the connection is dropped by calling SQLDisconnect().

SQLSetConnectAttr() accepts attribute information in one of two different formats: a null-terminated character string or a 32-bit integer value. The format of each is noted in the attribute's description. Character strings pointed to by the ValuePtr argument of SQLSetConnectAttr() have a length of StringLength.

Connection attributes

The currently defined attributes are shown below.

SQL_ATTR_AUTOCOMMIT (DB2 CLI/ODBC)

A 32-bit integer value that specifies the mode type. The supported values are:

SQL_AUTOCOMMIT_ON = Each statement is automatically committed. This is the default.
In auto-commit mode, all updates performed by a statement are made persistent automatically after the statement is executed. Auto-commit mode is the default behavior. By default, transaction support is not enabled and furthermore, statement level atomicity is not guaranteed. For example, the following UPDATE statement might fail during processing and only a subset of rows might be updated:
```
UPDATE T SET A = A + 1
```
There might be many reasons why the update/delete/insert operations fail. For example, a check constraint can be violated during an update. As a result, a portion of the table can be updated properly while the rest of the table cannot, and the changes cannot be rolled back.
SQL_AUTOCOMMIT_OFF = The application must manually, explicitly commit or rollback a transaction. Committing or rolling back a transaction is accomplished by calling SQLEndTran(). For more information about using SQLEndTran(), see SQLEndTran--Request a COMMIT or ROLLBACK.
In manual-commit mode, transactions are started implicitly with the first access to the database using SQLPrepare() and SQLExecDirect(). At this point a transaction has begun, even if the call failed. The transaction ends when you use SQLEndTran() to either ROLLBACK or COMMIT the transaction.
In manual-commit mode, transactions can issue any SQL statements, including DDL and DML (for example, CREATE TABLE or UPDATE statements).

SQL_ATTR_CONNECTION_DEAD (DB2 CLI/ODBC)

A READ ONLY 32-bit integer value that indicates whether or not the connection is still active. DB2 CLI will return one of the following values:

SQL_CD_FALSE - the connection is still active.
SQL_CD_TRUE - the connection is dead.

SQL_ATTR_LOGIN_TIMEOUT (DB2 CLI/ODBC)

A 32-bit integer value corresponding to the number of seconds to wait for a login request to complete before returning control to the application.

SQL_ATTR_FILENAME_FORMAT (DB2 Everyplace)

A 32-bit integer specifies whether DB2e database engine should create filenames in long or 8.3 format. Applications are allowed to change filename format only if no catalog files exist in the path connected when SQLSetConnectAttr is invoked. SQL_ERROR with SQLState HY000 will be returned if changing filename format is denied due to pre-existing catalog files. For example, if an application connects to a path where DB2 Everyplace catalog files already exist, any attempts to change filename format will fail. If an application connects to a path where no DB2 Everyplace catalog files exist and it attempts to change filename format after the first CREATE TABLE statement, SQLSetConnectAttr will return SQL_ERROR too. This is because catalog files are created during the very first CREATE TABLE statement, and it is not allowed to change filename format after the creation of catalog files. The default filename format is platform dependent. SQL_FILENAME_FORMAT_LONG is currently the default for all platforms supported.

Attribute values:

 SQL_FILENAME_FORMAT_LONG - files will be created in long filename format.

 SQL_FILENAME_FORMAT_83- files will be created in 8.3 filename format.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

ǥ 81. SQLSetConnectAttr SQLSTATEs

SQLSTATE	Description	Explanation
HY000	General error.	Filename format cannot be changed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.

Restrictions

The number of tables that can be updated inside a transaction is limited. DB2 Everyplace permits a maximum of 256 open files inside a transaction, assuming that the operating system also permits this many open files. This typically means that approximately 100 tables can be updated. The number of tables depends on index usage and number of statement handles. As the number of active statement handles increases, fewer tables can potentially be updated. Each table is counted only one time even if it is accessed or updated several times inside a transaction.
Transactions were added to DB2 Everyplace to allow consistent updating and insertion of several related records in a number of tables. Changes are written to the data tables after the application commits the transaction.
If the application terminated prematurely without committing the current transaction, the updates within that transaction are rolled back automatically.
After the SQLEndTran returns, the transaction is either committed or rolled back.
When an application connects to a database that terminated prematurely (during an active transaction) then the transaction is recovered. The database recovers transactions using the following logic:
- If the transaction is not complete, the database is not be updated.
- If the transaction is complete, the database is updated with the information from that transaction.
- If the recovery is interrupted, the appropriate action is performed at the next connect.

Related reference