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 CLI and ODBC support vendor escape clauses as defined by X/Open.
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 through a different syntax, then the escape clauses increase the portability of the application.
--(*vendor(vendor-identifier),
product(product-identifier) extended SQL text*)--
Applications
should now only use the shorthand syntax per current ODBC standard. { extended SQL text }
to
define the listed SQL extensions: {d 'value'}
{t 'value'}
{ts 'value'}
For example, the SELECT * FROM EMPLOYEE WHERE HIREDATE={d '1994-03-29'} statement can be used to issue a query against the EMPLOYEE table.
CLI will translate the select 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.
{oj outer-join}
where outer
join is table-name {LEFT | RIGHT | FULL} OUTER JOIN
{table-name | outer-join}
ON search-condition
SELECT * FROM {oj T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3}
WHERE T1.C2>20
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3 WHERE T1.C2>20
{escape 'escape-character'}
where escape-character is
any character supported by the DB2 rules
governing the use of the SQL ESCAPE clause.As an example of how to use an "escape" ODBC escape clause, suppose you had a table Customers with the columns Name and Growth. The Growth column contains data having the metacharacter '%'. The SELECT Name FROM Customers WHERE Growth LIKE '1_\%'{escape '\'} statement would select all of the values from Name that have values in Growth only between 10% and 19%.
Applications that are not concerned about portability across different vendor DBMS products should pass an SQL ESCAPE clause directly to the data source. To determine when LIKE predicate escape characters are supported by a particular DB2 data source, an application can call SQLGetInfo() with the SQL_LIKE_ESCAPE_CLAUSE information type.
{[?=]call procedure-name[([parameter][,[parameter]]...)]}
A procedure can have zero or more parameters.
ODBC specifies 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 through SQLBindParameter(). CLI will return the return code as the procedure's return value if ?= is present in the escape clause. If ?= is not present, and if the stored procedure return code is not SQL_SUCCESS, then the application can retrieve diagnostics, including the SQLCODE, using the SQLGetDiagRec() and SQLGetDiagField() functions. CLI supports literals as procedure arguments, however vendor escape clauses must be used. For example, the CALL storedproc ('aaaa', 1) statement would not succeed, but {CALL storedproc ('aaaa', 1)} statement would. If a parameter is an output parameter, it must be a parameter marker.
{CALL NETB94(?,?,?)}
CALL NEBT94(?, ?, ?)
{fn scalar-function}
Where, scalar-function can be any function listed in the list of extended scalar functions.
SELECT {fn CONCAT(FIRSTNAME,LASTNAME)} FROM EMPLOYEE
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 options: SQL_NUMERIC_FUNCTIONS, SQL_STRING_FUNCTIONS, SQL_SYSTEM_FUNCTIONS, and SQL_TIMEDATE_FUNCTIONS.