SQLSetConnectAttr--Set options related to a connection

Purpose

SQLSetConnectAttr() sets options related to a connection.

Syntax

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

Function arguments

Table 80. SQLSetConnectAttr arguments

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

Table 81. SQLSetConnectAttr SQLSTATEs

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

• Key to DB2 CLI function descriptions
• DB2 CLI function summary
• SQLEndTran--Request a COMMIT or ROLLBACK