To use a connection pool, an application needs to perform the following steps:
1 Enable connection pooling by calling SQLSetEnvAttr to set the SQL_ATTR_CONNECTION_POOLING environment attribute to SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV.
This call must be made before the application allocates the shared environment for which connection pooling is enabled. The environment handle in the call to SQLSetEnvAttr should be set to null, which makes SQL_ATTR_CONNECTION_POOLING a process-level attribute.
If the attribute is set to SQL_CP_ONE_PER_DRIVER, a single connection pool is supported for each driver. If an application works with many drivers and few environments, this might be more efficient because fewer comparisons might be required. If set to SQL_CP_ONE_PER_HENV, a single connection pool is supported for each environment.
If an application works with many environments and few drivers, this might be more efficient because fewer comparisons might be required. Connection pooling is disabled by setting SQL_ATTR_CONNECTION_POOLING to SQL_CP_OFF.
2 Allocate an environment by calling SQLAllocHandle with the HandleType argument set to SQL_HANDLE_ENV.
The environment allocated by this call is an implicit shared environment because connection pooling has been enabled. The environment used is not determined, however, until SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called on this environment.
3 Set up the environment attribute SQL_ATTR_ODBC_VERSION with the supported value of SQL_OV_ODBC3, or SQL_OV_ODBC2 using SQLSetEnvAttr.
An example call would be: SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
4 Allocate a connection by calling SQLAllocHandle with the InputHandle argument set to SQL_HANDLE_DBC, and the InputHandle set to the environment handle allocated for connection pooling.
The Driver Manager attempts to find an existing environment that matches the environment attributes set by the application. If no such environment exists, one is created, with a reference count (maintained by the Driver Manager) of 1.
If a matching shared environment is found, the environment is returned to the application and its reference count is incremented. (The actual connection to be used is not determined by the Driver Manager until SQLConnect or SQLDriverConnect is called.)
5 Call SQLConnect or SQLDriverConnect to make the connection.
The Driver Manager uses the connection options in the call to SQLConnect (or the connection keywords in the call to SQLDriverConnect) and the connection attributes set after connection allocation to determine which connection in the pool should be used.
How a requested connection is matched to a pooled connection is determined by the SQL_ATTR_CP_MATCH environment attribute.
6 Call SQLDisconnect when done with the connection.
The connection is returned to the connection pool and becomes available for reuse.
Note: For comprehensive coverage of Connection Pooling feature refer to MSDN and MSDN Help.