Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
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 ExecuteSQL.
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)
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.
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 SetFieldType, 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.
You must also increment the initialization value for the m_nFields 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.
{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 (?)}";
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 m_nFields and m_nParams. 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 CFieldExchange::SetFieldType.
See Also Recordset: Requerying a Recordset (ODBC), Recordset: Declaring a Class for a Table (ODBC), Recordset: Performing a Join (ODBC)