Environment, connection, and statement attributes in CLI applications

Environments, connections, and statements each have a defined set of attributes or options. All attributes can be queried by the application, but only some attributes can be changed from their default values. By changing attribute values, the application can change the behavior of CLI.

An environment handle has attributes which affect the behavior of CLI functions under that environment. The application can specify the value of an attribute by calling SQLSetEnvAttr() and can obtain the current attribute value by calling SQLGetEnvAttr(). SQLSetEnvAttr() can only be called before any connection handles have been allocated for the environment handle. For details on each environment attribute, see the list of CLI environment attributes.

A connection handle has attributes which affect the behavior of CLI functions under that connection. Of the attributes that can be changed:
  • Some can be set any time after the connection handle is allocated.
  • Some can be set only before the actual connection has been established.
  • Some can be set any time after the connection has been established.
  • Some can be set after the connection has been established, but only while there are no outstanding transactions or open cursors.

The application can change the value of connection attributes by calling SQLSetConnectAttr() and can obtain the current value of an attribute by calling SQLGetConnectAttr(). An example of a connection attribute which can be set any time after a handle is allocated is the auto-commit option SQL_ATTR_AUTOCOMMIT. For details on each connection attribute, see the list of CLI connection attributes.

A statement handle has attributes which affect the behavior of CLI functions executed using that statement handle. Of the statement attributes that can be changed:
  • Some attributes can be set, but currently are limited to only one specific value.
  • Some attributes can be set any time after the statement handle has been allocated.
  • Some attributes can only be set if there is no open cursor on that statement handle.

The application can specify the value of any statement attribute that can be set by calling SQLSetStmtAttr() and can obtain the current value of an attribute by calling SQLGetStmtAttr(). For details on each statement attribute, see the list of CLI statement attributes.

The SQLSetConnectAttr() function cannot be used to set statement attributes. This was supported in versions of CLI before version 5.

Many applications just use the default attribute settings; however, there might be situations where some of these defaults are not suitable for a particular user of the application. Some default values can be changed by setting the CLI/ODBC configuration keywords. CLI provides end users with two methods of setting some configuration keywords. The first method is to specify the keyword and its new default attribute value(s) in the connection string input to the SQLDriverConnect() and SQLBrowseConnect() functions. The second method involves the specification of the new default attribute value(s) in a CLI initialization file using CLI/ODBC configuration keywords.

The CLI initialization file can be used to change default values for all CLI applications on that workstation. This might be the end user's only means of changing the defaults if the application does not provide a means for the user to provide default attribute values in the SQLDriverConnect() connection string. Default attribute values that are specified on SQLDriverConnect() override the values in the CLI initialization file for that particular connection.

The mechanisms for changing defaults are intended for end user tuning; application developers must use the appropriate set-attribute function. If an application does call a set-attribute or option function with a value different from the initialization file or the connection string specification, then the initial default value is overridden and the new value takes effect.

The figure-1 shows the addition of the attribute functions to the basic connect scenario.

Figure 1. Setting and retrieving attributes (options)
Setting and retrieving attributes (options)