SAMPLE: Dynamic Column Binding with MFC ODBC Database Classes

• The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1, 4.2, 5.0

This is a 32-bit version of the DYNCOL sample.

SUMMARY

The DYNC32 sample demonstrates how to dynamically determine the number, types, and names of each column in a given table and then bind these columns to dynamically allocated objects in your CRecordset derived class.

This sample has been updated to work with the new implementation of CRecordset in Visual C++ 4.2 while remaining compatible with 4.0 and 4.1.

The following file is available for download from the Microsoft Software Library:

 ~ Dync32.exe (size: 50364 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

   DYNC32.EXE -d

MORE INFORMATION

DYNC32 follows the basic procedures outlined in the documentation titled, "Dynamically Binding Data Columns," found in Database Encyclopedia of the Books OnLine. However, DYNC32 has no permanent members, therefore only one list is generated that contains information for all the columns in the table. If you have a subset of columns that are always present and are therefore member variables of the CRecordset derived class, you will need to follow the instructions in the article on generating only a list of the columns not already bound. DYNC32 only constructs a single list and implements methods for performing DoFieldExchange() and DoDataExchange() for this list of dynamically allocated objects.

Generating the List

The first thing DYNC32 must do is determine how many columns are in the database, and of what type. To do this, the CRecordset-derived object, CDynCol32Set, contains a CColumns object to query this information. In CDynCol32Set, the Open member function has been overridden so that the column information can be retrieved before the base-class CRecordset::Open function is called to open the database. After this information has been accessed, a list of CColumnData objects is allocated to hold a description of each column in the table, plus a pointer to a storage object allocated for that column (to be used in the record field exchange routines).

   BOOL CDyncol32Set::Open(UINT nOpenType, LPCSTR lpszSql, DWORD

   {
     // use one CDatabase Object so we only prompt once for database Open
     // ***** allocated in constructor...

     if (! m_pDatabase->Open(NULL, FALSE, FALSE, "ODBC;"))
       return  FALSE;

     CTables tables(m_pDatabase);

     // prompt for a table to open
     CTablesDialog tableName(NULL,m_pDatabase);
     if (IDCANCEL == tableName.DoModal())
       return FALSE;

     // Specify the table to look at
     m_pColumns = new CColumns(m_pDatabase);
     m_pColumns->m_strTableNameParam = tableName.m_strTableNameSelected;

     // Set the database to be the CDynaSet's database if one hasn't
     // already been set (this should be the case)
     if (m_pColumns->m_pDatabase == NULL)
       m_pColumns->m_pDatabase = m_pDatabase;

     // Open the recordset to get the column info
     if (!m_pColumns->Open(CRecordset::forwardOnly, NULL,
   CRecordset::readOnly))

       return FALSE;

     // Initialize the number of fields dynamically allocated to CDynaSet
     m_nFields = 0;

     // Loop until we've seen all the columns
     while (!m_pColumns->IsEOF())
     {
       // Allocate a new CColumnData object for the current column
       CColumnData *pData = new CColumnData;

       // Store the colmun information
       pData->m_nDataType = m_pColumns->m_nDataType;
       pData->m_strColumnName = "[";
       pData->m_strColumnName += m_pColumns->m_strColumnName;
       pData->m_strColumnName +="]";

       // Allocate an object of the appropriate type to store
       // the column data
       switch(pData->m_nDataType)
       {
         case SQL_BIT:
           pData->m_pData = (void *)new BOOL;
           break;

         case SQL_TINYINT:
           pData->m_pData = (void *)new BYTE;
           break;

         case SQL_SMALLINT:
           pData->m_pData = (void *)new int;
           break;

          ...

         default:
           ASSERT(FALSE);
       }

       // Add the column descriptor to the list and
       // increment the number of columns in the
    CDynaSet

       m_pList.AddTail(pData);
       m_nFields++;

       // Get the next column's information
       m_pColumns->MoveNext();
     }

     // Free the HSTMT used to get the table info
     RETCODE nRetCode;
     AFX_SQL_SYNC(::SQLFreeStmt(m_pColumns->m_hstmt, SQL_CLOSE));
     // Return the base class if we got this far
     lpszSql = m_pColumns->m_strTableNameParam;

     // we don't need the columns recordset any more
     delete m_pColumns;
     return CRecordset::Open(nOpenType, lpszSql, dwOptions);
   }

Getting the Column Data into the Recordset

Once the CColumnData list has been generated, DoFieldExchange() must be overridden to traverse this list and call the appropriate RFX routine for each CColumnData's storage object.

   void CDyncol32Set::DoFieldExchange(CFieldExchange* pFX)
   {
     // Set the type of exhange; same as AppWizard generated
     pFX->SetFieldType(CFieldExchange::outputColumn);

     // Get a pointer to the first CColumnData object in the list
     POSITION rPos = m_pList.GetHeadPosition();
     CColumnData *pData = (CColumnData *)m_pList.GetNext(rPos);

     // Loop until we've traversed all the columns
     while (pData)
     {
       // Call the appropriate RFX routine for the column's type
       switch(pData->m_nDataType)
       {
         case SQL_BIT:
           RFX_Bool(pFX, pData->m_strColumnName, *((BOOL *)(pData-
                                                            >m_pData)));
           break;

         case SQL_TINYINT:
           RFX_Byte(pFX, pData->m_strColumnName, *((BYTE *)(pData-
                                                            >m_pData)));
           break;

         case SQL_SMALLINT:
           RFX_Int(pFX, pData->m_strColumnName, *((int *)(pData-
                                                          >m_pData)));
           break;

         ...

         default:
            ASSERT(FALSE);
       }

       // Set pData to NULL if that was the last column
       if (rPos)
         pData = (CColumnData *)m_pList.GetNext(rPos);
       else
         pData = NULL;
     }
   }

Getting the Recordset Data into the View

After getting the data into the recordset, the last thing to do is to get this information into the CRecordView-derived class to be displayed. In the case of DYNC32, it simply uses a GRID OCX control to do a straight dump of the data onto the view. In order to do this, the CColumnData list must again be traversed. Normally an appropriate DDX routine would be called for each element in the list, but DYNC32 only puts the information into the cells of a GRID OCX control to be displayed.

   void CDyncol32View::DoDataExchange(CDataExchange* pDX)
   {
      CRecordView::DoDataExchange(pDX);
      //{{AFX_DATA_MAP(CDyncol32View)
      DDX_Control(pDX, IDC_GRID1, m_grid);
      //}}AFX_DATA_MAP
      RefreshData();
   }

   void CDyncol32View::RefreshData()
   {
     if (!m_pSet->IsOpen())
     return;
     // Get pointer to CDynaSet's CColumnData list
     CPtrList *pList = &m_pSet->m_pList;

     // Get the first element
     POSITION rPos = pList->GetHeadPosition();
     CColumnData *pData = (CColumnData *)pList->GetNext(rPos);

     // Allocate a string buffer to be used in conversions
     CString strBuffer;

     // Set the initial row to fill in
     int row = 1;

     // Set the GRID to have the correct number of rows and columns
     m_grid.SetRows(m_pSet->m_nFields + 1);
     m_grid.SetCols(2);
     m_grid.SetColWidth(0, 2500);
     m_grid.SetColWidth(1, 2500);

     // Loop until we've traversed all the CColumnData objects
     while (pData)
     {
       // Insert the column name in the first column and move
       // to the second column to insert the data
       m_grid.SetRow(row);
       m_grid.SetCol(0);
       m_grid.SetText(pData->m_strColumnName);
       m_grid.SetCol(1);

       // Call the appropriate DDX
       switch(pData->m_nDataType)
       {
         case SQL_BIT:
           strBuffer = *((BOOL *) (pData->m_pData)) ? "TRUE" : "FALSE";
           m_grid.SetText(strBuffer);
           break;

        case SQL_TINYINT:
        {
          char *pBuffer = strBuffer.GetBuffer(32);
          _itoa(*((BYTE *)(pData->m_pData)), pBuffer, 10);
        }
        strBuffer.ReleaseBuffer();
        m_grid.SetText(strBuffer);
        break;

        case SQL_SMALLINT:
        case SQL_INTEGER:
        {
           char *pBuffer = strBuffer.GetBuffer(32);
           _itoa(*((int *)(pData->m_pData)), pBuffer, 10);
        }
        strBuffer.ReleaseBuffer();
        m_grid.SetText(strBuffer);
        break;

        ...

        default:
          ASSERT(FALSE);
       }
       // Increment the row and check to see if that was our
       // last CColumnData in the list
       row++;
       if (rPos)
         pData = (CColumnData *)pList->GetNext(rPos);
       else
         pData = NULL;
     }
   }

Note that the implementation of CRecordset has changed from between version 4.1 and 4.2. As a result, you will notice that CColumns::Open() and CTables::Open(), the two overrides of CRecordset::Open() that are used to directly call ODBC API functions, now include conditional code. This code represents the minimum that must be done to update the recordsets to 4.2.

Keywords          : MfcDatabase kbprg kbsample kbusage kbfile
Technology        : kbMfc
Version           : 4.0 4.1 4.2 5.0
Platform          : NT WINDOWS
Solution Type     : kbcode