USING ODBC Connection Pooling with CDatabase (Under MFC)
Rating:
None
James r. twine () August 3, 1999
Environment: Visual C 6This 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 OverviewConnection 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 NewsConnection 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 itTo 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 Me 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 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 INTERLY. Note That this is a good idea anyway! Try IT and you will see an omprovement, Even if you do not use connections pooling!
The Bad NewsNow 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 Class for the cdatabase-derived class thing i buy. It is very simple.
Now That you have all this information
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
