`SQLSetConnectAttr()` - Set connection attributes

SQLSetConnectAttr() sets attributes that govern aspects of connections.

ODBC specifications for `SQLSetConnectAttr()`

Table 1. `SQLSetConnectAttr()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
3.0	Yes	Yes

Syntax

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

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLSetConnectAttr()` arguments
Data type	Argument	Use	Description
SQLHDBC	ConnectionHandle	input	Connection handle.
SQLINTEGER	Attribute	input	Connection attribute to set. Refer to Table 4 for a complete list of attributes.
SQLPOINTER	ValuePtr	input	Pointer to the value to be associated with Attribute. Depending on the value of Attribute, ValuePtr will be a 32-bit unsigned integer value or point to a nul-terminated character string. If the Attribute argument is a driver-specific value, the value in ValuePtr might be a signed integer.
SQLINTEGER	StringLength	input	Information about the ValuePtr argument. For ODBC-defined attributes: If ValuePtr points to a character string, this argument should be the length of ValuePtr. If ValuePtr points to an integer, BufferLength is ignored. For driver-defined attributes (IBM® extension): If ValuePtr points to a character string, this argument should be the length of *ValuePtr or SQL_NTS if it is a nul-terminated string. If ValuePtr points to an integer, BufferLength is ignored.

Usage

An application can call SQLSetConnectAttr() at any time between the time the connection is allocated or freed. All connection and statement attributes successfully set by the application for the connection persist until SQLFreeHandle() is called on the connection.

Some connection attributes can be set only before or after a connection is made. Other attributes cannot be set after a statement is allocated. The following table indicates when each of the connection attributes can be set.

Table 3. When connection attributes can be set
Attribute	Before connection	After connection	After statements are allocated
SQL_ATTR_ACCESS_MODE	Yes	Yes	Yes¹
SQL_ATTR_AUTOCOMMIT	Yes	Yes	Yes²
SQL_ATTR_CLIENT_TIME_ZONE	Yes	Yes	Yes
SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION	Yes	Yes	Yes
SQL_ATTR_CONNECTTYPE	Yes	No	No
SQL_ATTR_CURRENT_SCHEMA	Yes	Yes	Yes
SQL_ATTR_DB2EXPLAIN	Yes	Yes	Yes
SQL_ATTR_DECFLOAT_ROUNDING_MODE	Yes	Yes	Yes
SQL_ATTR_EXTENDED_INDICATORS	Yes	Yes	Yes
SQL_ATTR_INFO_ACCTSTR	No	Yes	Yes
SQL_ATTR_INFO_APPLNAME	No	Yes	Yes
SQL_ATTR_INFO_USERID	No	Yes	Yes
SQL_ATTR_INFO_WRKSTNNAME	No	Yes	Yes
SQL_ATTR_KEEP_DYNAMIC	No	Yes	Yes ^{1, 2}
SQL_ATTR_MAXCONN	Yes	No	No
SQL_ATTR_SESSION_TIME_ZONE	Yes	No	No
SQL_ATTR_SYNC_POINT	Yes	No	No
SQL_ATTR_TXN_ISOLATION	No	Yes	Yes
Notes: Attribute only affects subsequently allocated statements. Attribute can be set only if all transactions on the connections are closed.

Table 4 lists the SQLSetConnectAttr() Attribute values. Values shown in bold are default values unless they are otherwise specified in the ODBC initialization file. Db2 ODBC supports all of the ODBC 2.0 Attribute values that are renamed in ODBC 3.0.

For a summary of the Attribute values renamed in ODBC 3.0, refer to "Changes to SQLSetConnectAttr() attributes".

ODBC applications that need to set statement attributes should use SQLSetStmtAttr(). The ability to set statement attributes on the connect level is supported, but it is not recommended.

Table 4. Connection attributes
Attribute	ValuePtr
SQL_ATTR_ACCESS_MODE	A 32-bit integer value which can be either: SQL_MODE_READ_ONLY Indicates that the application is not performing any updates on data from this point on. Therefore, a less restrictive isolation level and locking can be used on transactions; that is, uncommitted read (SQL_TXN_READ_UNCOMMITTED). Db2 ODBC does not ensure that requests to the database are read-only. If an update request is issued, Db2 ODBC processes it using the transaction isolation level it selected as a result of the SQL_MODE_READ_ONLY setting. QL_MODE_READ_WRITE Indicates that the application is making updates on data from this point on. Db2 ODBC goes back to using the default transaction isolation level for this connection. SQL_MODE_READ_WRITE is the default. This connection must have no outstanding transactions.
SQL_ATTR_AUTOCOMMIT¹	A 32-bit integer value that specifies whether to use autocommit or manual commit mode: SQL_AUTOCOMMIT_OFF The application must manually, explicitly commit or rollback transactions with `SQLEndTran()` calls. SQL_AUTOCOMMIT_ON Db2 ODBC operates in autocommit mode. Each statement is implicitly committed. Each statement, that is not a query, is committed immediately after it has been executed. Each query is committed immediately after the associated cursor is closed. This is the default value. Exception: If the connection is a coordinated distributed unit of work connection, the default is SQL_AUTOCOMMIT_OFF. When specifying autocommit, the application can have only one outstanding statement per connection. For example, two cursors cannot be open, otherwise unpredictable results can occur. An open cursor must be closed before another query is executed. Because in many Db2 environments the execution of the SQL statements and the commit can be flowed separately to the database server, autocommit can be expensive. The application developer should take this into consideration when selecting the autocommit mode. Changing from manual-commit to autocommit mode commits any open transaction on the connection. For information about setting this attribute see the topic Disable autocommit to reduce network flow.
SQL_ATTR_CLIENT_TIME_ZONE	A null-terminated character string in the format ±hh:mm. The supported time zone values range from -12:59 through +14:00. SQL_ATTR_CLIENT_TIME_ZONE can be set on the connection and statement handle. When set on a connection handle, the attribute value will be the default for every statement handle that is allocated on the connection.
SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION	A 32-bit integer value that specifies how the application resolves concurrent access to locked data: 0 No setting. This is the default value. 1 USE CURRENTLY COMMITTED. Read transactions can access the currently committed version of the data when the data is in the process of being updated or deleted. Rows that are in the process of being inserted are skipped. After this value is set through SQLSetConnectAttr(), all user SELECT statements for that connection are prepared with the USE CURRENTLY COMMITTED attribute. This option applies only when cursor stability (CS) isolation is in effect. 2 WAIT FOR OUTCOME. Read transactions that require access to data that is in the process of being updated or deleted must wait for a COMMIT or ROLLBACK operation to complete. Rows in the process of being inserted are not skipped. After this value is set through SQLSetConnectAttr(), all user SELECT statements for that connection are prepared with the WAIT FOR OUTCOME attribute. 3 SKIP LOCKED DATA. Read transactions can skip any rows that are incompatibly locked by other transactions. After this value is set through SQLSetConnectAttr(), all user SELECT statements for that connection are prepared with the SKIP LOCKED DATA attribute. This option applies only when cursor stability or read stability (RS) or cursor stability (CS) isolation are in effect. SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION can be set before or after a connection is made. It can also be set after statements are allocated, however it will only affect subsequently allocated statements.
SQL_ATTR_CONNECTTYPE²	A 32-bit integer value that specifies whether this application is to operate in a coordinated or uncoordinated distributed environment. If the processing needs to be coordinated, then this attribute must be considered in conjunction with the SQL_ATTR_SYNC_POINT connection attribute. The possible values are: SQL_CONCURRENT_TRANS The application can have concurrent multiple connections to any one database or to multiple databases. This attribute value corresponds to the specification of the type 1 CONNECT in embedded SQL. Each connection has its own commit scope. No effort is made to enforce coordination of transaction. The current setting of the SQL_ATTR_SYNC_POINT attribute is ignored. This is the default. SQL_COORDINATED_TRANS The application wants to have commit and rollbacks coordinated among multiple database connections. This attribute value corresponds to the specification of the type 2 CONNECT in embedded SQL and must be considered in conjunction with the SQL_ATTR_SYNC_POINT connection attribute. In contrast to the SQL_CONCURRENT_TRANS setting described above, the application is permitted only one open connection per database. Important: This connection type results in the default for SQL_ATTR_AUTOCOMMIT connection attribute to be SQL_AUTOCOMMIT_OFF. This attribute must be set before making a connect request; otherwise, the `SQLSetConnectAttr()` call is rejected. All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT values. The first connection determines the acceptable attributes for the subsequent connections. IBM specific: This attribute is an IBM-defined extension. Recommendation: Have the application set the SQL_ATTR_CONNECTTYPE attribute at the environment level rather than on a per connection basis. ODBC applications written to take advantage of coordinated Db2 transactions must set these attributes at the connection level for each connection as `SQLSetEnvAttr()` is not supported in ODBC.
SQL_ATTR_CURRENT_SCHEMA	A nul-terminated character string containing the name of the schema to be used by Db2 ODBC for the `SQLColumns()` call if the szSchemaName pointer is set to null. To reset this attribute, specify this attribute with a zero length or a null pointer for the vParam argument. This attribute is useful when the application developer has coded a generic call to `SQLColumns()` that does not restrict the result set by schema name, but needs to constrain the result set at isolated places in the code. This attribute can be set at any time and is effective on the next `SQLColumns()` call where the szSchemaName pointer is null. IBM specific: This attribute is an IBM-defined extension.
SQL_ATTR_DB2EXPLAIN	A 32-bit integer value that specifies whether EXPLAIN information should be gathered. This attribute sets the CURRENT EXPLAIN MODE special register. You can specify one of the following values: SQL_DB2EXPLAIN_OFF Sets the CURRENT EXPLAIN MODE special register to NO, which disables the EXPLAIN facility. SQL_DB2EXPLAIN_MODE_ON Sets the CURRENT EXPLAIN MODE special register to YES, which enables the EXPLAIN facility. If you enable the EXPLAIN facility, you must meet the following requirements: The EXPLAIN tables must exist. The current authorization ID must have INSERT privilege for the EXPLAIN tables. The new SQL_ATTR_DB2EXPLAIN setting is effective on the next statement preparation for this connection. Alternatively, you can set the CURRENT EXPLAIN MODE special register for ODBC applications by using the DB2EXPLAIN initialization keyword or the SET CURRENT EXPLAIN MODE SQL statement. If you want to set the CURRENT EXPLAIN MODE special register to EXPLAIN, you must use the SET CURRENT EXPLAIN MODE SQL statement. IBM specific: This attribute is an IBM-defined extension.
SQL_ATTR_DECFLOAT_ROUNDING_MODE	A 32-bit integer value that lets an application control the rounding mode for DECFLOAT data type values. Possible values are: ROUND_HALF_EVEN Round to the nearest integer. If the value is equidistant from two integers, round so that the final digit is even. ROUND_HALF_UP Round to the nearest integer. If the value is equidistant from two integers, round up. ROUND_DOWN Round toward 0. This is equivalent to truncation. ROUND_CEILING Round toward positive infinity. ROUND_FLOOR Round toward negative infinity. ROUND_HALF_DOWN Round to the nearest integer. If the value is equidistant from two integers, round down. ROUND_UP Round away from zero.
SQL_ATTR_EXTENDED_INDICATORS	A 32-bit integer value that overrides the EXTENDEDINDICATOR initialization keyword value. SQL_TRUE Extended indicator support will be enabled. SQL_FALSE Extended indicator support is not enabled. SQL_FALSE is the default value.
SQL_ATTR_INFO_ACCTSTR	A null-terminated character string used to identify the client accounting string to the host database. The length of the attribute value must not exceed 255 characters. Some servers might not be able to handle the entire length of the value provided and might truncate it. If truncation occurs, users will not see any truncation warnings. To reset this attribute, specify this attribute with a zero length. This attribute is ignored if MVSATTACHTYPE=CAF is specified in the initialization file, the application created a Db2 thread using CAF before invoking Db2 ODBC, or the application is a stored procedure.
SQL_ATTR_INFO_APPLNAME	A null-terminated character string used to identify the client accounting string to the host database. The length of the attribute value must not exceed 255 characters. Some servers might not be able to handle the entire length of the value provided and might truncate it. If truncation occurs, users will not see any truncation warnings. To reset this attribute, specify this attribute with a zero length. This attribute is ignored if MVSATTACHTYPE=CAF is specified in the initialization file, the application created a Db2 thread using CAF before invoking Db2 ODBC, or the application is a stored procedure.
SQL_ATTR_INFO_USERID	A null-terminated character string used to identify the client accounting string to the host database. The length of the attribute value must not exceed 128 characters. Some servers might not be able to handle the entire length of the value provided and might truncate it. If truncation occurs, users will not see any truncation warnings. To reset this attribute, specify this attribute with a zero length. This attribute is ignored if MVSATTACHTYPE=CAF is specified in the initialization file, the application created a Db2 thread using CAF before invoking Db2 ODBC, or the application is a stored procedure.
SQL_ATTR_INFO_WRKSTNNAME	A null-terminated character string used to identify the client accounting string to the host database. The length of the attribute value must not exceed 255 characters. Some servers might not be able to handle the entire length of the value provided and might truncate it. If truncation occurs, users will not see any truncation warnings. To reset this attribute, specify this attribute with a zero length. This attribute is ignored if MVSATTACHTYPE=CAF is specified in the initialization file, the application created a Db2 thread using CAF before invoking Db2 ODBC, or the application is a stored procedure.
SQL_ATTR_KEEP_DYNAMIC	A 32-bit integer value that specifies whether the KEEPDYNAMIC bind option is enabled. When this option is enabled, the data source keeps dynamically prepared statements in a prepared state across transaction boundaries 0 KEEPDYNAMIC functionality is not available. ODBC packages were bound with KEEPDYNAMIC(NO) option. 0 is the default value. 1 KEEPDYNAMIC functionality is available. ODBC packages were bound with KEEPDYNAMIC(YES) option. This attribute is supported only for access to Db2 for z/OS® data sources.
SQL_ATTR_MAXCONN³	A 32-bit integer value corresponding to the number of maximum concurrent connections that an application wants to set up. The default value is 0, which means no maximum - the application is allowed to set up as many connections as the system resources permit. The integer value must be 0 or a positive number. This can be used as a governor for the maximum number of connections on a per application basis. The value that is in effect when the first connection is established is the value that is used. When the first connection is established, attempts to change this value are rejected. IBM specific: This attribute is an IBM-defined extension. Recommendation: Have the application set SQL_ATTR_MAXCONN at the environment level rather then on a connection basis. ODBC applications must set this attribute at the connection level because `SQLSetEnvAttr()` is not supported in ODBC.
SQL_ATTR_SESSION_TIME_ZONE	A null-terminated character string in the format ±hh:mm, containing the server session time zone information. The supported time zone values range from -12:59 through +14:00. This attribute must be set before making a connect request; otherwise, the SQLSetConnectAttr() call is rejected.
SQL_ATTR_SYNC_POINT	A 32-bit integer value that allows the application to choose between one-phase coordinated transactions and two-phase coordinated transactions. The possible values are: SQL_ONEPHASE The Db2 ODBC 3.0 driver does not support SQL_ONEPHASE. SQL_TWOPHASE Two-phase commit is used to commit the work done by each database in a multiple database transaction. This requires the use of a transaction manager to coordinate two-phase commits among the databases that support this protocol. Multiple readers and multiple updaters are allowed within a transaction. This attribute is only used when SQL_ATTR_CONNECTTYPE attribute is SQL_COORDINATED_TRANS. Then SQL_TWOPHASE is the default. This attribute is ignored when SQL_ATTR_CONNECTTYPE is set to SQL_CONCURRENT_TRANS. This attribute must be set before a connect request. Otherwise the attribute set request is rejected. All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT values. The first connection determines the acceptable attributes for the subsequent connections. Recommendation: Ensure that your application sets the SQL_ATTR_CONNECTTYPE attribute at the environment level rather than at a connection level.
SQL_ATTR_TXN_ISOLATION⁴	A 32-bit bit mask that sets the transaction isolation level for the current connection referenced by hdbc. The valid values for vParam can be determined at run time by calling `SQLGetInfo()` with InfoType set to SQL_TXN_ISOLATION_OPTION. The following values are accepted by Db2 ODBC, but each server might only support a subset of these isolation levels: SQL_TXN_READ_UNCOMMITTED Dirty reads, reads that cannot be repeated, and phantoms are possible. SQL_TXN_READ_COMMITTED Dirty reads are not possible. Reads that cannot be repeated, and phantoms are possible. This is the default. SQL_TXN_REPEATABLE_READ Dirty reads and reads that cannot be repeated are not possible. Phantoms are possible. SQL_TXN_SERIALIZABLE Transactions can be serialized. Dirty reads, non-repeatable reads, and phantoms are not possible. SQL_TXN_NOCOMMIT Any changes are effectively committed at the end of a successful operation; no explicit commit or rollback is allowed. This is analogous to autocommit. This is not an ANSI/ISO SQL standard of 1992 isolation level, but an IBM defined extension, supported only by Db2 for i. In IBM terminology, SQL_TXN_READ_UNCOMMITTED is uncommitted read. SQL_TXN_READ_COMMITTED is cursor stability. SQL_TXN_REPEATABLE_READ is read stability. SQL_TXN_SERIALIZABLE is repeatable read. This attribute cannot be specified while there is an open cursor on any statement handle, or an outstanding transaction for this connection; otherwise, SQL_ERROR is returned on the function call (SQLSTATE HY011). Tip: An IBM extension enables you to set transaction isolation levels on each individual statement handle. See the SQL_ATTR_STMTTXN_ISOLATION attribute in the function description for `SQLSetStmtAttr()`.
Notes: You can change the default value for this attribute with the AUTOCOMMIT keyword in the ODBC initialization file. You can change the default value for this attribute with the CONNECTTYPE keyword in the ODBC initialization file. You can change the default value for this attribute with the MAXCONN keyword in the ODBC initialization file. You can change the default value for this attribute with the TXNISOLATION keyword in the ODBC initialization file.

Return codes

After you call SQLSetConnectAttr(), it returns one of the following values:

SQL_SUCCESS
SQL_INVALID_HANDLE
SQL_ERROR

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 5. `SQLSetConnectAttr()` SQLSTATEs
SQLSTATE	Description	Explanation
01000	Warning.	Informational message. (`SQLSetConnectAttr()` returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
01S02	Option value changed.	SQL_ATTR_SYNC_POINT changed to SQL_TWOPHASE. SQL_ONEPHASE is not supported.
08S01	Unable to connect to data source.	The communication link between the application and the data source failed before the function completed.
08003	Connection is closed.	An Attribute value is specified that requires an open connection, but the ConnectionHandle is not in a connected state.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate memory for the specified handle.
HY009	Invalid use of a null pointer.	A null pointer is passed for ValuePtr and the value in *ValuePtr is a string value.
HY010	Function sequence error.	`SQLExecute()` or `SQLExecDirect()` is called with the statement handle, and returned SQL_NEED_DATA. This function is called before data is sent for all data-at-execution parameters or columns. Invoke `SQLCancel()` to cancel the data-at-execution condition.
HY011	Operation invalid at this time.	The argument Attribute is SQL_ATTR_TXN_ISOLATION and a transaction is open.
HY024	Invalid attribute value.	Given the specified Attribute value, an invalid value is specified in *ValuePtr.
HY090	Invalid string or buffer length.	The StringLength argument is less than 0, but is not SQL_NTS.
HY092	Option type out of range.	The value specified for the argument Attribute is not valid for this version of Db2 ODBC.
HYC00	Driver not capable.	The value specified for the argument Attribute is a valid connection or statement attribute for this version of the Db2 ODBC driver, but is not supported by the data source.

Example

The following example uses SQLConnectAttr() to set statement attribute values:

rc=SQLSetConnectAttr( hdbc,SQL_ATTR_AUTOCOMMIT,
                      (void*) SQL_AUTOCOMMIT_OFF, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

SQLSetConnectAttr() - Set connection attributes

ODBC specifications for SQLSetConnectAttr()