Recordset: Parameterizing a Recordset (ODBC)

Article
09/15/2006

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:

The purpose of a parameterized recordset.
When and why you might want to parameterize a recordset.
How to declare parameter data members in your recordset class.
How to pass parameter information to a recordset object at run time.

Parameterized Recordsets

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

It may result in better execution speed.
It lets you build a query at run time, based on information not available to you at design time, such as information obtained from your user or calculated at run time.

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:

Passing run-time arguments to a predefined query.

To pass parameters to a stored procedure, you must specify a complete custom ODBC CALL statement — with parameter placeholders — when you call Open, overriding the recordset’s default SQL statement. See in the Class Library Reference and the articles SQL: Customizing Your Recordset’s SQL Statement (ODBC) and Recordset: Declaring a Class for a Predefined Query (ODBC).
Efficiently performing numerous requeries with different parameter information.

For example, each time your end-user looks up information for a particular student in the student registration database, you can specify the student’s name or ID as a parameter obtained from the user. Then, when you call your recordset’s Requery member function, the query selects only that student’s record.

Your recordset’s filter string, stored in m_strFilter, might look like this:
```
"StudentID = ?"
```
Suppose you obtain the student ID in the variable strInputID. When you set a parameter to strInputID (for example, the student ID 100) the value of the variable is bound to the parameter placeholder represented by the “?” in the filter string.

Assign the parameter value as follows:
```
strInputID = "100";
...
m_strParam = strInputID;
```
Note that you would not want to set up a filter string this way:
```
m_strFilter = "StudentID = 100";   // 100 is incorrectly quoted
                                   // for some drivers
```
For a discussion of how to use quotes correctly for filter strings, see the article Recordset: Filtering Records (ODBC).

The parameter value is different each time you requery the recordset for a new student ID.

****Tip ****Using a parameter is more efficient than simply a filter. For a parameterized recordset, the database must process an SQL SELECT statement only once. For a filtered recordset without parameters, the SELECT statement must be processed each time you Requery with a new filter value.

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

Run ClassWizard and create the class. See the article ClassWizard: Creating a Recordset Class.
Specify field data members for the recordset’s columns.
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.
Modify the 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
```
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.
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 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 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

Construct the recordset object.
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.
Assign a run-time parameter value to each parameter data member of the object.
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 . You can likewise check whether a parameter data member is Null, using .

Share via

Recordset: Parameterizing a Recordset (ODBC)

Parameterized Recordsets

When to Use Parameters

Parameterizing Your Recordset Class

Passing Parameter Values at Run Time

Additional resources