INFO: How to Enable Connection Pooling in an ODBC Application

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article describes how to enable connection pooling in an ODBCapplication.
More information
Microsoft ODBC version 3.0 introduced the concept of connection pooling.
Connection pooling enables an ODBC application to reuse an existing
connection from a pool, so the ODBC application does not have togo
through the complete connection process for any subsequent connection.

When an ODBC application disconnects a connection, the connection is saved
into a pool instead of actually disconnected. How long this connectionremains
in the pool depends on the CPTimeout property of the ODBC driver.When the
timeout expires, the connection is closed and removed fromthe pool. The ODBC
application can use SQLConfigDriver to change the valueof CPTimeout, and this
value applies to all the ODBC applications that areusing the specified ODBC driver.

The default value for the CPTimeout is 60seconds.Connection pooling is very useful when an ODBC application like MicrosoftInternet
Information Server (for example) connects and disconnectsfrequently. Microsoft
Internet Information Server (IIS) version 3.0 withActive Server Pages (ASP)
takes advantage of connection pooling. You canenable connection pooling for
IIS users by changing the value ofStartConnectionPool to 1. StartConnectionPool is located under:
An ODBC application can use SQLSetEnvAttr to enable connection pooling. Following is an example of how to enable connection pooling in an ODBCapplication:
   #include <windows.h>   #include <stdio.h>   #include <sql.h>   #include <sqlext.h>   void main()   {   SQLHENV   henv;   SQLHDBC   hdbc;   int       i=0;   if (!SQL_SUCCEEDED(SQLSetEnvAttr(      NULL,  // make process level cursor pooling      SQL_ATTR_CONNECTION_POOLING,      (SQLPOINTER)SQL_CP_ONE_PER_DRIVER,      SQL_IS_INTEGER)))   printf("SQLSetEnvAttr/SQL_ATTR_CONNECTION_POOLING error\n");   if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv)))      printf("SQLAllocHandle error\n");   // set the ODBC behavior version.   if (!SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,   (SQLPOINTER) SQL_OV_ODBC2, SQL_IS_INTEGER)))      printf("SQLSetEnvAttr/SQL_ATTR_ODBC_VERSION error\n");   //set the matching condition for using an existing connection in the   pool   if (!SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_CP_MATCH,   (SQLPOINTER) SQL_CP_RELAXED_MATCH, SQL_IS_INTEGER)))   printf("SQLSetEnvAttr/SQL_ATTR_CP_MATCH error\n");while (i < 10) {   if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC,      henv, &hdbc)))      printf("SQLAllocHandle error\n");      if (!SQL_SUCCEEDED(SQLConnect(hdbc,   (unsigned char*)"testing\0", SQL_NTS,      (unsigned char*)"sa\0", SQL_NTS,   (unsigned char*)"\0", SQL_NTS)))      printf("SQLConnect error\n");   else      printf("Connect successfully %d times\n", i);   //the first time, the application calls SQLDisconenct, it will return                 //the connection to the //pool   SQLDisconnect(hdbc);   if (!SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_DBC, hdbc)))      printf("SQLFreeHandle error\n");   i++;   }   SQLFreeHandle(SQL_HANDLE_ENV, henv);   }				
When the ODBC application calls SQLDisconnect the first time, theconnection is saved to the pool. Any subsequent SQLConnect/SQLDisconnectthat matches the required condition will reuse the first connection.
viper asp

Article ID: 164221 - Last Review: 06/22/2014 18:14:00 - Revision: 3.0

  • kbinfo kbprogramming KB164221