SQL Escape Sequences in ODBC

A number of SQL language features, such as outer joins and scalar function calls, tend to be implemented by providers in a DB-specific form, which can happen even where standards bodies have defined standard syntaxes. For that reason, ODBC has defined its own escape sequences for some standard syntaxes. These language features are:

• Date, time, timestamp, and datetime interval literals

• Scalar functions such as numeric, string, and data type conversion functions

• LIKE predicate escape character

• Outer joins

• Procedure calls

The escape sequence is recognized and parsed by ODBC drivers, which replace the escape sequences with the correct DB-specific grammar. Note however, that the drivers only support those escape sequences that they can map to underlying language features. For example, if the data source does not support outer joins, neither will the driver. It is thus possible to choose to use either the ODBC escape sequence or the DB-specific syntax. However, applications that use the DB-specific syntax will not be interoperable.

ODBC escape sequences are enclosed in braces, e.g. {extension}. Examples of some types of escape sequence are as follows:

Date, time, and timestamp escape sequence literals are:

{literal-type 'value'}

e.g. {d '2009-06-30'}

where literal-type is one of the following: