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:
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.
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. |
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). |
When you call a recordset object’s Open member function, Open constructs an SQL statement using some or all of the following ingredients:
Tip To use the SQL GROUP BY clause (and possibly the HAVING clause), append the clause(s) to the end of your filter string.
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.
Besides supplying a filter, a sort order, or parameters, you can take the following actions to customize your recordset’s selection:
See the article SQL: Customizing Your Recordset’s SQL Statement (ODBC). That article describes the kinds of SQL statements (or partial statements) you can pass to Open and what the framework does with them.
Note If the custom string you pass does not begin with “SELECT” or “{CALL”, MFC assumes it contains a table name. This also applies to the next bulleted item below.
You can have GetDefaultSQL return any of the items that you can pass in the lpszSQL parameter to Open. If you don’t pass a custom SQL string in lpszSQL, the framework uses the string that GetDefaultSQL returns. At a minimum, GetDefaultSQL must return a single table name. But you can have it return multiple table names, a full SELECT statement, an ODBC CALL statement, and so on. For a list of what you can pass to lpszSQL — or have GetDefaultSQL return — see the article SQL: Customizing Your Recordset’s SQL Statement (ODBC).
If you’re performing a join of two or more tables, rewrite GetDefaultSQL to customize the table list used in the SQL FROM clause. See the article Recordset: Performing a Join (ODBC).
See the article Recordset: Dynamically Binding Data Columns (ODBC).
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)