Using ODBC to Dynamically Query Your Data Stores


This article was contributed by Tom Archer.

There are times when, as a programmer, you might be faced with scenarios where you do not know the schema of a database until runtime. Examples of this are ad-hoc query and reporting tools. In both cases, the end user is allowed to build their own SQL from a list of tables. As you may already know, it is extremely easy to pass ODBC an SQL string, have it executed, and retrieve the resulting data. But, how can you do this when you don't know what the resulting data will look like when you write your application?

Luckily ODBC provides several functions that can be used for this very purpose. After connecting to the data source, the next steps needed would be the following:

  • 1. Prepare the SQL statement via the SQLPrepare function.
  • 2. Execute the SQL statement with the SQLExecute function.
  • 3. Call SQLNumResultCols to find out how many columns were returned in the result set.
  • 4. For each column, call the SQLDescribeCol function to get the column type.
  • 5. For each column, convert the SQL type returned from SQLDescribeCol to a C type.
  • 6. For each row in the result set, allocate memory for the data (depending on the C type).
  • 7. For each row, call SQLGetData to read the data into the allocated memory for that row/column.
Did I say "luckily"? Actually, I said it with tongue firmly planted in cheek. Therefore, in this article I submit to you a class (CODBCDynamic) that reduces the 400+ lines of code required to fully implement the functionality listed above to 2 lines of code! Here are some examples of how to use the CODBCDynamic class.

Examples of how to use the CODBCDynamic class

While this article also includes a full-fledged test application, it's always nice to be able to see what you're getting before you invest the time in downloading, unzipping and running someone else's code. Therefore, here are some code snippets that show how easy the CODBCDynamic class is to use.

  • Submitting an SQL statement
  • To submit an SQL statement, you simply instantiate a CODBCDynamic object (passing a valid DSN) and then call the CODBCDynamic::ExecuteSQL member function (passing the SQL string to execute). That's it!
    // simply specify the ODBC DSN in the c'tor 
    // and pass the desired SQL to the ExecuteSQL function...
    CODBCDynamic odbcDynamic(_T("YourDsn"));
    odbcDynamic.ExecuteSQL(_T("SELECT * from OrderHeader"));
    

  • Retrieving data from a result set
  • In the first example above, I showed you how the CODBCDynamic class allows you to submit an SQL statement using the ExecuteSQL member function. However, there are times, when your application will only have the HSTMT to a result set. For example, if you call the ODBC SDK function SQLGetTypeInfo, you will receive a result set with the returned data. Using the CODBCDynamic class, you can read the data into its member variables with the following two lines of code.
    // call a function that returns an hstmt to a result set (e.g., SQLGetTypeInfo)
    odbcDynamic.FetchData(hstmt);
    

  • Retrieving all rows and columns of data once ExecuteSQL or FetchData has been called
  • Once either the ExecuteSQL or FetchData member functions have been called, the resulting data can be retrieved from the CODBCDynamic object in a very generic manner. The CODBCDynamic class has a templatized array (m_ODBCRecordArray) that represents each of the records that were read. Each entry in the m_ODBCRecordArray is a templatized CMapStringToPtr map of columns and their respective values for that record. The map is keyed by the column name (retrieved automatically) and the data is in the form of a CDBVariantEx object. However, you never have to worry about such technicalities. Assuming that you've already called ExecuteSQL or FetchData, here's an example of how easy it is to iterate through the returned records of an SQL statement.
    // instantiate a CODBCDynamic object (specifying the desired DSN)
    CODBCDynamic odbcDynamic(_T("Forms Express System Database"));
    // execute the desired SQL 
    odbcDynamic.ExecuteSQL(_T("SELECT * from UserMaster"));
    
    // retrieve the record array
    CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;
    
    CString strInfo;
    
    // for every returned record...
    for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
    {
     CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];
    
     POSITION pos;
     CDBVariantEx* pvarValue;
     CString strColName;
    
     CString strValue;
    
     // for every column within the current record
     for (pos = pODBCRecord->GetStartPosition(); pos != NULL;)
     {
      pODBCRecord->GetNextAssoc(pos, strColName, pvarValue);
      pvarValue->GetStringValue(strValue);
    
      strInfo.Format(_T("Record: %ld, Column: %s, Value: '%s'"), iRecord, strColName, strValue);
      AfxMessageBox(strValue);
     }
    }
    

  • Retrieving specific columns once ExecuteSQL or FetchData has been called
  • As mentioned above, once the ExecuteSQL or FetchData function has returned, each returned record is stored in an array and each record is a basically a map of column names to CDBVariant values. Therefore, as easy as it is to iterate through all the returned the data, you can just as easily request specific columns by name. Here's an example of how you would do that.
    // instantiate a CODBCDynamic object (specifying the desired DSN)
    CODBCDynamic odbcDynamic(_T("Forms Express System Database"));
    // execute the desired SQL 
    odbcDynamic.ExecuteSQL(_T("SELECT * from UserMaster"));
    
    // retrieve the record array
    CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;
    
    // for every returned record...
    for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
    {
     CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];
     CString strValue;
    
     // retrieve the desired column (by name)
     CDBVariantEx* pvarValue = NULL;
     if (pODBCRecord->Lookup(_T("sUserId"), pvarValue))
     {
      // As shown in the example above, you can use the 
      // CDBVariantEx::GetStringValue to have the value 
      // translated into a CString and returned...
      pvarValue->GetStringValue(strValue);
      AfxMessageBox(strValue);
    
      // ... or you can now use the appropriate CDBVariant member 
      // variable to access the data. For example, if the column's 
      // data type is string, or text...
      AfxMessageBox(*pvarValue->m_pstring);
     }
    }
    
    That's it! That's how easy it is to interrogate any ODBC data source. The last thing that I will point out is that in the example above, I used my CDBVariantEx's GetStringValue member function to retrieve the data in as a CString. However, because I chose to store the data in CDBVariant objects, you can also easily query that object as to the data's exact type by inspecting the CDBVariant::m_dwType member variable. For more documentation on this small, but useful class, please refer to the Visual C++ documentation.

    Downloads

    Download demo project - 15 KB
    Download source - 15 KB Date Last Updated: October 22, 2000


    About the Author

    Tom Archer - MSFT

    I am a Program Manager and Content Strategist for the Microsoft MSDN Online team managing the Windows Vista and Visual C++ developer centers. Before being employed at Microsoft, I was awarded MVP status for the Visual C++ product. A 20+ year veteran of programming with various languages - C++, C, Assembler, RPG III/400, PL/I, etc. - I've also written many technical books (Inside C#, Extending MFC Applications with the .NET Framework, Visual C++.NET Bible, etc.) and 100+ online articles.

    Comments

    • Simple question to start on scratch

      Posted by founderideas01 on 03/22/2008 12:21pm

      Hi, 
      
      I am a real newborn in ODBC and try to learn by the example before diving in the theory.
      
      I wish to simply retreive records from a MS access 97 database called IB_Flows.mdb.
      
      In my MFC project, things are initialized like this:
      
      String CInvestFlowsSet::GetDefaultConnect()
      {
      	if (m_Path == _T(""))
              return _T("DSN=MS Access 97 Database;DBQ=H:\\IB_Flows.mdb;DefaultDir=H:\\;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;");
      
      	CString sz = _T("DSN=MS Access 97 Database;DBQ=");
      	sz = sz + m_Path;
      	sz += _T(";DefaultDir=H:\\;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;");
      	return sz;
      }
      
      
      CString CInvestFlowsSet::GetDefaultSQL()
      {
      	return _T("[Table1]");
      }
      
      There you can see that:
      
      Default database:  H:\\IB_Flows.mdb
      DSN: MS Access 97 
      UID: admin
      
      Please, how should I retreive the records in H:\\IB_Flows.mdb  ?
      
      Your code seems to take account only of DSN, but I want to connect to a .mdb file ! when should I input that?
      
      Thanks a lot .
      
      Founder

      Reply
    • still needs more work

      Posted by Legacy on 02/19/2004 12:00am

      Originally posted by: Mel

      considering how old this is, I'm a little hesitant to even comment on it. The class is a good start, but has a long way to go before it becomes very useful. For example, needs an Open() with UserName and Authentication (Password) parameters instead of just defaulting these two to empty strings. Also a Close() method so that the same object can be reused with different database connections.

      The test example program is simply awful. It won't even run without errors and I had to make a lot of changes.

      Reply
    • Getting BIT columns always FALSE

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

      Originally posted by: Vitor Sarabando

      for BIT columns you have to make this workwaround (sujested by Microsoft in Q230491)
      
      

      short CODBCDynamic::GetFieldTypeFromSQLType(short nSQLType)
      {
      short nFieldType;
      switch (nSQLType){
      case SQL_BIT:
      // nFieldType = SQL_C_BIT;
      // Microsoft Bug Q230491
      nFieldType = SQL_C_SLONG;
      break;
      case SQL_TINYINT:
      nFieldType = SQL_C_UTINYINT;
      ....

      Reply
    • select fields from different tables

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

      Originally posted by: Paula

      this code works fine if i select fields from one table. But how do i select fields from different tables in the same database. I've tried to do that but something goes wrong...
      

      Reply
    • File not found!

      Posted by Legacy on 05/27/2002 12:00am

      Originally posted by: chenjunlong

      Thanks.But when I compile the files ,there is an error:Cannot open include file:";ODBCDynamic.hpp":no such file or directory.How can I deal with it.
      

      Reply
    • More information

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

      Originally posted by: Ryan White

      this site is almost the exact same.... it has more files to help you.

      http://www.codeproject.com/database/dynamic_odbc_class.asp

      Ryan White
      www.92110.com

      Reply
    • compiler can't find "ODBCDynamic.lib".

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

      Originally posted by: Jim Sprouffske

      Thanks, after I compiled it, link error:can't find "ODBCDynamic.lib".
      Would you please send it to me?
      Thanks a lot,

      Best Wishes

      Jim

      Reply
    • Memory leak found, correction here

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

      Originally posted by: Dan Ramage

      I was having a steady memory leak while using this class, and I finally found the culprit. In the ExecuteSQL() function, the ::SQLFreeHandle(SQL_HANDLE_STMT, &hstmt)
      call to release the statement handle is incorrect. It should be ::SQLFreeHandle(SQL_HANDLE_STMT, hstmt) where the handle is passed in, not the address of the handle.
      Other than that, the class works quite well. I have made extensive modification in the error checking and handling area which I may offer up once I am finally done with this project.

      Dan

      Reply
    • ok but...

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

      Originally posted by: sheetart deshpande

      the given code is ok but what if we have to read different fields from different tables of a database?
      sheetart deshpande.

      Reply
    • unable to run demo!

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

      Originally posted by: Hari R K

      I downloaded and unzip the demo project, and src files. I got "cannot open include file 'CODBCDynamic.hpp' no such file or dir", even i tried it by inserting that class by using (Menu bar)project-add to project-files and in (workspace)Header files-add files to folder, etc. Even then it returns error. Please tell where the mistake is?
      Thanking you,
      Hari R K

      Reply
    • Loading, Please Wait ...

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

    Top White Papers and Webcasts

    • As mobile devices have pushed their way into the enterprise, they have brought cloud apps along with them. This app explosion means account passwords are multiplying, which exposes corporate data and leads to help desk calls from frustrated users. This paper will discover how IT can improve user productivity, gain visibility and control over SaaS and mobile apps, and stop password sprawl. Download this white paper to learn: How you can leverage your existing AD to manage app access. Key capabilities to …

    • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

    Most Popular Programming Stories

    More for Developers

    Latest Developer Headlines

    RSS Feeds