Vendor escape clauses in CLI applications

Escape clauses are used extensively by ODBC to define SQL extensions. CLI translates the ODBC extensions into the correct Db2® syntax. Use the SQLNativeSql() function to display the resulting syntax.

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.

CLI used both the standard and shorthand syntax for escape clauses. The standard syntax has been deprecated (although 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 per current ODBC standard.

Shorthand escape clause syntax

The format of an escape clause definition is:

   { extended SQL text }

to define the listed SQL extensions:

Extended date, time, timestamp data
Outer join
LIKE predicate
Stored procedure call
Extended scalar functions
- Numeric functions
- String functions
- System functions

ODBC date, time, timestamp data

The ODBC escape clauses for date, time, and timestamp data are:

    {d 'value'}
    {t 'value'}
    {ts 'value'}

d indicates value is a date in the yyyy-mm-dd format,
t indicates value is a time in the hh:mm:ss format
ts indicates value is a timestamp in the yyyy-mm-dd hh:mm:ss[.f...] format.

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.

ODBC outer join

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, CLI will translate the 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.

LIKE predicate

In a SQL LIKE predicate, the metacharacter % matches zero or more of any character, and the metacharacter _ matches any one character. The SQL 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 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.

Stored procedure call

The ODBC escape clause for calling a stored procedure is:

 {[?=]call procedure-name[([parameter][,[parameter]]...)]}

where:

[?=] indicates the optional parameter marker for the return value
procedure-name specifies the name of a procedure stored at the data source
parameter specifies a procedure 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.

For example, CLI will translate the statement:

    {CALL NETB94(?,?,?)}

To an internal CALL statement format:

    CALL NEBT94(?, ?, ?)

ODBC scalar functions

Scalar functions such as string length, substring, or trim can be used on columns of a result set and on columns that restrict rows of a result set. The ODBC escape clause for scalar functions is:

    {fn scalar-function}

Where, scalar-function can be any function listed in the list of extended scalar functions.

For example, CLI will translate the 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 options: SQL_NUMERIC_FUNCTIONS, SQL_STRING_FUNCTIONS, SQL_SYSTEM_FUNCTIONS, and SQL_TIMEDATE_FUNCTIONS.