SQL: Customizing Your Recordset’s SQL Statement (ODBC)

HomeOverviewHow Do IFAQSampleTutorialODBC Driver List

This article explains:

Note   This information applies to the MFC ODBC classes. If you’re working with the MFC DAO classes, see the topic Comparison of Microsoft Jet Database Engine SQL and ANSI SQL in DAO Help.

SQL Statement Construction

Your recordset bases record selection primarily on an SQL SELECT statement. When you declare your class with ClassWizard, the wizard writes an overriding version of the GetDefaultSQL member function that looks something like this (for a recordset class called CAuthors).

CString CAuthors::GetDefaultSQL()
{
    return "AUTHORS";
}

By default, this override returns the table name you specified with ClassWizard — in the example, the table name is “AUTHORS.” When you later call the recordset’s Open member function, Open constructs a final SELECT statement of the form:

SELECT rfx-field-list FROM table-name [WHERE m_strFilter] 
       [ORDER BY m_strSort]

where table-name is obtained by calling GetDefaultSQL and rfx-field-list is obtained from the RFX function calls in DoFieldExchange. This is what you get for a SELECT statement unless you replace it with an overriding version at run time, although you can also modify the default statement with parameters or a filter.

Important   If you specify a column name that contains (or could contain) spaces, you must enclose the name in square brackets. For example, the name “First Name” should be “[First Name]”.

To override the default SELECT statement, pass a string containing a complete SELECT statement when you call Open. Instead of constructing its own default string, the recordset uses the string you supply. If your replacement statement contains a WHERE clause, don’t specify a filter in m_strFilter because you would then have two filter statements. Similarly, if your replacement statement contains an ORDER BY clause, don’t specify a sort in m_strSort so that you will not have two sort statements.

Caution   In the ENROLL tutorial application, filter strings typically use a parameter placeholder, “?”, rather than assigning a specific literal value, such as “MATH101”, at compile time. If you do use literal strings in your filters (or other parts of the SQL statement), you may have to “quote” (enclose in specified delimiters) such strings with a DBMS-specific literal prefix and literal suffix character (or characters). For example, the code in the ENROLL tutorial uses a single quote character to bracket the value “MATH101” assigned as the filter.

You may also encounter special syntactic requirements for operations such as outer joins, depending on your DBMS. Use ODBC functions to obtain this information from your driver for the DBMS. For example, call ::SQLGetTypeInfo for a particular data type, such as SQL_VARCHAR, to request the LITERAL_PREFIX and LITERAL_SUFFIX characters. If you are writing database-independent code, see Appendix C in the ODBC SDK Programmer’s Reference on the MSDN Library CD for detailed syntax information.

A recordset object constructs the SQL statement that it uses to select records unless you pass a custom SQL statement. How this is done depends mainly on the value you pass in the lpszSQL parameter of the Open member function.

The general form of an SQL SELECT statement is:

SELECT [ALL | DISTINCT] column-list FROM table-list
    [WHERE search-condition][ORDER BY column-list [ASC | DESC]]

One way to add the DISTINCT keyword to your recordset’s SQL statement is to embed the keyword in the first RFX function call in DoFieldExchange. For example:

...
    RFX_Text(pFX, "DISTINCT CourseID", m_strCourseID);
...

Warning   Use this technique only with a recordset opened as read-only.

Overriding the SQL Statement

The following table shows the possibilities for the lpszSQL parameter to Open. The cases in the table are explained following the table.

The lpszSQL Parameter and the Resulting SQL String

Case What you pass in lpszSQL The resulting SELECT statement
1 NULL SELECT rfx-field-list FROM table-name

CRecordset::Open calls GetDefaultSQL to get the table name. The resulting string is one of Cases 2 through 5, depending on what GetDefaultSQL returns.

2 A table name SELECT rfx-field-list FROM table-name

The field list is taken from the RFX statements in DoFieldExchange. If m_strFilter and m_strSort are not empty, adds the WHERE and/or ORDER BY clauses.

3 * A complete SELECT statement but without a WHERE or ORDER BY clause As passed. If m_strFilter and m_strSort are not empty, adds the WHERE and/or ORDER BY clauses.
4 * A complete SELECT statement with a WHERE and/or ORDER BY clause As passed. m_strFilter and/or m_strSort must remain empty, or two filter and/or sort statements will be produced.
5 * A call to a stored procedure As passed.

* m_nFields must be less than or equal to the number of columns specified in the SELECT statement. The data type of each column specified in the SELECT statement must be the same as the data type of the corresponding RFX output column.

Case 1   lpszSQL = NULL

The recordset selection depends on what GetDefaultSQL returns when CRecordset::Open calls it. Cases 2 through 5 describe the possible strings.

Case 2   lpszSQL = a Table Name

The recordset uses record field exchange (RFX) to build the column list from the column names provided in the RFX function calls in the recordset class’s override of DoFieldExchange. If you used ClassWizard to declare your recordset class, this case has the same result as Case 1 (provided that you pass the same table name you specified in ClassWizard). If you don’t use ClassWizard to write your class, Case 2 is the simplest way to construct the SQL statement.

The following example constructs an SQL statement that selects records from the MFC tutorial sample ENROLL. When the framework calls the GetDefaultSQL member function, the function returns the name of the table, SECTION.

CString CEnrollSet::GetDefaultSQL()
{
    return "SECTION";
}

To obtain the names of the columns for the SQL SELECT statement, the framework calls the DoFieldExchange member function.

void CEnrollSet::DoFieldExchange(CFieldExchange* pFX)
{
    pFX->SetFieldType(CFieldExchange::outputColumn);
    //{{AFX_FIELD_MAP(CEnrollSet)
    RFX_Text(pFX, "CourseID", m_strCourseID);
    RFX_Text(pFX, "InstructorID", m_strInstructorID);
    RFX_Text(pFX, "RoomNo", m_strRoomNo);
    RFX_Text(pFX, "Schedule", m_strSchedule);
    RFX_Text(pFX, "SectionNo", m_strSectionNo);
    //}}AFX_FIELD_MAP
}

When complete, the SQL statement looks like this:

SELECT CourseID, InstructorID, RoomNo, Schedule, SectionNo 
    FROM SECTION

Case 3   lpszSQL = a SELECT/FROM Statement

You specify the column list by hand rather than relying on RFX to construct it automatically. You might want to do this when:

Case 4   lpszSQL = SELECT/FROM Plus WHERE and/or ORDER BY

You specify everything: the column list (based on the RFX calls in DoFieldExchange), the table list, and the contents of a WHERE and/or an ORDER BY clause. If you specify your WHERE and/or ORDER BY clauses this way, do not use m_strFilter and/or m_strSort.

Case 5   lpszSQL = a Stored Procedure Call

If you need to call a predefined query (such as a stored procedure in a Microsoft SQL Server database), you must write a CALL statement in the string you pass to lpszSQL. ClassWizard doesn’t support declaring a recordset class for calling a predefined query. Not all predefined queries return records.

If a predefined query doesn’t return records, you can use the CDatabase member function ExecuteSQL directly. For a predefined query that does return records, you must also manually write the RFX calls in DoFieldExchange for any columns the procedure returns. The RFX calls must be in the same order, and return the same types, as the predefined query. For more information, see the article Recordset: Declaring a Class for a Predefined Query (ODBC).

See Also   SQL: SQL and C++ Data Types (ODBC), SQL: Making Direct SQL Calls (ODBC)