SQL
| Overview | How Do I | FAQ | Sample | | ODBC Driver List
Structured Query Language (SQL) is a way to communicate with a relational database that lets you define, query, modify, and control the data. Using SQL syntax, you can construct a statement that extracts records according to criteria you specify.
****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 statements begin with a keyword “verb” such as CREATE or SELECT. SQL is a very powerful language; a single statement can affect an entire table.
Many versions of SQL exist, each developed with a particular DBMS in mind. The MFC database classes recognize a set of SQL statements that corresponds to the X/Open and SQL Access Group Common Applications Environment (CAE) SQL draft specification (1991). For details on the syntax of these statements, see Appendix C in the ODBC SDK Programmer’s Reference on the MSDN Library CD.
This article explains:
The relationship between ODBC and SQL.
The most common SQL keywords used by the database classes.
How the database classes use SQL.
Open Database Connectivity (ODBC)
The database classes are implemented with ODBC, which uses SQL in a call-level interface rather than embedding SQL commands in the code. ODBC uses SQL to communicate with a data source through ODBC drivers. These drivers interpret the SQL and translate it, if necessary, for use with a particular database format, such as Microsoft Access. For more information about how ODBC uses SQL, see the article ODBC and the ODBC SDK Programmer’s Reference on the MSDN Library CD.
The Database Classes
The database classes are designed to let you manipulate and update data in an existing data source. ClassWizard and the database classes construct most of the SQL statements for you.
The database classes use a portion of SQL known as the Data Manipulation Language (DML). These commands let you work with all or part of the data source, add new records, edit records, and delete records. The following table lists the most common SQL keywords and the ways the database classes use them.
Some Common SQL Keywords
| SQL keyword | ClassWizard and database classes use it ... |
| SELECT | To identify which tables and columns in the data source are to be used. |
| WHERE | To apply a filter that narrows the selection. |
| ORDER BY | To apply a sort order to the recordset. |
| INSERT | To add new records to a recordset. |
| DELETE | To delete records from a recordset. |
| UPDATE | To modify the fields of a record. |
In addition, the database classes recognize ODBC CALL statements, which you can use to call a predefined query (or stored procedure) on some data sources. The ODBC database driver interprets these statements and substitutes the command appropriate for each DBMS.
****Note ****Not all DBMSs support CALL statements.
If the classes cannot recognize a user-supplied statement in CRecordset::Open, it is interpreted as a table name.
For an explanation of how the framework constructs SQL statements, see the articles Recordset: How Recordsets Select Records (ODBC) and SQL: Customizing Your Recordset’s SQL Statement (ODBC).
SQL databases use data types similar to those used in C and C++. For a discussion of these similarities, see the article SQL: SQL and C++ Data Types (ODBC).
You can find more information about SQL, including a list of supported SQL statements, data types, SQL core grammar, and a reading list of recommended publications about SQL, in the ODBC SDK Programmer's Reference on the MSDN Library CD.
How the Database Classes Use SQL
The recordsets you derive from the database classes use ODBC to communicate with a data source, and ODBC retrieves records from the data source by sending SQL statements. This article explains the relationship between the database classes and SQL.
A recordset constructs an SQL statement by building up the pieces of an SQL statement into a CString. The string is constructed as a SELECT statement, which returns a set of records.
When the recordset calls ODBC to send an SQL statement to the data source, the ODBC Driver Manager passes the statement to the ODBC driver, and the driver sends it to the underlying DBMS. The DBMS returns a result set of records, and the ODBC driver returns the records to the application. The database classes let your program access the result set in a type-safe C++ class derived from CRecordset.
The following articles provide more information about how the database classes use SQL:
See Also ODBC