SQLSetEnvAttr() - Set environment attributes

SQLSetEnvAttr() sets attributes that affects all connections in an environment.

ODBC specifications for SQLSetEnvAttr()

Table 1. SQLSetEnvAttr() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
No Yes Yes

Syntax

SQLRETURN  SQLSetEnvAttr (SQLHENV           EnvironmentHandle,
                          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. SQLSetEnvAttr() arguments
Data type Argument Use Description
SQLHENV EnvironmentHandle input Environment handle.
SQLINTEGER Attribute input Environment attribute to set. See Table 3 for the list of attributes and their descriptions.
SQLPOINTER ValuePtr input The value for Attribute.
SQLINTEGER StringLength input The length of ValuePtr in bytes if the attribute value is a character string. If Attribute does not denote a string, Db2 ODBC ignores StringLength.

Usage

When set, the attribute value affects all connections in this environment.

The application can obtain the current attribute value by calling SQLGetEnvAttr().

Table 3 lists the SQLSetEnvAttr() Attribute values. The values that are shown in bold are default values.

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

Table 3. Environment attributes
Attribute Contents
SQL_ATTR_ODBC_VERSION A 32-bit integer that determines whether certain functionality exhibits ODBC 2.0 behavior or ODBC 3.0 behavior. This value cannot be changed while any connection handles are allocated.
The following values are used to set the value of this attribute:
  • SQL_OV_ODBC3: Causes the following ODBC 3.0 behavior:
    • Db2 ODBC returns and expects ODBC 3.0 data type codes for date, time, and timestamp.
    • Db2 ODBC returns ODBC 3.0 SQLSTATE codes when SQLGetDiagRec() is called.
    • The CatalogName argument in a call to SQLTables() accepts a search pattern.
  • SQL_OV_ODBC2 causes the following ODBC 2.x behavior:
    • Db2 ODBC returns and expects ODBC 2.x data type codes for date, time, and timestamp.
    • Db2 ODBC returns ODBC 2.0 SQLSTATE codes when SQLGetDiagRec() or SQLError() are called.
    • The CatalogName argument in a call to SQLTables() does not accept a search pattern.
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_OUTPUT_NTS A 32-bit integer value which controls the use of nul-termination in output arguments. The possible values are:
  • SQL_TRUE: Db2 ODBC uses nul-termination to indicate the length of output character strings.

    This is the default.

  • SQL_FALSE: Db2 ODBC does not use nul-termination in output character strings.
The CLI functions affected by this attribute are all functions called for the environment (and for any connections and statements allocated under the environment) that have character string parameters.

This attribute can only be set when no connection handles are allocated under the environment handle.
SQL_ATTR_CONNECTTYPE1 A 32-bit integer value that specifies whether this application is to operate in a coordinated or uncoordinated distributed environment. The possible values are:
  • SQL_CONCURRENT_TRANS: Each connection has its own commit scope. No effort is made to enforce coordination of transaction. If an application issues a commit using the environment handle on SQLEndTran() and not all of the connections commit successfully, the application is responsible for recovery. This corresponds to CONNECT (type 1) semantics subject to the restrictions described in Db2 ODBC restrictions on the ODBC connection model.

    This is the default.

  • SQL_COORDINATED_TRANS: The application wants to have commit and rollbacks coordinated among multiple database connections. In contrast to the SQL_CONCURRENT_TRANS setting described above, the application is permitted only one open connection per database.

This attribute must be set before allocating any connection handles, otherwise, the SQLSetEnvAttr() call is rejected.

All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT values. This attribute can also be set using the SQLSetConnectAttr() function.

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 using SQLSetConnectAttr() as SQLSetEnvAttr() is not supported in ODBC.
SQL_ATTR_MAXCONN2 A 32-bit integer value corresponding to the number that 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 this attribute is not supported in ODBC.
Notes:
  1. You can change the default value for this attribute with the CONNECTTYPE keyword in the ODBC initialization file.
  2. You can change the default value for this attribute with the MAXCONN keyword in the ODBC initialization file.

Return codes

After you call SQLSetEnvAttr(), 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 4. SQLSetEnvAttr() SQLSTATEs
SQLSTATE Description Explanation
HY009 Invalid use of a null pointer. A null pointer is passed for ValuePtr and the value in *ValuePtr is a string value.
HY011 Operation invalid at this time. Applications cannot set environment attributes while connection handles are allocated on the environment handle.
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 specified Attribute is not supported by Db2 ODBC. Given specified Attribute value, the value specified for the argument ValuePtr is not supported.

Example

The following example uses SQLSetEnvAttr() to set an environment attribute. Also, see the topic Functions for establishing a distributed unit-of-work connection. 
SQLINTEGER output_nts,autocommit;
rc = SQLSetEnvAttr( henv,
                   SQL_ATTR_OUTPUT_NTS,
                   ( SQLPOINTER ) output_nts,
                   0
                  ) ;
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );