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;
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 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


Comments

  • how i can support print to datagrid

    Posted by Legacy on 04/16/2003 12:00am

    Originally posted by: tarek omr

    how i can support print to recordsource which is loacte in datagrid ?
    
    and i can't find crystal report in vc++

    Reply
  • some confusion's

    Posted by Legacy on 02/20/2003 12:00am

    Originally posted by: harry

    hello sir/mam,
    i m developing a project on lib mang. through but i m facing some problems
    such as i wanna how to call exe files of different SDI applications from any MDI application
    please solve my queries.
    harry

    Reply
  • coding

    Posted by Legacy on 11/22/2002 12:00am

    Originally posted by: erwin castro

    i suggest that there must be an explantion of the source code, on how does the program work.

    Reply
  • how to add new records for more than one table at the sametime in access2000 through MFC:ODBC

    Posted by Legacy on 08/10/2002 12:00am

    Originally posted by: priya

    tried to add new records through MFC:ODBC connectiviy. When I tried to add new records for "more" than 1 table at the same time in a mdb file, it say RecordSet is readonly exception. any idea or comments on how to solve this.

    Reply
  • How to Add and Delete Data in your Databes

    Posted by Legacy on 10/06/2001 12:00am

    Originally posted by: Yongama Kendle

    I'm still a biginner with MFC, so please help me on how to Add new records and Delete existing ones to your database using MFCs

    Reply
  • I want to Edit, Update some records in my ODBCDatabase?

    Posted by Legacy on 07/06/2001 12:00am

    Originally posted by: Nguyenletien

    I want to Edit, Update some records in my ODBCDatabase? But I don't want to use Class Wizard. I connected My database, I accessed My database, I can read field's value but I don't know how to edit my recordset? Can you tell me the way to write this code? Thank you very much for your help.

    Reply
  • Open a crecordset

    Posted by Legacy on 05/11/2001 12:00am

    Originally posted by: Jesus

    Is there a way to avoid the ODBC dialog with the function open of Crecodset.
    In a bucle, there is always an invocation to the function open of the crecordset, but when the link with the server is down, i need that the program does not show the message
    "Connection failed...." and then, when click ok there is another message "sql server login".
    Is there a way to avoid those messages and continue with the next line.

    Reply
  • Will MFC actually use the environment stored in m_shSQLEnv?

    Posted by Legacy on 04/11/2000 12:00am

    Originally posted by: PeterK

    Now I agree that this section of code will set up Connection Pooling.

    srRetCode = SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,
    (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, 0 ); // Enable Connection Pooling

    According to the on-line help:

    "Connection pooling is enabled before the environment is allocated by calling SQLSetEnvAttr to set SQL_ATTR_CONNECTION_POOLING to SQL_CP_ONE_PER_DRIVER (which specifies a maximum of one pool per driver) or SQL_CP_ONE_PER_HENV (which specifies a maximum of one pool per environment). SQLSetEnvAttr in this case is called with EnvironmentHandle set to null, which makes the attribute a process-level attribute. If SQL_ATTR_CONNECTION_POOLING is set to SQL_CP_OFF, connection pooling is disabled."

    Now I am a little confused about the handle to the environment. Here the author allocates a handle to an environment:

    srRetCode = SQLAllocHandle( SQL_HANDLE_ENV, NULL, &m_shSQLEnv ); // Get Global Handle

    So the program stores this handle in m_shSQLEnv. But from what I have read MFC uses it's own environment and the only way to get at the handle to the environment is to call AfxGetHENV().

    According to the on-line help:

    "HENV AFXAPI AfxGetHENV( );

    Return Value

    The handle to the ODBC environment currently in use by MFC. Can be SQL_HENV_NULL if there are no CDatabase objects and no CRecordset objects in use.

    Remarks

    You can use the returned handle in direct ODBC calls, but you must not close the handle or assume that the handle is still valid and available after any existing CDatabase- or CRecordset-derived objects have been destroyed."

    This handle is automatically allocated in CDatabase::AllocConnect() and is then stored in m_henvAllConnections which is a member of an _AFX_DB_STATE object. This source code can be found in dbcore.cpp.

    So although this may indeed enable Connection Pooling is it really safe to use the m_shSQLEnv member variable for anything at all? I think not. However removing the allocation of the environment and the associated member variable from this example should go a long way toward enabling Connection Pooling.

    Reply
  • It may not be necessary

    Posted by Legacy on 10/29/1999 12:00am

    Originally posted by: Thach Ngo

    If you are using the latest ODBC Administrator version 3.510.xxx (which comes with Visual Studio 6.0), there is a Connection Pooling property page which allows you to configure the pooling time out.
    All you need to do is call the database the normal way (without having to specify connection pooling) and the ODBC administrator will actually handle the connection pooling for you. There is also a Perfmon enable button which if enabled will allow you to monitor the database connection using Perform Monitor.

    Reply
  • This is only for client applications (MFC applications)

    Posted by Legacy on 10/17/1999 12:00am

    Originally posted by: Gary MacDougall

    Many moons ago, before ATL (or too early to use ATL), my company had some engineers that decided to write an IIS DNA application.  They used the CDatabase classes to write all of the ActiveX scriptable components on the IIS server side.  Not a bad idea considering they were leveraging existing MFC technology and it was a fast way to ramp up the application development time (everyone was MFC savvy).  
    
    

    Once the application was finished, there was a major flaw in it that we only found out after we released it to the general public (it scaled horribly) because:

    A.) ODBC Connection Pooling wasn't implemented, and isn't not possible with CDatabase within MFC Active Components.

    B.) the MFC components where "blocking" in the IIS request queue would "lunch line" every thread (the apartment model was in fact STA, but it appeared that MFC had too much blocking code to be consdered a scalable component architecture)

    It took us 6 months to re-architect and re-write the damn thing to get it straight. We learned a lot, but this was between 1998 and 1999--on the cusp of the ADO/ATL convergance--so we were living the "bleeding edge" sort to speak.

    It's my opinion that the ONLY way to go with database programming these days is ADO and ATL. Using CDatabase on the client is ok, I guess, but NEVER use this on the server... please, learn from our mistakes. It was ugly.

    Connection pooling is supported as a "no brainer" with ADO and ATL, so the hoops you have to jump through just aren't there...

    g.

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • A global data storage provider whose business is booming needed a best-in-class data center to serve as the backbone of its technical operations going forward—and it needed it delivered within a year.

  • Live Event Date: September 10, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild". This loop of continuous delivery and continuous feedback is …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds