SQLSetConnectAttr Function

SQLSetConnectAttr Function

Collapse All

Language Filter

SQLSetConnectAttr Function

Conformance: Version Introduced: ODBC 3.0 Standards Compliance: ISO 92

Summary

SQLSetConnectAttr sets attributes that govern aspects of connections.

Note
For more information about what the Driver Manager maps this function to when an ODBC 3.x application is working with an ODBC 2.x driver, see Mapping Replacement Functions for Backward Compatibility of Applications.

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

Arguments

ConnectionHandle: [Input] Connection handle.

Attribute: [Input] Attribute to set, listed in "Comments."

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 will point to a null-terminated character string. Note that if the Attribute argument is a driver-specific value, the value in ValuePtr may be a signed integer.

StringLength

[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. For character string data, this argument should contain the number of bytes in the string.

If Attribute is an ODBC-defined attribute and ValuePtr is an integer, StringLength is ignored.

If Attribute is a driver-defined attribute, the application indicates the nature of the attribute to the Driver Manager by setting the StringLength argument. StringLength can have the following values:

If ValuePtr is a pointer to a character string, then StringLength is the length of the string or SQL_NTS.
If ValuePtr is a pointer to a binary buffer, then 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, then StringLength should have the value SQL_IS_POINTER.
If ValuePtr contains a fixed-length value, then StringLength is either SQL_IS_INTEGER or SQL_IS_UINTEGER, as appropriate.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLSetConnectAttr returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_DBC and a Handle of ConnectionHandle. The following table lists the SQLSTATE values commonly returned by SQLSetConnectAttr and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

The driver can return SQL_SUCCESS_WITH_INFO to provide information about the result of setting an option.

SQLSTATE	Error	Description
01000	General warning	Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01S02	Option value changed	The driver did not support the value specified in ValuePtr and substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08002	Connection name in use	The Attribute argument was SQL_ATTR_ODBC_CURSORS, and the driver was already connected to the data source.
08003	Connection does not exist	(DM) An Attribute value was specified that required an open connection, but the ConnectionHandle was not in a connected state.
08S01	Communication link failure	The communication link between the driver and the data source to which the driver was connected failed before the function completed processing.
24000	Invalid cursor state	The Attribute argument was SQL_ATTR_CURRENT_CATALOG, and a result set was pending.
3D000	Invalid catalog name	The Attribute argument was SQL_CURRENT_CATALOG, and the specified catalog name was invalid.
HY000	General error	An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLGetDiagRec in the *MessageText buffer describes the error and its cause.
HY001	Memory allocation error	The driver was unable to allocate memory required to support execution or completion of the function.
HY009	Invalid use of null pointer	The Attribute argument identified a connection attribute that required a string value, and the ValuePtr argument was a null pointer.
HY010	Function sequence error	(DM) An asynchronously executing function was called for a StatementHandle associated with the ConnectionHandle and was still executing when SQLSetConnectAttr was called. (DM) SQLExecute, SQLExecDirect, SQLBulkOperations, or SQLSetPos was called for a StatementHandle associated with the ConnectionHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. (DM) SQLBrowseConnect was called for the ConnectionHandle and returned SQL_NEED_DATA. This function was called before SQLBrowseConnect returned SQL_SUCCESS_WITH_INFO or SQL_SUCCESS.
HY011	Attribute cannot be set now	The Attribute argument was SQL_ATTR_TXN_ISOLATION, and a transaction was open.
HY013	Memory management error	The function call could not be processed because the underlying memory objects could not be accessed, possibly because of low memory conditions.
HY024	Invalid attribute value	Given the specified Attribute value, an invalid value was specified in ValuePtr. (The Driver Manager returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE or SQL_ATTR_ASYNC_ENABLE. For all other connection and statement attributes, the driver must verify the value specified in ValuePtr.) The Attribute argument was SQL_ATTR_TRACEFILE or SQL_ATTR_TRANSLATE_LIB, and ValuePtr was an empty string.
HY090	Invalid string or buffer length	(DM) ValuePtr* is a character string, and the StringLength argument was less than 0 but was not SQL_NTS.
HY092	Invalid attribute/option identifier	(DM) The value specified for the argument Attribute was not valid for the version of ODBC supported by the driver. (DM) The value specified for the argument Attribute was a read-only attribute.
HYC00	Optional feature not implemented	The value specified for the argument Attribute was a valid ODBC connection or statement attribute for the version of ODBC supported by the driver but was not supported by the driver.
HYT01	Connection timeout expired	The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT.
IM001	Driver does not support this function	(DM) The driver associated with the ConnectionHandle does not support the function.
IM009	Unable to load translation DLL	The driver was unable to load the translation DLL that was specified for the connection. This error can be returned only when Attribute is SQL_ATTR_TRANSLATE_LIB.

When Attribute is a statement attribute, SQLSetConnectAttr can return any SQLSTATEs returned by SQLSetStmtAttr.

Comments

For general information about connection attributes, see Connection Attributes.

The currently defined attributes and the version of ODBC in which they were introduced are shown in the table later in this section; it is expected that more attributes will be defined to take advantage of different data sources. A range of attributes is reserved by ODBC; driver developers must reserve values for their own driver-specific use from Open Group.

Note
The ability to set statement attributes at the connection level by calling SQLSetConnectAttr has been deprecated in ODBC 3.x. ODBC 3.x applications should never set statement attributes at the connection level. ODBC 3.x statement attributes cannot be set at the connection level, with the exception of the SQL_ATTR_METADATA_ID and SQL_ATTR_ASYNC_ENABLE attributes, which are both connection attributes and statement attributes and can be set at either the connection level or the statement level. ODBC 3.x drivers need only support this functionality if they should work with ODBC 2.x applications that set ODBC 2.x statement options at the connection level. For more information, see SQLSetConnectOption Mapping in Appendix G: Driver Guidelines for Backward Compatibility.

Note

The ability to set statement attributes at the connection level by calling SQLSetConnectAttr has been deprecated in ODBC 3.x. ODBC 3.x applications should never set statement attributes at the connection level. ODBC 3.x statement attributes cannot be set at the connection level, with the exception of the SQL_ATTR_METADATA_ID and SQL_ATTR_ASYNC_ENABLE attributes, which are both connection attributes and statement attributes and can be set at either the connection level or the statement level.

ODBC 3.x drivers need only support this functionality if they should work with ODBC 2.x applications that set ODBC 2.x statement options at the connection level. For more information, see SQLSetConnectOption Mapping in Appendix G: Driver Guidelines for Backward Compatibility.

An application can call SQLSetConnectAttr at any time between the time the connection is allocated and freed. All connection and statement attributes successfully set by the application for the connection persist until SQLFreeHandle is called on the connection. For example, if an application calls SQLSetConnectAttr before connecting to a data source, the attribute persists even if SQLSetConnectAttr fails in the driver when the application connects to the data source; if an application sets a driver-specific attribute, the attribute persists even if the application connects to a different driver on the connection.

Some connection attributes can be set only before a connection has been made; others can be set only after a connection has been made. The following table indicates those connection attributes that must be set either before or after a connection has been made. Either indicates that the attribute can be set either before or after connection.

Attribute	Set before or after connection?
SQL_ATTR_ACCESS_MODE	Either[1]
SQL_ATTR_ASYNC_ENABLE	Either[2]
SQL_ATTR_AUTOCOMMIT	Either
SQL_ATTR_CONNECTION_TIMEOUT	Either
SQL_ATTR_CURRENT_CATALOG	Either[1]
SQL_ATTR_LOGIN_TIMEOUT	Before
SQL_ATTR_METADATA_ID	Either
SQL_ATTR_ODBC_CURSORS	Before
SQL_ATTR_PACKET_SIZE	Before
SQL_ATTR_QUIET_MODE	Either
SQL_ATTR_TRACE	Either
SQL_ATTR_TRACEFILE	Either
SQL_ATTR_TRANSLATE_LIB	After
SQL_ATTR_TRANSLATE_OPTION	After
SQL_ATTR_TXN_ISOLATION	Either[3]

[1] SQL_ATTR_ACCESS_MODE and SQL_ATTR_CURRENT_CATALOG can be set before or after connecting, depending on the driver. However, interoperable applications set them before connecting because some drivers do not support changing these after connecting.

[2] SQL_ATTR_ASYNC_ENABLE must be set before there is an active statement.

[3] SQL_ATTR_TXN_ISOLATION can be set only if there are no open transactions on the connection. Some connection attributes support substitution of a similar value if the data source does not support the value specified in *ValuePtr. In such cases, the driver returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). For example, if Attribute is SQL_ATTR_PACKET_SIZE and *ValuePtr exceeds the maximum packet size, the driver substitutes the maximum size. To determine the substituted value, an application calls SQLGetConnectAttr.

The format of information set in the *ValuePtr buffer depends on the specified Attribute. SQLSetConnectAttr will accept 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 bytes.

The StringLength argument is ignored if the length is defined by the attribute, as is the case for all attributes introduced in ODBC 2.x or earlier.

Attribute

ValuePtr contents

SQL_ATTR_ACCESS_MODE (ODBC 1.0)

An SQLUINTEGER value. SQL_MODE_READ_ONLY is used by the driver or data source as an indicator that the connection is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. The driver is not required to prevent such statements from being submitted to the data source. The behavior of the driver and data source when asked to process SQL statements that are not read-only during a read-only connection is implementation-defined. SQL_MODE_READ_WRITE is the default.

SQL_ATTR_ASYNC_ENABLE (ODBC 3.0)

An SQLUINTEGER value that specifies whether a function called with a statement on the specified connection is executed asynchronously:

SQL_ASYNC_ENABLE_OFF = Off (the default) SQL_ASYNC_ENABLE_ON = On

Setting SQL_ASYNC_ENABLE_ON enables asynchronous execution for all future statement handles allocated on this connection. It is driver-defined whether this enables asynchronous execution for existing statement handles associated with this connection. An error is returned if asynchronous execution is enabled while there is an active statement on the connection.

This attribute can be set whether SQLGetInfo with the SQL_ASYNC_MODE information type returns SQL_AM_CONNECTION or SQL_AM_STATEMENT.

After a function has been called asynchronously, only the original function, SQLAllocHandle, SQLCancel, SQLGetDiagField, or SQLGetDiagRec can be called on the statement or the connection associated with StatementHandle, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on StatementHandle or the connection associated with StatementHandle returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error). Functions can be called on other statements. For more information, see Asynchronous Execution.

In general, applications should execute functions asynchronously only on single-thread operating systems. On multithread operating systems, applications should execute functions on separate threads rather than executing them asynchronously on the same thread. Drivers that operate only on multithread operating systems do not need to support asynchronous execution.

The following functions can be executed asynchronously:

SQLBulkOperations

SQLColAttribute

SQLColumnPrivileges

SQLColumns

SQLCopyDesc

SQLDescribeCol

SQLDescribeParam

SQLExecDirect

SQLExecute

SQLFetch

SQLFetchScroll

SQLForeignKeys

SQLGetData

SQLGetDescField[1]

SQLGetDescRec[1]

SQLGetDiagField

SQLGetDiagRec

SQLGetTypeInfo

SQLMoreResults

SQLNumParams

SQLNumResultCols

SQLParamData

SQLPrepareSQLPrimaryKeys

SQLProcedureColumns

SQLProcedures

SQLPutData

SQLSetPos

SQLSpecialColumnsSQLStatistics

SQLTablePrivileges

SQLTables

SQL_ATTR_AUTO_IPD (ODBC 3.0)

A read-only SQLUINTEGER value that specifies whether automatic population of the IPD after a call to SQLPrepare is supported:

SQL_TRUE = Automatic population of the IPD after a call to SQLPrepare is supported by the driver.

SQL_FALSE = Automatic population of the IPD after a call to SQLPrepare is not supported by the driver. Servers that do not support prepared statements will not be able to populate the IPD automatically.

If SQL_TRUE is returned for the SQL_ATTR_AUTO_IPD connection attribute, the statement attribute SQL_ATTR_ENABLE_AUTO_IPD can be set to turn automatic population of the IPD on or off. If SQL_ATTR_AUTO_IPD is SQL_FALSE, SQL_ATTR_ENABLE_AUTO_IPD cannot be set to SQL_TRUE. The default value of SQL_ATTR_ENABLE_AUTO_IPD is equal to the value of SQL_ATTR_AUTO_IPD.

This connection attribute can be returned by SQLGetConnectAttr but cannot be set by SQLSetConnectAttr.

SQL_ATTR_AUTOCOMMIT (ODBC 1.0)

An SQLUINTEGER value that specifies whether to use autocommit or manual-commit mode:

SQL_AUTOCOMMIT_OFF = The driver uses manual-commit mode, and the application must explicitly commit or roll back transactions with SQLEndTran.

SQL_AUTOCOMMIT_ON = The driver uses autocommit mode. Each statement is committed immediately after it is executed. This is the default. Any open transactions on the connection are committed when SQL_ATTR_AUTOCOMMIT is set to SQL_AUTOCOMMIT_ON to change from manual-commit mode to autocommit mode.

For more information, see Commit Mode.

Important

Some data sources delete the access plans and close the cursors for all statements on a connection each time a statement is committed; autocommit mode can cause this to happen after each nonquery statement is executed or when the cursor is closed for a query. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo and Effect of Transactions on Cursors and Prepared Statements.

When a batch is executed in autocommit mode, two things are possible. The entire batch can be treated as an autocommitable unit, or each statement in a batch is treated as an autocommitable unit. Certain data sources can support both these behaviors and may provide a way of choosing one or the other. It is driver-defined whether a batch is treated as an autocommitable unit or whether each individual statement within the batch is autocommitable.

SQL_ATTR_CONNECTION_DEAD

(ODBC 3.5)

A read-only SQLUINTERGER value that indicates the state of the connection. If SQL_CD_TRUE, the connection has been lost. If SQL_CD_FALSE, the connection is still active.

SQL_ATTR_CONNECTION_TIMEOUT (ODBC 3.0)

An SQLUINTEGER value corresponding to the number of seconds to wait for any request on the connection to complete before returning to the application. The driver should return SQLSTATE HYT00 (Timeout expired) anytime that it is possible to time out in a situation not associated with query execution or login.

If ValuePtr is equal to 0 (the default), there is no timeout.

SQL_ATTR_CURRENT_CATALOG (ODBC 2.0)

A character string containing the name of the catalog to be used by the data source. For example, in SQL Server, the catalog is a database, so the driver sends a USE database statement to the data source, where database is the database specified in *ValuePtr. For a single-tier driver, the catalog might be a directory, so the driver changes its current directory to the directory specified in *ValuePtr.

SQL_ATTR_LOGIN_TIMEOUT (ODBC 1.0)

An SQLUINTEGER value corresponding to the number of seconds to wait for a login request to complete before returning to the application. The default is driver-dependent. If ValuePtr is 0, the timeout is disabled and a connection attempt will wait indefinitely.

If the specified timeout exceeds the maximum login timeout in the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).

SQL_ATTR_METADATA_ID (ODBC 3.0)

An SQLUINTEGER value that determines how the string arguments of catalog functions are treated.

If SQL_TRUE, the string argument of catalog functions are treated as identifiers. The case is not significant. For nondelimited strings, the driver removes any trailing spaces and the string is folded to uppercase. For delimited strings, the driver removes any leading or trailing spaces and takes literally whatever is between the delimiters. If one of these arguments is set to a null pointer, the function returns SQL_ERROR and SQLSTATE HY009 (Invalid use of null pointer).

If SQL_FALSE, the string arguments of catalog functions are not treated as identifiers. The case is significant. They can either contain a string search pattern or not, depending on the argument.

The default value is SQL_FALSE.

The TableType argument of SQLTables, which takes a list of values, is not affected by this attribute.

SQL_ATTR_METADATA_ID can also be set on the statement level. (It is the only connection attribute that is also a statement attribute.)

For more information, see Arguments in Catalog Functions.

SQL_ATTR_ODBC_CURSORS (ODBC 2.0)

An SQLUINTEGER value specifying how the Driver Manager uses the ODBC cursor library:

SQL_CUR_USE_IF_NEEDED = The Driver Manager uses the ODBC cursor library only if it is needed. If the driver supports the SQL_FETCH_PRIOR option in SQLFetchScroll, the Driver Manager uses the scrolling capabilities of the driver. Otherwise, it uses the ODBC cursor library.

SQL_CUR_USE_ODBC = The Driver Manager uses the ODBC cursor library.

SQL_CUR_USE_DRIVER = The Driver Manager uses the scrolling capabilities of the driver. This is the default setting.

For more information about the ODBC cursor library, see Appendix F: ODBC Cursor Library.

SQL_ATTR_PACKET_SIZE (ODBC 2.0)

An SQLUINTEGER value specifying the network packet size in bytes.

Note
Many data sources either do not support this option or only can return but not set the network packet size.

If the specified size exceeds the maximum packet size or is smaller than the minimum packet size, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).

If the application sets packet size after a connection has already been made, the driver will return SQLSTATE HY011 (Attribute cannot be set now).

SQL_ATTR_QUIET_MODE (ODBC 2.0)

A window handle (HWND).

If the window handle is a null pointer, the driver does not display any dialog boxes.

If the window handle is not a null pointer, it should be the parent window handle of the application. This is the default. The driver uses this handle to display dialog boxes.

Note
The SQL_ATTR_QUIET_MODE connection attribute does not apply to dialog boxes displayed by SQLDriverConnect.

SQL_ATTR_TRACE (ODBC 1.0)

An SQLUINTEGER value telling the Driver Manager whether to perform tracing:

SQL_OPT_TRACE_OFF = Tracing off (the default)

SQL_OPT_TRACE_ON = Tracing on

When tracing is on, the Driver Manager writes each ODBC function call to the trace file.

Note
When tracing is on, the Driver Manager can return SQLSTATE IM013 (Trace file error) from any function.

An application specifies a trace file with the SQL_ATTR_TRACEFILE option. If the file already exists, the Driver Manager appends to the file. Otherwise, it creates the file. If tracing is on and no trace file has been specified, the Driver Manager writes to the file SQL.LOG in the root directory.

An application can set the variable ODBCSharedTraceFlag to enable tracing dynamically. Tracing is then enabled for all ODBC applications currently running. If an application turns tracing off, it is turned off only for that application.

If the Trace keyword in the system information is set to 1 when an application calls SQLAllocHandle with a HandleType of SQL_HANDLE_ENV, tracing is enabled for all handles. It is enabled only for the application that called SQLAllocHandle.

Calling SQLSetConnectAttr with an Attribute of SQL_ATTR_TRACE does not require that the ConnectionHandle argument be valid and will not return SQL_ERROR if ConnectionHandle is NULL. This attribute applies to all connections.

SQL_ATTR_TRACEFILE (ODBC 1.0)

A null-terminated character string containing the name of the trace file.

The default value of the SQL_ATTR_TRACEFILE attribute is specified with the TraceFile keyword in the system information. For more information, see ODBC Subkey.

Calling SQLSetConnectAttr with an Attribute of SQL_ATTR_ TRACEFILE does not require the ConnectionHandle argument to be valid and will not return SQL_ERROR if ConnectionHandle is invalid. This attribute applies to all connections.

SQL_ATTR_TRANSLATE_LIB (ODBC 1.0)

A null-terminated character string containing the name of a library containing the functions SQLDriverToDataSource and SQLDataSourceToDriver that the driver accesses to perform tasks such as character set translation. This option may be specified only if the driver has connected to the data source. The setting of this attribute will persist across connections. For more information about translating data, see Translation DLLs and Translation DLL Function Reference.

SQL_ATTR_TRANSLATE_OPTION (ODBC 1.0)

A 32-bit flag value that is passed to the translation DLL. This attribute can be specified only if the driver has connected to the data source. For information about translating data, see Translation DLLs.

SQL_ATTR_TXN_ISOLATION (ODBC 1.0)

A 32-bit bitmask that sets the transaction isolation level for the current connection. An application must call SQLEndTran to commit or roll back all open transactions on a connection, before calling SQLSetConnectAttr with this option.

The valid values for ValuePtr can be determined by calling SQLGetInfo with InfoType equal to SQL_TXN_ISOLATION_OPTIONS.

For a description of transaction isolation levels, see the description of the SQL_DEFAULT_TXN_ISOLATION information type in SQLGetInfo and Transaction Isolation Levels.

[1] These functions can be called asynchronously only if the descriptor is an implementation descriptor, not an application descriptor.

Code Example

See SQLConnect.

Related Functions