Using ODBC Connection Pooling with CDatabase (under MFC)

Using ODBC Connection Pooling with CDatabase (under MFC)
Rating: none

James R. Twine () August 3, 1999

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.
(continued)

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;srRetCode = SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,         (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, 0 );                  // Enable Connection PoolingsrRetCode = SQLAllocHandle( SQL_HANDLE_ENV, NULL, &m_shSQLEnv ); // Get Global HandleLater 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.

Downloads

Download source - 1 Kb