ODBC Dynamic Connection

Many times it is neccessary to access databases without knowing the scheme of the database at design time.
Think that you are developing an application that supports ISE (Incremental Search and Edit) for a certain purpose.
The application reads a column of a database table to determine if there is any match to the letters that are allready typed in
a combo box. If it finds any then it displays the nearest one in the edit part of the combo.

When you do not know from where to get the information that should be displayed,or when the the source column
is being changed from time to time, the only solution is to bound your data columns at run time.
Of course you can also hard code the names of the columns, but you do not come far that way.

To solve a similar problem I developed a database connection class that can access any ODBC-database by using its DSN.
The CDBConnection is a class that takes care of all access to a database, regardless of the purpose. When it needs to fetch data
it uses Microsofts CDynamicBulkSet, and when it needs to edit data ( New, Update or Delete ) it uses CDynamicRow.
I used the CDynamicBulkSet here just to show several ways of accessing data dynamiclly and not at least to show also how to use multiple row fetch.

Where to use these classes?

  • When you do not want to create a CRecordSet derived class for each table you access.
  • When you read tables setup from a file. eg. Ini file.
  • When you want just to be able to reuse database access code in different projects.

    What do you need to use these classes?
    To use MFC database and collections support add the following two lines to your stdafx.h

  • afxdb.h MFC database classes
  • afxtempl.h To be able to use MFC collection templates

      Then add the follwing files to your project :
    • DBField.h
    • BulkSet.h and BulkSet.cpp
    • DynamicRow.h and DynamicRow.cpp
    • DBConnection.h and DBConnection.cpp
      And if you feel for it use the following class to keep connections while application is running.
    • ConnectionPool.h and ConnectionPool.cpp

    Before I begin explaining the usage of these classes, I would like you to notice that only strings are supported in this version.
    I Usually start by initializing all database connections and keeping them into the connectionpool.

    
    	// Add a connection to the connectionpool if it does not exist allready
    	if(!m_pConnectionPool->GetConnection("TestDSN2"))
    	{
    		CDBConnection *pCon2 = new CDBConnection();
    		// If you want to force logon dialog use this
    		// pCon2->ConnectToDataBase(TestDSN2,"","",TRUE);
    
    		// Otherwise use this
    		pCon2->ConnectToDataBase("TestDSN2","","",FALSE);
    		m_pConnectionPool->AddConnection(pCon2);		
    	}//if(!m_pConPool->GetConnection("TestDSN2"))
    
    In your GUI (if you want to show some data) you have to connect your components to columns in the databases!
    Here is a way of doing it.
    
    	m_Combo2.m_sDSN = _T("TestDSN2"); // We are getting values by using TestDSN2
    	m_Combo2.m_sTableName = _T("T_DemoTable"); // Connectiong to table T_DemoTable
    	m_Combo2.m_sColumnName = _T("C_Second"); // And column C_Second
    	m_Combo2.SetConnectionPool(m_pConnectionPool); // We past a pointer to the connectionpool, so we can access memebermethods
    
    In its turn this GUI-component (CISECombobox in this case) takes advantage of methods in the CDBConnection to retrive string values and add them into its stringlist.
    In the next few lines I show how easy it is to ask for values from the database. This code is from "GetConcatinationString".
    
    
    	// sCurValue is the currentvalue entered in the combobox
    	// We test for <= 1 just for performance reasons
    	if( sCurValue.GetLength() <= 1)
    	{
    		// delete list of strings
    		while (!m_StringList.IsEmpty())
    			m_StringList.RemoveHead();
    
    		// Construct the SQL-Query
    		sSql.Format("SELECT DISTINCT %s FROM %s WHERE %s LIKE '%s*' ORDER BY %s",m_sColumnName,
    			m_sTableName,m_sColumnName,sCurValue,m_sColumnName);
    				
    		CDBConnection * pCon = m_pConPool->GetConnection(m_sDSN);
    		
    		// Exceute the Sql
    		if(pCon->ExecuteStatement(sSql))
    		{
    			// Fetch datarows ( here it is preferaed to call GetFieldValue())
    			// I wanted just to show this function 
    			sRet =  pCon->GetNextRow();		
    			while(sRet.GetLength())
    			{
    				m_StringList.AddTail(sRet);
    				sRet =  pCon->GetNextRow();		
    			}//while(sRet.GetLength())
    		}		
    	}//if( sCurValue.GetLength() <= 1)
    
    Let us now take a look at how to add a new row ( that contains only one field ) to a database table.
    
    	try
    	{
    		// Look for a connection called TestDSN2.
    		// If it is not in the connectionpool make it!
    		CDBConnection * pCon = m_pConnectionPool->GetConnection("TestDSN2");
    		
    		// Prepare this connection
    		pCon->PrepareFieldExchange();
    		
    		// Construct a special Databasefield object and initialize it
    		CDBField *pF1  = new CDBField();
    		pF1->m_sColumn = "C_Second"; // Column name where this field fits
    		pF1->m_sValue  = m_sSecondString; // Cell value
    		pCon->m_pFields.Add(pF1);
    
    		// Add more fields as needed
    
    		// Ask the connection to add this field to the T_DemoTable.
    		if(!pCon->AddNewRow("T_DemoTable"))
    		{			
    			return;
    		}		
    	}//try
    	catch(CDBException e)
    	{
    		AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
    		e.Delete();
    		return;
    	}//catch(CDBException e)
    
    
    
    
    That was actually everything you need to know before beginning using the class-set enclosed here.

    What do you need to run the test application?

    • Add two DSNs to your system
      1-TestDSN1 points to .\Databases\FirstDB
      2-TestDSN2 points to .\Databases\SecondDB
    • Compile the program and run it
    • NOTE : The database enclosed in the demo project is in MSAccess 2000 format, you may have to create a one that matches your own access driver.

    Downloads

    Download demo project - 186 Kb
    Download source - 12 Kb


  • Comments

    • vb6.0 with MS Access

      Posted by Legacy on 10/14/2003 12:00am

      Originally posted by: Aijaz

      I using MS Access as backend but I don't know how to make connection. plz send me connection code and dicription.
      

      • ODBC can't run in XP sp2.0

        Posted by 1qaz1q on 06/19/2006 07:44am

        My app uses ODBC API in win2000 whit VC++6.0.it can't run in XP sp2.0,The SQLfetch() can't out! yhj199@tom.com thanks a lot!

        Reply
      Reply
    • Add New Fields in Database Table

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

      Originally posted by: rasho

      What is simpliest method to add new field to database table
      using ODBC, and also using ADO

      Reply
    • Visual C++ and AS/400

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

      Originally posted by: Nenad Mustafic

      How can I set pointer in DB2/400 file with some key, and than read records from that point?
      
      In rpg/400 this is something like this:
      KEY KLIST
      KFLD NAME
      KFLD COLOR

      KEY SETLLR001
      READ R001 99

      Reply
    • how i can view ODBC Report from my aplication

      Posted by Legacy on 07/15/2002 12:00am

      Originally posted by: Ramy Aiob

      i find some classes that support with Dao not Odbc
      can you send me pleas a class that support ODBC
      to view my Access Repot in my MFC aplication;
      thank you;

      Reply
    • How to add a full row into the database,not only one field?

      Posted by Legacy on 06/12/2002 12:00am

      Originally posted by: kingkong

      How to add a full row into the database,not only one field?

      Reply
    • How to add a full row into the database,not only one field?

      Posted by Legacy on 06/12/2002 12:00am

      Originally posted by: kingkong

      How to add a full row into the database,not only one field?

      Reply
    • How to retrive more than 20 row of records

      Posted by Legacy on 02/04/2002 12:00am

      Originally posted by: Deluxe

      It seen that I can only retrive 20 row of records...? for instance , i have more than 1000 of records... how could I retrive that by using GetFieldValue()...?

      Thanks

      ////////////////////////////////////////////////////

      I could not send a reply therefore I try here to modify :)
      Hi,

      If you look at the documentation of CRecordSet at MSDN, you will find out that you can call the virtual function SetRowSetSize(DWORD dwNewRosSetSize) to set a new number of fetched rows. The default size is 25 rows.

      Hussam

      Reply
    • How to insert a datetime column

      Posted by Legacy on 02/01/2002 12:00am

      Originally posted by: Caleb

      How could we do datetime column with MSSQL ...? My code fragment is here...
      
      

      CDBField *pF1 = new CDBField();
      pF1->m_sColumn = "time";
      pF1->m_sValue = "1-1-2002 1:1:1";
      pCon->m_pFields.Add(pF1);

      if(!pCon->AddNewRow("test"))
      {
      return;
      }


      I alwas get the error message "Invalid Characters for cast specification".....

      Reply
    • way to run macros or print reports

      Posted by Legacy on 01/13/2002 12:00am

      Originally posted by: Cyndi L. Chatman

      Hi -

      Is there a way, using this connection, to print reports
      or run macros within the Access database?

      Thanks,
      Cyndi

      Reply
    • How do I get Data from AS -400

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

      Originally posted by: Amin Khan

      How Do I make Connection With As 400 To use the data in my VB Project
      

      Reply
    • Loading, Please Wait ...

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

    Top White Papers and Webcasts

    • Learn How A Global Entertainment Company Saw a 448% ROI Every business today uses software to manage systems, deliver products, and empower employees to do their jobs. But software inevitably breaks, and when it does, businesses lose money -- in the form of dissatisfied customers, missed SLAs or lost productivity. PagerDuty, an operations performance platform, solves this problem by helping operations engineers and developers more effectively manage and resolve incidents across a company's global operations. …

    • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

    Most Popular Programming Stories

    More for Developers

    RSS Feeds