Recordset: Parameterizing a Recordset (ODBC)

HomeOverviewHow Do IFAQSampleTutorialODBC Driver List

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

Sometimes you’d like to be able to select records at run time, using information you’ve calculated or obtained from your end-user. Recordset parameters let you accomplish that goal.

This article explains:

Parameterized Recordsets

A parameterized recordset lets you pass parameter information at run time. This has two valuable effects:

When you call Open to run the query, the recordset uses the parameter information to complete its SQL SELECT statement. You can parameterize any recordset.

When to Use Parameters

Typical uses for parameters include:

For more information about filters, see the article Recordset: Filtering Records (ODBC).

Parameterizing Your Recordset Class

Note   This section applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, implementing parameters is a similar process. For more information, see the article Recordset: Fetching Records in Bulk (ODBC).

Before you create your recordset class, determine what parameters you need, what their data types are, and how the recordset will use them.

To parameterize a recordset class

  1. Run ClassWizard and create the class. See the article ClassWizard: Creating a Recordset Class.

  2. Specify field data members for the recordset’s columns.

  3. After ClassWizard writes the class to a file in your project, go to the .H file and manually add one or more parameter data members to the class declaration. The addition might look something like the following example, part of a snapshot class designed to answer the query “Which students are in the senior class?”
    class CStudentSet : public CRecordset
    {
    // Field/Param Data
        //{{AFX_FIELD(CStudentSet, CRecordset)
        CString m_strFirstName;
        CString m_strLastName;
        CString m_strStudentID;
        CString m_strGradYear;
        //}}AFX_FIELD
    
        CString m_strGradYrParam;
    };
    

    ClassWizard writes field data members inside the “//{{AFX_FIELD” comment brackets. You add your parameter data members outside the comment brackets. The convention is to append the word “Param” to each name.

  4. Modify the DoFieldExchange member function definition in the .CPP file. Add an RFX function call for each parameter data member you added to the class. For information on writing your RFX functions, see the article Record Field Exchange: How RFX Works. Precede the RFX calls for the parameters with a single call to
    pFX->SetFieldType( CFieldExchange::param );
    // RFX calls for parameter data members
    
  5. In the constructor of your recordset class, increment the count of parameters, m_nParams.

    For information, see The Recordset Constructor in the article Record Field Exchange: Working with the Wizard Code.

  6. When you write the code that creates a recordset object of this class, place a “?” (question mark) symbol in each place in your SQL statement string(s) where a parameter is to be replaced.

    At run time, “?” placeholders are filled, in order, by the parameter values you pass. The first parameter data member set after the SetFieldType call replaces the first “?” in the SQL string, the second parameter data member replaces the second “?”, and so on.

Important   Parameter order is important: the order of RFX calls for parameters in your DoFieldExchange function must match the order of the parameter placeholders in your SQL string.

Tip   The most likely string to work with is the string you specify (if any) for the class’s m_strFilter data member, but some ODBC drivers may allow parameters in other SQL clauses.

Passing Parameter Values at Run Time

You must specify parameter values before you call Open (for a new recordset object) or Requery (for an existing one).

To pass parameter values to a recordset object at run time

  1. Construct the recordset object.

  2. Prepare a string or strings, such as the m_strFilter string, containing the SQL statement, or part(s) of it. Put “?” placeholders where the parameter information is to go.

  3. Assign a run-time parameter value to each parameter data member of the object.

  4. Call the Open member function (or Requery, for an existing recordset).

For example, suppose you want to specify a filter string for your recordset using information obtained at run time. Assume you have constructed a recordset of class CStudentSet earlier — called rsStudents — and now want to requery it for a particular kind of student information.

// Set up a filter string with 
// parameter placeholders
rsStudents.m_strFilter = "GradYear <= ?";

// Obtain or calculate parameter values 
// to pass--simply assigned here 
CString strGradYear = GetCurrentAcademicYear( );

// Assign the values to parameter data members
rsStudents.m_strGradYrParam = strGradYear;

// Run the query
if( !rsStudents.Requery( ) )
    return FALSE;

The recordset contains records for those students whose records meet the conditions specified by the filter, which was constructed from run-time parameters. In this case, the recordset contains records for all senior students.

Note   If needed, you can set the value of a parameter data member to Null, using SetParamNull. You can likewise check whether a parameter data member is Null, using IsFieldNull.

See Also   Recordset: Adding, Updating, and Deleting Records (ODBC), Recordset: How Recordsets Select Records (ODBC)