Connection Pooling Using CDatabase

Posted on 2004-04-13
Last Modified: 2013-11-20
I was trying to setup Connection Pooling on an existing MFC based application.
The backend is Oracle and I used CRecordset to get access to the data.

I followed an article on the Codeguru website..

I followed the steps written in it ( listed below), but, I have questions about how to accomplish 3 of them....
done. 1. Create (or download) the CDatabase-derived class, and add it to your Project
done. 2. Create a member of the CDatabase-derived class in your CWinApp-derived (Application) class
done. 3. Create a SQLHANDLE data member in your Application class for the Enviroment Handle
??..    4. Add an accessor function that returns the address of the CDatabase-derived member you added in the previous step
??..    5. Make the necessary SQLSetEnvAttr(...) and SQLAllocHandle(...) calls as specified above
done. 6. Initially open your CDatabase-derived object (if you are using SQL Authentication)
??      7. Whenever you instantiate a CRecordset-derived object, pass the address of the CDatabase-derived class into its constructor

First of all,
1) If this is one of the better ways to accomplish Connection pooling, please advise on how to complete this based on code snippets below.
2)If there's another way of doing this (I have 20 tables), please step through. I cant say I'm good at this and any help is greatly appreciated.

Here's my code in snippets from different files.

----------- CCPDatabase class.. as is from article.Wrapper class across CDatabase

class      CCPDatabase : public CDatabase
      /**/                  CCPDatabase()
      //      Code Is Copied From CDatabase Source Code...
      virtual      BOOL      OpenEx( LPCTSTR lpszConnectString, DWORD dwOptions = 0 )
            dwOptions |= noOdbcDialog;                                          // Force NoDialog


-----------------MyWinApp Class

      // Setup the Connection Pool
      //   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
      // End setup of Connection Pool

      // ****************************************************************
      // Calling the CDatabase Open function for the first and ONLY time
      // ****************************************************************
   if( !m_shPDB.IsOpen( ) && 
       !m_shPDB.OpenEx( NULL ) )
      return FALSE;
      // **********return false on fail***********************************

---------------------------- One of my CRecordsets.CPP file
// DetailSet.cpp : implementation file

#include "stdafx.h"
#include "MyWinApp.h"
#include "DetailSet.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;

// CDetailSet


CDetailSet::CDetailSet(CCPDatabase* pDatabase)
      : CRecordset(pDatabase)
      m_Item = _T("");
      m_Seq = 0;
      m_Description = _T("");
      m_FeatureCode = _T("");
      m_RecordType = _T("");
      m_ORDERNO = _T("");
      m_nFields = 6;
      m_nDefaultType = dynaset;


CString CDetailSet::GetDefaultConnect()
      return _T("ODBC;DSN=OraTest;UID=user10;PWD=password1");

CString CDetailSet::GetDefaultSQL()
      return _T("[SC_FRAMES].[SC_FRMSCH_DETAIL]");

void CDetailSet::DoFieldExchange(CFieldExchange* pFX)

// CDetailSet diagnostics

#ifdef _DEBUG
void CDetailSet::AssertValid() const

void CDetailSet::Dump(CDumpContext& dc) const
#endif //_DEBUG
#if !defined(AFX_DETAILSET_H__AD149183_516C_11D2_8176_0060979C789B__INCLUDED_)
#define AFX_DETAILSET_H__AD149183_516C_11D2_8176_0060979C789B__INCLUDED_

#if _MSC_VER >= 1000
#pragma once
#endif // _MSC_VER >= 1000
// DetailSet.h : header file

// CDetailSet recordset

class CDetailSet : public CRecordset
      CDetailSet(CCPDatabase* pDatabase = &((CFrameSchedApp *)AfxGetApp())->m_shPDB);

// Field/Param Data
      //{{AFX_FIELD(CDetailSet, CRecordset)

// Overrides
      // ClassWizard generated virtual function overrides
      virtual CString GetDefaultConnect();    // Default connection string
      virtual CString GetDefaultSQL();    // Default SQL for Recordset
      virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support

// Implementation
#ifdef _DEBUG
      virtual void AssertValid() const;
      virtual void Dump(CDumpContext& dc) const;

// Microsoft Developer Studio will insert additional declarations immediately before the previous line.

#endif // !defined(AFX_DETAILSET_H__AD149183_516C_11D2_8176_0060979C789B__INCLUDED_)
Question by:Kasinadh
  • 3
  • 3

Expert Comment

ID: 10816805
1. Depends .... if you want to use MFC only, you should go ahead with what you are doing .... looks like you have already done all the steps. just create a CDetailSet object and run your queries.
2. you can also do connection pooling with just ODBC
look here for an example on how to establish conn .....
look here for example to query data ....


Author Comment

ID: 10817993
Going with step 1, I am facing a problem.
I'm unable to finish the code to perform connection pooling.
I'm unable to send the handle to my CRecordset.
Its still going through CDatabase for the OpenEx function. My overriding class-function is not being called.

1) Does my Code still look complete? Is it right according to the steps defined.

Expert Comment

ID: 10818506
>>I'm unable to send the handle to my CRecordset.
you are doing that here .... CDetailSet(CCPDatabase* pDatabase = &((CFrameSchedApp *)AfxGetApp())->m_shPDB);

looks good ...
create a instance of CDetailSet, by default it will take the handle of the m_shPDB object
then start using this CDetailSet object to do queries
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 10825814
I see that the application is up and running with the changes.
Thank You for all the help here.
Just how would I check the functioning of this program(Connection pooling aspect).( developer privileges only)

Accepted Solution

bkfirebird earned 500 total points
ID: 10825944
check out the part on how to monitor connection pooling

if your database has a monitoring utility or analyzer, you could use that to see how many connections are being made

Author Comment

ID: 10847215
Thank You for all the help. I could get to pool the connections. This task is complete.

The reason to accomplish this was to improve performance.
But, I have not noticed any performance improvement. My inserts/updates didnt get any better.
My last question on this would be what factors could I play to take advantage of pooling.

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Expand macro to include alternate header placement and naming 5 71
no14 challenge 14 66
post4 challenge 28 100
Updating statistics with error notification email in SQL server 4 95
Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question