Recordset: How Recordsets Select Records (ODBC)

HomeOverviewHow Do IFAQSampleTutorialODBC Driver List

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

This article explains:

Recordsets select records from a data source through an ODBC driver by sending SQL statements to the driver. The SQL sent depends on how you design and open your recordset class.

Your Options in Selecting Records

The following table shows your options in selecting records.

How and When You Can Affect a Recordset

When you ... You can ...
Declare your recordset class with ClassWizard... Specify which table to select from.

Specify which columns to include.

See ClassWizard: Creating a Recordset Class.

Complete your recordset class implementation... Override member functions such as OnSetOptions (advanced) to set application-specific options or to change defaults. Specify parameter data members if you want a parameterized recordset.
Construct a recordset object (before you call Open) and then... Specify a search condition (possibly compound) for use in a WHERE clause that filters the records. See Recordset: Filtering Records (ODBC).

Specify a sort order for use in an ORDER BY clause that sorts the records. See Recordset: Sorting Records (ODBC).

Specify parameter values for any parameters you added to the class. See Recordset: Parameterizing a Recordset (ODBC).

Run the recordset’s query by calling Open... Specify a custom SQL string to replace the default SQL string set up by ClassWizard. See CRecordset::Open in the Class Library Reference and SQL: Customizing Your Recordset’s SQL Statement (ODBC).
Call Requery to requery the recordset with the latest values on the data source... Specify new parameters, filter, or sort. See Recordset: Requerying a Recordset (ODBC).

How a Recordset Constructs Its SQL Statement

When you call a recordset object’s Open member function, Open constructs an SQL statement using some or all of the following ingredients:

Open constructs an SQL SELECT statement from these ingredients. See Customizing the Selection for details about how the framework uses the ingredients.

After constructing the statement, Open sends the SQL to the ODBC Driver Manager (and the ODBC Cursor Library if it is in memory), which sends it on to the ODBC driver for the specific DBMS. The driver communicates with the DBMS to carry out the selection on the data source and fetches the first record. The framework loads the record into the field data members of the recordset.

You can use a combination of these techniques to open tables and to construct a query based on a join of multiple tables. With additional customization, you can call predefined queries (stored procedures), select table columns not known at design time and bind them to recordset fields, or perform most other data-access tasks. Tasks you can’t accomplish by customizing recordsets can still be accomplished by calling ODBC API functions or directly executing SQL statements with CDatabase::ExecuteSQL.

Customizing the Selection

Besides supplying a filter, a sort order, or parameters, you can take the following actions to customize your recordset’s selection:

If you want to base the recordset on a complex SQL statement, you’ll need to use some combination of these customization techniques. For example, perhaps you want to use SQL clauses and keywords not directly supported by recordsets, or perhaps you’re joining multiple tables.

See Also   Recordset: How Recordsets Update Records (ODBC), ODBC, SQL, Recordset: Locking Records (ODBC)