Choosing an SQL Grammar

The first decision to be made when constructing SQL statements is which grammar to use. In addition to the grammars available from the various standards bodies, such as X/Open, ANSI, and ISO, virtually every DBMS vendor defines their own grammar, each of which varies slightly from the standard.

Appendix C, “SQL Grammar,” describes the minimum SQL grammar that all ODBC drivers must support. This grammar is a subset of the Entry level of SQL-92. Drivers may support additional grammar to conform to the Intermediate, Full, or FIPS 127-2 Transitional levels defined by SQL-92. For more information, see Appendix C, “SQL Grammar,” and SQL-92.

Appendix C also defines escape sequences containing standard grammar for commonly available language features, such as outer joins, that are not covered by the SQL-92 grammar. For more information, see Appendix C, “SQL Grammar,” and “Escape Sequences,” later in this chapter.

The grammar that is chosen affects how the driver processes the statement. Drivers must modify SQL-92 SQL and the ODBC-defined escape sequences to DBMS-specific SQL. Because most SQL grammars are based on one or more of the various standards, most drivers do little or no work to meet this requirement. It often consists only of searching for the escape sequences defined by ODBC and replacing them with DBMS-specific grammar. When a driver encounters grammar it does not recognize, it assumes the grammar is DBMS-specific and passes the SQL statement without modification to the data source for execution.

Thus, there are really two choices of grammar to use: the SQL-92 grammar (and the ODBC escape sequences) and a DBMS-specific grammar. Of the two, only the SQL-92 grammar is interoperable, so all interoperable applications should use it. Applications that are not interoperable can use the SQL-92 grammar or a DBMS-specific grammar. DBMS-specific grammars have two advantages: they can exploit any features not covered by SQL-92 and they are marginally faster because the driver does not have to modify them. The latter feature can be partially enforced by setting the SQL_ATTR_NOSCAN statement attribute, which stops the driver from searching for and replacing escape sequences.

If the SQL-92 grammar is used, the application can discover how it is modified by the driver by calling SQLNativeSql. This is often useful when debugging applications. SQLNativeSql accepts an SQL statement and returns it after the driver has modified it. Because this function is in the Core interface conformance level, it is supported by all drivers.