Recordset: Declaring a Class for a Predefined Query (ODBC)

Article
09/15/2006

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

This article explains how to create a recordset class for a predefined query (sometimes called a “stored procedure,” as in Microsoft SQL Server).

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If bulk row fetching is implemented, the process is very similar. To understand the differences between recordsets that implement bulk row fetching and those that do not, see the article Recordset: Fetching Records in Bulk (ODBC).

Some database management systems (DBMSs) allow you to create a predefined query and call it from your programs like a function. The query has a name, may or may not take parameters, and may or may not return records. The procedure in this article describes how to call a predefined query that returns records (and perhaps takes parameters).

The database classes don’t support updating predefined queries. The difference between a snapshot predefined query and a dynaset predefined query is not updatability but whether changes made by other users (or other recordsets in your program) are visible in your recordset.

****Tip ****You don’t need a recordset to call a predefined query that doesn’t return records. Prepare the SQL statement as described below, but execute it by calling the CDatabase member function .

You can create a single recordset class to manage calling a predefined query, but you must do some of the work yourself. ClassWizard doesn’t support creating a class specifically for this purpose.

To create a class for calling a predefined query (stored procedure)

Use ClassWizard to create a recordset class for the table that contributes the most columns returned by the query. This gives you a head start.
Manually add field data members for any columns of any tables that the query returns but that ClassWizard didn’t create for you. Add them outside the “//{{AFX_FIELD” comments.

For example, if the query returns three columns each from two additional tables, add six field data members (of the appropriate data types) to the class.
Manually add RFX function calls in the DoFieldExchange member function of the class, one corresponding to the data type of each added field data member.
```
Add these function calls outside the “//{{AFX_FIELD_MAP” comments. Immediately before these RFX calls, call , as shown here:
pFX->SetFieldType( CFieldExchange::outputColumn );
```
****Note ****You must know the data types and the order of columns returned in the result set. The order of RFX function calls in DoFieldExchange must match the order of result set columns.
Manually add initializations for the new field data members in the recordset class constructor.

You must also increment the initialization value for the data member. ClassWizard writes the initialization, but it only covers the field data members it adds for you. Put the increment statement outside the comment brackets. For example:
```
m_nFields += 6;
//{{AFX_FIELD(CDelinquents, CRecordset)
...
//}}AFX_FIELD
```
Some data types shouldn’t be initialized here, for example, CLongBinary or byte arrays.
If the query takes parameters, add a parameter data member for each parameter, an RFX function call for each, and an initialization for each.
You must increment m_nParams for each added parameter, as you did m_nFields for added fields in step 4 above. See the article Recordset: Parameterizing a Recordset (ODBC) for details.
Manually write an SQL statement string with the following form:
```
{CALL proc-name [(? [, ?]...)]}
```
where CALL is an ODBC keyword, proc-name is the name of the query as it is known on the data source, and the “?” items are placeholders for the parameter values you supply to the recordset at run time (if any). The following example prepares a placeholder for one parameter:
```
CString mySQL = "{CALL Delinquent_Accts (?)}";
```
In the code that opens the recordset, first set the values of the recordset’s parameter data members, then call the Open member function, passing your SQL string for the lpszSQL parameter. Or instead, replace the string returned by the GetDefaultSQL member function in your class.

The following examples illustrate the procedure for calling a predefined query, named Delinquent_Accts, which takes one parameter for a sales district number. This query returns three columns: Acct_No, L_Name, Phone. All columns are from the Customers table.

The recordset below specifies field data members for the columns the query returns and a parameter for the sales district number requested at run time.

class CDelinquents : public CRecordset
{
// Field/Param Data
    //{{AFX_FIELD(CDelinquents, CRecordset)
    LONG m_lAcct_No;
    CString m_strL_Name;
    CString m_strPhone;
    //}}AFX_FIELD
    LONG m_lDistParam;
    // ...
};

This class declaration is as ClassWizard writes it, except for the m_lDistParam member added manually outside the “//{{AFX_FIELD” comment. Other members below the comments aren’t shown here.

The next example shows the initializations for the data members in the CDelinquents constructor. You add the two lines outside the comment brackets.

CDelinquents::CDelinquents(CDatabase* pdb)
   : CRecordset(pdb)
{
    //{{AFX_FIELD_INIT(CDelinquents)
    m_lAcct_No = 0;
    m_strL_Name = "";
    m_strPhone = "";
    m_nFields = 3;
    //}}AFX_FIELD_INIT
    m_nParams = 1;
    m_lDistParam = 0;
}

Note the initializations for and . ClassWizard initializes m_nFields; you initialize m_nParams.

The next example shows the RFX functions in CDelinquents::DoFieldExchange:

void CDelinquents::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CDelinquents)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, "Acct_No", m_lAcct_No);
    RFX_Text(pFX, "L_Name", m_strL_Name);
    RFX_Text(pFX, "Phone", m_strPhone);
//}}AFX_FIELD_MAP
    pFX->SetFieldType(CFieldExchange::param);
    RFX_Long(pFX, "Dist_No", m_lDistParam);
}

Besides making the RFX calls for the three returned columns, this code manages binding the parameter you pass at run time. The parameter is keyed to the Dist_No (district number) column.

The next example shows how to set up the SQL string and how to use it to open the recordset.

// Construct a CDelinquents recordset object
CDelinquents rsDel( NULL );
CString strSQL = "{CALL Delinquent_Accts (?)}"
// Specify a parameter value (obtained earlier from the user)
rsDel.m_lDistParam = lDistrict;
// Open the recordset and run the query
if( rsDel.Open( CRecordset::snapshot, strSQL ) )
    // Use the recordset ...

This code constructs a snapshot, passes it a parameter obtained earlier from the user, and calls the predefined query. When the query runs, it returns records for the specified sales district. Each record contains columns for the account number, customer’s last name, and customer’s phone number.

****Tip ****You might want to handle a return value (output parameter) from a stored procedure. For more information and an example, see .

Share via

Recordset: Declaring a Class for a Predefined Query (ODBC)

Additional resources