Click here to Skip to main content
15,860,972 members
Articles / Desktop Programming / MFC
Article

The CODBCDynamic class

Rate me:
Please Sign up or sign in to vote.
5.00/5 (12 votes)
14 Mar 2000 187.9K   3.1K   40   51
A class to dynamically read data from any ODBC data source
  • Download demo project - 14 Kb
  • Download source files - 14 Kb
  • Introduction

    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.

    License

    This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

    A list of licenses authors might use can be found here


    Written By
    Software Developer (Senior) Microsoft
    United States United States
    I manage the strategy for the Azure Management Experience documentation at Microsoft. Those technologies include Open-Source DevOps (Terraform, Ansible, Jenkins), Azure PowerShell, and Azure CLI.

    Comments and Discussions

     
    GeneralRe: Lost memory free Pin
    Alfonso Bastias29-Aug-01 9:04
    Alfonso Bastias29-Aug-01 9:04 
    GeneralRe: Lost memory free Pin
    14-May-02 15:54
    suss14-May-02 15:54 
    GeneralRe: Lost memory free Pin
    Tom Archer15-May-02 0:54
    Tom Archer15-May-02 0:54 
    GeneralMissing include file... Pin
    SinclairE2-Aug-01 5:21
    SinclairE2-Aug-01 5:21 
    GeneralRe: Missing include file... Pin
    Tom Archer2-Aug-01 5:28
    Tom Archer2-Aug-01 5:28 
    GeneralRe: Missing include file... Pin
    SinclairE2-Aug-01 5:42
    SinclairE2-Aug-01 5:42 
    GeneralRe: Missing include file... Pin
    Tom Archer2-Aug-01 5:45
    Tom Archer2-Aug-01 5:45 
    GeneralRe: Missing include file... Pin
    SinclairE2-Aug-01 6:32
    SinclairE2-Aug-01 6:32 
    Once compiled, it's a great app.
    Cheers.
    GeneralRe: Missing include file... Pin
    Tom Archer2-Aug-01 6:45
    Tom Archer2-Aug-01 6:45 
    GeneralRe: Missing include file... Pin
    webwesen14-Nov-02 12:57
    webwesen14-Nov-02 12:57 
    GeneralBug Fix - CODBCDynamic::GetFieldTypeFromSQLType asserts. Pin
    26-Jul-01 7:59
    suss26-Jul-01 7:59 
    QuestionHow can i send mail programatically like Outlook express Pin
    Vikash Dubey29-Jun-01 8:09
    Vikash Dubey29-Jun-01 8:09 
    GeneralData truncated for fields of type 'SQL_LONGVARCHAR' Pin
    Norby Mahoney28-Jun-01 11:24
    Norby Mahoney28-Jun-01 11:24 
    QuestionHave you updated this class to also Write to any ODBC source? Pin
    Norby Mahoney27-Jun-01 5:38
    Norby Mahoney27-Jun-01 5:38 
    AnswerRe: Have you updated this class to also Write to any ODBC source? Pin
    Tom Archer27-Jun-01 6:39
    Tom Archer27-Jun-01 6:39 
    GeneralCan't get it to compile Pin
    5-Jun-01 5:07
    suss5-Jun-01 5:07 
    GeneralRe: Can't get it to compile Pin
    Tom Archer27-Jun-01 6:40
    Tom Archer27-Jun-01 6:40 
    Generalgood example Pin
    3-Jun-01 19:06
    suss3-Jun-01 19:06 
    GeneralRe: good example Pin
    Tom Archer27-Jun-01 6:41
    Tom Archer27-Jun-01 6:41 
    QuestionIs it possible to access the tablename or alias name of each column? Pin
    Steve Gorman20-Aug-00 7:49
    sussSteve Gorman20-Aug-00 7:49 
    AnswerRe: Is it possible to access the tablename or alias name of each column? Pin
    Tom Archer20-Aug-00 7:52
    Tom Archer20-Aug-00 7:52 
    Answerquick SQL workaround Pin
    Steve Gorman21-Aug-00 8:56
    sussSteve Gorman21-Aug-00 8:56 
    AnswerRe: Is it possible to access the tablename or alias name of each column? Pin
    Lars26-Sep-00 4:18
    Lars26-Sep-00 4:18 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.