Connection pooling allows an application to reuse a connection that has already been created. When connection pooling is enabled and SQLConnect is called, the Driver Manager tries 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.

For more information on connection pooling, see Connection Pooling.

Connection pooling is enabled before the environment is allocated by calling SQLSetEnvAttr 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). SQLSetEnvAttr 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.

After connection pooling has been enabled, SQLAllocHandle 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. However, the environment that will be used is not determined until SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called.

SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called to allocate a connection. The Driver Manager tries 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.

However, the connection that will be used is not determined until SQLConnect is called. At that point, the Driver Manager tries 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 SQLConnect (the values of the ServerName, UserName, and Authentication keywords) and any connection attributes set since SQLAllocHandle with a HandleType of SQL_HANDLE_DBC was called. The Driver Manager checks these 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 SQLConnect must match but not all the connection attributes must match.

The following rules are applied when a connection attribute, as set by the application before SQLConnect 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 SQLDisconnect to disconnect, the connection is returned to the connection pool and is available for reuse.

When distributed transactions are involved, it is possible to optimize connection pooling performance by using SQL_DTC_TRANSITION_COST, which is a SQLUINTEGER bitmask. The transitions referred to are the transitions of the connection attribute SQL_ATTR_ENLIST_IN_DTC going from value 0 to nonzero, and vice versa. This is a connection going from not enlisted in a distributed transaction to enlisted in a distributed transaction, and vice versa. Depending on how the driver has implemented enlistment (setting connection attribute SQL_ATTR_ENLIST_IN_DTC), these transitions may be expensive and should therefore be avoided for best performance.

The value returned by the driver contains any combination of the following bits:

• SQL_DTC_ENLIST_EXPENSIVE, when set, implies the zero to nonzero transition is significantly more expensive than a transition from nonzero to another nonzero value (enlisting a previously enlisted connection in its next transaction).

• SQL_DTC_UNENLIST_EXPENSIVE, when set, implies the nonzero to zero transition is significantly more expensive than using a connection whose SQL_ATTR_ENLIST_IN_DTC attribute is already set to zero.

There is a performance versus connection usage tradeoff. If a driver indicates that one or more of these transitions are expensive, the driver manager's connection pooler responds to this by keeping more connections in the pool. Some of the connections in the pool are preferred for nontransactional use, and some are preferred for transactional use. However, if the driver indicates that these transitions are not expensive, fewer connections can be used, perhaps alternating between nontransactional and transactional use.

Drivers that do not support SQL_ATTR_ENLIST_IN_DTC do not need to support SQL_DTC_TRANSITION_COST. For drivers that support SQL_ATTR_ENLIST_IN_DTC but not SQL_DTC_TRANSITION_COST, it is assumed that the transitions are not expensive, as if the driver returned 0 (no bits set) for this value.

Although SQL_DTC_TRANSITION_COST was introduced in ODBC 3.5, an ODBC 2.x driver can also support it because the driver manager will query this information regardless of the driver version.

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.

// SQLConnect_ref.cpp
// compile with: odbc32.lib
#include <windows.h>
#include <sqlext.h>

int main() {
   SQLHENV henv;
   SQLHDBC hdbc;
   SQLHSTMT hstmt;
   SQLRETURN retcode;

   SQLCHAR * OutConnStr = (SQLCHAR * )malloc(255);
   SQLSMALLINT * OutConnStrLen = (SQLSMALLINT *)malloc(255);

   // Allocate environment handle
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

   // Set the ODBC version environment attribute
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 

      // Allocate connection handle
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
         retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 

         // Set login timeout to 5 seconds
         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

            // Connect to data source
            retcode = SQLConnect(hdbc, (SQLCHAR*) "NorthWind", SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);

            // Allocate statement handle
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
               retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 

               // Process data
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
               }

               SQLDisconnect(hdbc);
            }

            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
         }
      }
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
   }
}