Recordset: Dynamically Binding Data Columns (ODBC)

  • Article

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

Recordsets manage binding table columns that you specify at design time, but there are cases when you may want to bind columns that were unknown to you at design time. This article explains:

  • When you might want to bind columns dynamically to a recordset.

  • How to bind columns dynamically at run time.

****Note   ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. The techniques described generally are not recommended if you are using bulk row fetching. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

When You Might Bind Columns Dynamically

At design time, ClassWizard creates recordset classes based on the known tables and columns on your data source. Databases can change between when you design them and later when your application uses those tables and columns at run time. You or another user might add or drop a table or add or drop columns from a table that your application’s recordset relies upon. This probably isn’t a concern for all data-access applications, but if it is for yours, how can you cope with changes in the database schema, other than by redesigning and recompiling? The purpose of this article is to answer that question.

This article describes the most common case in which you might bind columns dynamically — having begun with a recordset based on a known database schema, you want to handle additional columns at run time. The article further assumes that the additional columns map to CString field data members, the most common case, although suggestions are supplied to help you manage other data types.

With a small amount of extra code, you can:

  • Determine what columns are available at run time.

  • Bind additional columns to your recordset dynamically, at run time.

Your recordset still contains data members for the columns you knew about at design time. It also contains a small amount of extra code that dynamically determines whether any new columns have been added to your target table and, if so, binds these new columns to dynamically allocated storage (rather than to recordset data members).

This article doesn’t cover other dynamic binding cases, such as dropped tables or columns. For those, you’ll need to use ODBC API calls more directly. See the ODBC SDK Programmer’s Reference on the MSDN Library CD.

Example code for this article comes from the MFC Database samples and .

How to Bind Columns Dynamically

To bind columns dynamically in a case like that of the DYNABIND example, you must know (or be able to determine) the names of the additional columns. You must also allocate storage for the additional field data members, specify their names and their types, and specify the number of columns you’re adding.

The following discussion mentions two different recordsets. The first is the main recordset that selects records from the target table. The second is a special column recordset used to get information about the columns in your target table.

The General Process

At the most general level, you follow these steps:

  1. Construct your main recordset object.

    Optionally, pass a pointer to an open CDatabase object, or be able to supply connection information to the column recordset in some other way.

  2. Take steps to add columns dynamically.

    See the process described in Adding the Columns below.

  3. Open your main recordset.

    The recordset selects records and uses record field exchange (RFX) to bind both the static columns (those mapped to recordset field data members) and the dynamic columns (mapped to extra storage that you allocate).

Adding the Columns

Dynamically binding added columns at run time requires the following steps:

  1. Determine at run time what columns are in the target table. Extract from that information a list of the columns that have been added to the table since your recordset class was designed.

    A good approach is to use a column recordset class designed to query the data source for column information for the target table — such as column name, data type, and so on. The MFC Database sample provides a recordset class called CColumns that you can use to build a list of the new columns’ names.

  2. Provide storage for the new field data members. Your main recordset class doesn’t have field data members for unknown columns, so you must provide a place to store the names, result values, and possibly data type information (if the columns are of different data types).

    One approach is to build one or more dynamic lists, one for the new columns’ names, another for their result values, and a third for their data types (if necessary). These lists, particularly the value list, provide the information and the necessary storage for binding. The following figure illustrates building the lists.

    Building Lists of Columns to Bind Dynamically

  3. Add an RFX function call in your main recordset’s DoFieldExchange function for each added column. These RFX calls do the work of fetching a record, including the additional columns, and binding the columns to recordset data members or to your dynamically supplied storage for them.

    One approach is to add a loop to your main recordset’s DoFieldExchange function that loops through your list of new columns, calling the appropriate RFX function for each column in the list. On each RFX call, pass a column name from the column name list and a storage location in the corresponding member of the result value list.

Lists of Columns

The four lists you need to work with are:

Current-Table-Columns (List 1 in the illustration)

A list of the columns currently in the table on the data source. This list may or may not match the list of columns currently bound in your recordset.

Bound-Recordset-Columns (List 2 in the illustration)

A list of the columns bound in your recordset (usually set up with ClassWizard). These columns already have RFX statements in your DoFieldExchange function.

Columns-To-Bind-Dynamically (List 3 in the illustration)

A list of columns in the table but not in your recordset. These are the columns you want to bind dynamically.

Dynamic-Column-Values (List 4 in the illustration) A list containing storage for the values retrieved from the columns you bind dynamically. Elements of this list correspond to those in Columns-to-Bind-Dynamically, one to one.

Building Your Lists

With a general strategy in mind, you can turn to the details. The procedures in the rest of this article show you how to build the lists shown in Lists of Columns. The procedures guide you through:

  • Determining the columns in your table at run time

  • Determining the names of columns not in your recordset

  • Providing dynamic storage for columns newly added to the table

  • Dynamically adding RFX calls for new columns

Determining the Columns in Your Table at Run Time

First, build Current-Table-Columns (as in the illustration): a list of the columns in the table on the data source.

To determine the columns in a table at run time (Current-Table-Columns)

  1. Borrow the files COLUMNST.H/.CPP from the MFC Database sample . Add the .CPP file to your project and include the .H file as needed.

  2. At run time, construct a “column recordset” object of class CColumn, passing a pointer to an open CDatabase object.

  3. Before you call Open, set one or more of the column recordset’s parameters. The following table describes what these parameters specify.

    Parameter Description 
    m_strQualifierParam
    		 Identifies the database containing the table for ODBC. You usually don’t need to specify this value. 
    m_strOwnerParam
    		 Identifies the person who created the target table. 
    m_strTableNameParam
    		 Identifies the target table by name. 
    m_strColumnNameParam
    		 Identifies a specific column by name.

    In most cases, you need only the table name, although some data sources might require the owner name as well, and others might require even more information. In addition to table name, use the column name parameter if you need information for only a single column in the table. For information about these parameters, see ::SQLColumns in the ODBC SDK Programmer’s Reference on the MSDN Library CD.

  4. Call Open for the column recordset.

    The recordset returns a record for each column in the specified table (unless you specify m_strColumnNameParam).

  5. Construct Current-Table-Columns, a collection object that can hold CString objects.

    For example, you might use a CStringList.

  6. Scroll through the object’s records, loading column names into Current-Table-Columns as you go.

This procedure results in a collection object that contains the names of all columns in a specified table. For example, the illustration shows Current-Table-Columns (List 1) with four elements. The last element is “Phone.” For descriptions of the lists, see Lists of Columns.

Determining Which Table Columns Are Not in Your Recordset

Next, build a list (Bound-Recordset-Columns, as in List 2 in the illustration) that contains a list of the columns already bound in your main recordset. Then build a list (Columns-to-Bind-Dynamically, derived from Current-Table-Columns and Bound-Recordset-Columns) that contains column names that are in the table on the data source but not in your main recordset.

To determine the names of columns not in the recordset (Columns-to-Bind-Dynamically)

  1. Build a list (Bound-Recordset-Columns) of the columns already bound in your main recordset.

    One approach is to create Bound-Recordset-Columns at design time. You can visually examine the RFX function calls in the recordset’s DoFieldExchange function to get these names. Then set up your list as an array initialized with the names.

    For example, the illustration shows Bound-Recordset-Columns (List 2) with three elements. Bound-Recordset-Columns is missing the Phone column shown in Current-Table-Columns (List 1).

  2. Compare Current-Table-Columns and Bound-Recordset-Columns to build a list (Columns-to-Bind-Dynamically) of the columns not already bound in your main recordset.

    One approach is to loop through your list of columns in the table at run time (Current-Table-Columns) and your list of columns already bound in your recordset (Bound-Recordset-Columns) in parallel. Into Columns-to-Bind-Dynamically put any names in Current-Table-Columns that don’t appear in Bound-Recordset-Columns.

    For example, the illustration shows Columns-to-Bind-Dynamically (List 3) with one element: the Phone column found in Current-Table-Columns (List 1) but not in Bound-Recordset-Columns (List 2).

  3. Build a list of Dynamic-Column-Values (as in List 4 in the illustration) in which to store the data values corresponding to each column name stored in your list of columns to bind dynamically (Columns-to-Bind-Dynamically).

    The elements of this list play the role of new recordset field data members. They are the storage locations to which the dynamic columns are bound. For descriptions of the lists, see Lists of Columns.

Providing Storage for the New Columns

Next, set up storage locations for the columns to be bound dynamically. The idea is to provide a list element in which to store each column’s value. These storage locations parallel the recordset member variables, which store the normally bound columns.

To provide dynamic storage for new columns (Dynamic-Column-Values)

  • Build Dynamic-Column-Values, parallel to Columns-to-Bind-Dynamically, to contain the value of the data in each column.

    For example, the illustration shows Dynamic-Column-Values (List 4) with one element: a CString object containing the actual phone number for the current record: “555-1212”.

    In the most common case, Dynamic-Column-Values has elements of type CString. If you’re dealing with columns of varying data types, you’ll need a list that can contain elements of a variety of types.

The result of the preceding procedures is two main lists: Columns-to-Bind-Dynamically containing the names of columns and Dynamic-Column-Values containing the values in the columns for the current record.

****Tip   ****If the new columns aren’t all of the same data type, you might want an extra parallel list containing items that somehow define the type of each corresponding element in the column list. (You can use the values AFX_RFX_BOOL, AFX_RFX_BYTE, and so on, for this if you wish. These constants are defined in AFXDB.H.) Choose a list type based on how you represent the column data types.

Adding RFX Calls to Bind the Columns

Finally, arrange for the dynamic binding to occur by placing RFX calls for the new columns in your DoFieldExchange function.

To dynamically add RFX calls for new columns

  • In your main recordset’s DoFieldExchange member function, add code that loops through your list of new columns (Columns-to-Bind-Dynamically). In each loop, extract a column name from Columns-to-Bind-Dynamically and a result value for the column from Dynamic-Column-Values. Pass these items to an RFX function call appropriate to the data type of the column. For descriptions of the lists, see Lists of Columns.

In the common case, in your RFX_Text function calls you extract CString objects from the lists, as in the following lines of code, where Columns-to-Bind-Dynamically is a CStringList called m_listName and Dynamic-Column-Values is a CStringList called m_listValue:

RFX_Text( pFX,
            m_listName.GetNext( posName ),
            m_listValue.GetNext( posValue ));

For an example of such a loop added to DoFieldExchange, see CSections::DoFieldExchange in the file SECTIONS.CPP in the MFC Database sample . For more information about RFX functions, see in the Class Library Reference.

****Tip   ****If the new columns are of different data types, use a switch statement in your loop to call the appropriate RFX function for each type.

When the framework calls DoFieldExchange during the Open process to bind columns to the recordset, the RFX calls for the static columns bind those columns. Then your loop repeatedly calls RFX functions for the dynamic columns.

See the complete source code in the MFC Database sample .

See Also   Recordset: Working with Large Data Items (ODBC)