f90SQLConnect (SQLConnect)

Conformance

Version Introduced: ODBC 1.0

Standards Compliance: ISO 92

Summary

f90SQLConnect establishes connections to a driver and a data source. The connection handle references storage of all information about the connection to the data source, including status, transaction state, and error information.

Syntax

Arguments

ConnectionHandle [Input]

Connection handle.

ServerName [Input]

Character string with a data source name.

UserName [Input]

Character string with a User identifier.

Authentication [Input]

Character string with Authentication data (typically the password).

iRet [Output]

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When f90SQLConnect returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling f90SQLGetDiagRec with a HandleType of SQL_HANDLE_DBC and a Handle of ConnectionHandle. The following table lists the SQLSTATE values commonly returned by f90SQLConnect 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.

Comments

The Driver Manager does not connect to a driver until the application calls a function (f90SQLConnect, f90SQLDriverConnect, or f90SQLBrowseConnect) to connect to the driver. Until that point, the Driver Manager works with its own handles and manages connection information. When the application calls a connection function, the Driver Manager checks whether a driver is currently connected to for the specified ConnectionHandle:

• If a driver is not connected to, the Driver Manager connects to the driver and calls f90SQLAllocHandle with a HandleType of SQL_HANDLE_ENV, f90SQLAllocHandle with a HandleType of SQL_HANDLE_DBC, f90SQLSetConnectAttr (if the application specified any connection attributes), and the connection function in the driver. The Driver Manager returns SQLSTATE IM006 (Driver's SQLSetConnectOption failed) and SQL_SUCCESS_WITH_INFO for the connection function if the driver returned an error for f90SQLSetConnectAttr.

• If the specified driver is already connected to on the ConnectionHandle, the Driver Manager only calls the connection function in the driver. In this case, the driver must make sure that all connection attributes for the ConnectionHandle maintain their current settings.

• If a different driver is connected to, the Driver Manager calls f90SQLFreeHandle with a HandleType of SQL_HANDLE_DBC, and then, if no other driver is connected to in that environment, it calls f90SQLFreeHandle with a HandleType of SQL_HANDLE_ENV in the connected driver and then disconnects that driver. It then performs the same operations as when a driver is not connected to.

The driver then allocates handles and initializes itself.

When the application calls f90SQLDisconnect, the Driver Manager calls f90SQLDisconnect in the driver. However, it does not disconnect the driver. This keeps the driver in memory for applications that repeatedly connect to and disconnect from a data source. When the application calls f90SQLFreeHandle with a HandleType of SQL_HANDLE_DBC, the Driver Manager calls f90SQLFreeHandle with a HandleType of SQL_HANDLE_DBC and then f90SQLFreeHandle with a HandleType of SQL_HANDLE_ENV in the driver, and then disconnects the driver.

An ODBC application can establish more than one connection.

Connection Pooling

Connection pooling allows an application to reuse a connection that has already been created. When connection pooling is enabled and f90SQLConnect is called, the Driver Manager attempts to make the connection using a connection that is part of a pool of connections in an environment that has been designated for connection pooling. This environment is a shared environment that is used by all applications that use the connections in the pool.

Connection pooling is enabled before the environment is allocated by calling f90SQLSetEnvAttr to set SQL_ATTR_CONNECTION_POOLING to SQL_CP_ONE_PER_DRIVER (which specifies a maximum of one pool per driver) or SQL_CP_ONE_PER_HENV (which specifies a maximum of one pool per environment). f90SQLSetEnvAttr in this case is called with EnvironmentHandle set to null, which makes the attribute a process-level attribute. If SQL_ATTR_CONNECTION_POOLING is set to SQL_CP_OFF, connection pooling is disabled.

Once connection pooling has been enabled, f90SQLAllocHandle with a HandleType of SQL_HANDLE_ENV is called to allocate an environment. The environment allocated by this call is a shared environment because connection pooling has been enabled. The environment to be used is not determined, however, until f90SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called.

f90SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called to allocate a connection. The Driver Manager attempts to find an existing shared environment that matches the environment attributes set by the application. If no such environment exists, one is created as an implicit shared environment. If a matching shared environment is found, the environment handle is returned to the application, and its reference count is incremented.

The connection to be used is not determined, however, until f90SQLConnect is called. At that point, the Driver Manager attempts to find an existing connection in the connection pool that matches the criteria requested by the application. These criteria include the connection options requested in the call to f90SQLConnect (the values of the ServerName, UserName, and Authentication keywords) and any connection attributes set since f90SQLAllocHandle with a HandleType of SQL_HANDLE_DBC was called. The Driver Manager checks this criteria against the corresponding connection keywords and attributes in connections in the pool. If a match is found, the connection in the pool is used. If no match is found, a new connection is created.

If the SQL_ATTR_CP_MATCH environment attribute is set to SQL_CP_STRICT_MATCH, the match must be exact for a connection in the pool to be used. If the SQL_ATTR_CP_MATCH environment attribute is set to SQL_CP_RELAXED_MATCH, the connection options in the call to f90SQLConnect must match, but not all of the connection attributes must match.

The following rules are applied when a connection attribute, as set by the application before f90SQLConnect is called, does not match the connection attribute of the connection in the pool:

• If the connection attribute must be set before the connection is made:
  • If SQL_ATTR_CP_MATCH is SQL_CP_STRICT_MATCH, SQL_ATTR_PACKET_SIZE in the pooled connection must be identical to the attribute set by the application. If SQL_CP_RELAXED_MATCH, the values of SQL_ATTR_PACKET_SIZE can be different.
  • The value of SQL_ATTR_LOGIN_VALUE does not affect the match.

• If the connection attribute can be set either before or after the connection is made:
  • If the connection attribute has not been set by the application, but has been set on the connection in the pool, and there is a default, the connection attribute in the pooled connection is set back to the default, and a match is declared. If there is no default, the pooled connection is not considered a match.
  • If the connection attribute has been set by the application, but has not been set on the connection in the pool, the connection attribute on the pool is changed to that set by the application, and a match is declared.
  • If the connection attribute has been set by the application, and has also been set on the connection in the pool, but the values are different, the value of the application's connection attribute is used, and a match is declared.

• If the values of driver-specific connection attributes are not identical and SQL_ATTR_CP_MATCH is set to SQL_CP_STRICT_MATCH, the connection in the pool is not used.

When the application calls f90SQLDisconnect to disconnect, the connection is returned to the connection pool, and is available for reuse.

Code Example

In the following example, an application allocates environment and connection handles. It then connects to the SalesOrders data source with the user ID JohnS and the password Sesame and processes data. When it has finished processing data, it disconnects from the data source and frees the handles.

Related Subroutines