The X/Open SQL CAE specification defined an escape clause as: "a syntactic mechanism for vendor-specific SQL extensions to be implemented in the framework of standardized SQL". Both DB2 CLI and ODBC support vendor escape clauses as defined by X/Open.
Currently, escape clauses are used extensively by ODBC to define SQL extensions. DB2 CLI translates the ODBC extensions into the correct DB2 syntax. The SQLNativeSql() function can be used to display the resulting syntax.
If an application is only going to access DB2 data sources, then there is no reason to use the escape clauses. If an application is going to access other data sources that offer the same support, but uses different syntax, then the escape clauses increase the portability of the application.
DB2 CLI used both the standard and shorthand syntax for escape clauses. The standard syntax has been deprecated (although DB2 CLI still supports it). An escape clause using the standard syntax took the form:
--(*vendor(vendor-identifier),
product(product-identifier) extended SQL text*)--
Applications should now only use the shorthand syntax, as described below, to remain current with the latest ODBC standards.
The format of an escape clause definition is:
{ extended SQL text }
to define the following SQL extensions:
The ODBC escape clauses for date, time, and timestamp data are:
{d 'value'}
{t 'value'}
{ts 'value'}
For example, the following statement can be used to issue a query against the EMPLOYEE table:
SELECT * FROM EMPLOYEE WHERE HIREDATE={d '1994-03-29'}
DB2 CLI will translate the above statement to a DB2 format. SQLNativeSql() can be used to return the translated statement.
The ODBC escape clauses for date, time, and timestamp literals can be used in input parameters with a C data type of SQL_C_CHAR.
The ODBC escape clause for outer join is:
{oj outer-join}
where outer join is
table-name {LEFT | RIGHT | FULL} OUTER JOIN
{table-name | outer-join}
ON search-condition
For example, DB2 CLI will translate the following statement:
SELECT * FROM {oj T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3}
WHERE T1.C2>20
to IBM's format, which corresponds to the SQL92 outer join syntax.
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3 WHERE T1.C2>20
| Note: | Not all DB2 servers support outer join. To determine if the current server supports outer joins, call SQLGetInfo() with the SQL_SQL92_RELATIONAL_JOIN_OPERATORS and SQL_OJ_CAPABILITIES options. |
In a SQL LIKE predicate, the metacharacter % matches zero or more of any character and the metacharacter _ matches any one character. The ESCAPE clause allows the definition of patterns intended to match values that contain the actual percent and underscore characters by preceding them with an escape character. The escape clause ODBC uses to define the LIKE predicate escape character is:
{escape 'escape-character'}
where escape-character is any character supported by the DB2 rules governing the use of the ESCAPE clause.
Applications that are not concerned about portability across different vendor DBMS products should pass the ESCAPE clause directly to the data source. To determine when LIKE predicate escape characters are supported by a particular DB2 data source, an application should call SQLGetInfo() with the SQL_LIKE_ESCAPE_CLAUSE information type.
The ODBC escape clause for calling a stored procedure is:
{[?=]call procedure-name[([parameter][,[parameter]]...)]}
A procedure may have zero or more parameters. (The square brackets ([ ]) indicate optional arguments.)
ODBC species the optional parameter ?= to represent the procedure's return value, which, if present, will be stored in the location specified by the first parameter marker as defined via SQLBindParameter(). DB2 CLI will return the SQLCODE as the procedure's return value if ?= is present in the escape clause. If ?= is not present, then the application can retrieve the SQLCA by using the SQLGetSQLCA() function. Unlike ODBC, DB2 CLI does not support literals as procedure arguments, parameter markers must be used.
For more information about stored procedures, refer to Using Stored Procedures or the Application Development Guide.
For example, DB2 CLI will translate the following statement:
{CALL NETB94(?,?,?)}
To an internal CALL statement format:
CALL NEBT94(?, ?, ?)
Scalar functions such as string length, substring, or trim can be used on columns of a result sets and on columns that restrict rows of a result set. The ODBC escape clauses for scalar functions is:
{fn scalar-function}
Where, scalar-function can be any function listed in Appendix D, Extended Scalar Functions.
For example, DB2 CLI will translate of the following statement:
SELECT {fn CONCAT(FIRSTNAME,LASTNAME)} FROM EMPLOYEE
to:
SELECT FIRSTNAME CONCAT LASTNAME FROM EMPLOYEE
SQLNativeSql() can be called to obtain the translated SQL statement.
To determine which scalar functions are supported by the current server referenced by a specific connection handle, call SQLGetInfo() with the SQL_NUMERIC_FUNCTIONS, SQL_STRING_FUNCTIONS, SQL_SYSTEM_FUNCTIONS, and SQL_TIMEDATE_FUNCTIONS options.