Using ODBC Connection Pooling with CDatabase (under MFC)

Environment: Visual C++ 6

This article shows how you can use ODBC Connection Pooling with MFC’s CDatabase
and CRecordset objects. It will explain the way in which I did it, which I am
sure is not the only way it can be done. Also, I am no Database expert, so I
cannot explain all of the internal details of what is going on.

Note that in order for this to work correctly (the way I did it), you are
going to have to derive a class from CDatabase, and override the OpenEx(…)
method. More details on this follow…

Brief Overview

Connection pooling is a feature of the (some?) ODBC drivers that holds
onto connections to a Database server behind-the-scenes. When a
connection is requested, and the driver already is holding on to one,
it hands over the connection it already has. This prevents constant
round-trips to the server to create and tear down connections.

The Good News

Connection Pooling can speed up your Database-related operations.
You will see a marked increase in speed if you are constantly
constructing and destructing CRecordset-derived objects and running
queries with them.

How to do it

To enable Connection Pooling, you need to make the following calls
before you create your first CDatabase object (I put the following
calls into CWinApp::InitInstance(), and the m_shSQLEnv data member
a member of my CWinApp-derived class). This tells the ODBC driver
to set the global (process-level) ODBC Environment to use Connection
Pooling (if available), and to maintain one connection per driver
(in production code, you will check the return values, of course!):

//   m_shSQLEnv Is A SQLHANDLE Member That Must Be Freed When Your
//   Application Terminated
SQLRETURN srRetCode = 0;
        (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, 0 );                  // Enable Connection Pooling
srRetCode = SQLAllocHandle( SQL_HANDLE_ENV, NULL, &m_shSQLEnv ); // Get Global Handle
Later on, I free the m_shSQLEnv handle in my CWinApp::ExitInstance() function:

if( m_shSQLEnv )                                                 // If Environment Handle For Connection Pooling Obtained
    SQLFreeHandle( SQL_HANDLE_ENV, m_shSQLEnv );                 // Free It

In order to use this correctly, you need to keep a globally accessable
(read: shared) single instance of a CDatabase object. (Actually,
this is going to be a CDatabase-derived object, but more on that
later.) Again, this CDatabase-derived member is a member of my
CWinApp-derived class, and I provide an accessor function for it
that returns it address. This accessor function will be used by
all CRecordset objects when they are constructed, so that they do
not create their own CDatabase class internally. Note that this
is a good idea anyway! Try it and you will see an improvement,
even if you do not use Connection Pooling!

The Bad News

Now here is the kicker… Connection Pooling requires the ODBC
version of the CDatabase::noOdbcDialog flag. Since you do not
(have to) handle opening and closing the CDatabase object (that
is where Connection Pooling comes in), you are going to find an
interesting problem. When CRecordset-derived objects call Open(…)
on the internal CDatabase object that they have, there is no way
(that I have found) to pass over the CDatabase::noOdbcDialog flag,
which is required when using Connection Pooling.

My solution to this problem was to derive a class from CDatabase,
and overide the OpenEx(…) method, which Open(…) calls, to always
add in the CDatabase::noOdbcDialog flag. That solved my problem.
You can download the code for the CDatabase-derived class that I
used. It is very simple.

Now that you have all this information, here are the steps
required to use it:

  • Create (or download) the CDatabase-derived class, and add it to your Project
  • Create a member of the CDatabase-derived class in your CWinApp-derived (Application) class
  • Create a SQLHANDLE data member in your Application class for the Enviroment Handle
  • Add an accessor function that returns the address of the CDatabase-derived member you added in the previous step
  • Make the necessary SQLSetEnvAttr(…) and SQLAllocHandle(…) calls as specified above
  • Initially open your CDatabase-derived object (if you are using SQL Authentication)
  • Whenever you instantiate a CRecordset-derived object, pass the address of the CDatabase-derived class into its constructor

That should be all that is required, as that is what I am doing, and
(so far) it seems to be working for me.


Download source – 1 Kb

More by Author

Must Read