Statements::JAVA4

Escape Syntax

The SQL string used in a Statement object may include JDBC escape syntax. Escape
syntax allows the driver to more easily scan for syntax that requires special
processing. Implementing this special processing in the driver layer improves
application portability.

Special escape processing might be needed for the following:

• commonly used features that do not have standard syntax defined by SQL, or where the native syntax supported by the underlying data source varies widely among vendors. The driver may translate the escape syntax to a specific native syntax in this case.
• features that are not supported by the underlying data source but are implemented by the driver.

Escape processing for a Statement object is turned on or off using the method setEscapeProcessing, with the default being on. The RowSet interface also includes a setEscapeProcessing method. The RowSet method applies to the SQL string used to populate a RowSet object. The setEscapeProcessing method does not work for a PreparedStatement object because its SQL string may have been precompiled when the PreparedStatement object was created.

JDBC defines escape syntax for the following:

• scalar functions
• date and time literals
• outer joins
• calling stored procedures
• escape characters for LIKE clauses

Scalar Functions

Almost all underlying data sources support numeric, string, time, date, system, andconversion functions on scalar values. The escape syntax to access a scalar function is:

{fn (argument list)}

For example, the following code calls the function concat with two arguments to be concatenated:

{fn concat("Hot", "Java")}

The following syntax gets the name of the current database user:
{fn user()}

Scalar functions may be supported by different data sources with slightly different native syntax, and they may not be supported by all drivers. The driver will either map the escaped function call into the native syntax or implement the function directly. Various DatabaseMetaData methods list the functions that are supported. For example, the method getNumericFunctions returns a comma-separated list of the Open Group CLI names of numeric functions, the method getStringFunctions returns string functions, and so on.
“Scalar Functions" for a list of the scalar functions a driver is expected to support. A driver is required to implement these functions only if the data source supports them, however.

Date and Time Literals

Data sources differ widely in the syntax they use for date, time, and timestamp literals. The JDBC API supports ISO standard format for the syntax of these literals, using an escape clause that the driver translates to native syntax.

The escape syntax for date literals is:

{d 'yyyy-mm-dd'}

The driver will replace the escape clause with the equivalent native representation. For example, the driver might replace {d ’1999-02-28’} with '28-FEB-99' if that is the appropriate format for the underlying data source.

The escape syntax for TIME and TIMESTAMP literals are:
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f . . .'}

The fractional seconds (.f . . .) portion of the timestamp can be omitted.

Outer Joins

Outer joins are an advanced feature and are not supported by all data sources. Consult relevant SQL documentation for an explanation of outer joins.

The escape syntax for an outer join is:

{oj }

where has the form:

table {LEFT|RIGHT|FULL} OUTER JOIN {table | } ON searchcondition
(Note that curly braces ({}) in the preceding line indicate that one of the items
between them must be used; they are not part of the syntax.) The following SELECT
statement uses the escape syntax for an outer join.

Statement stmt = con.createStatement();
stmt.executeQuery("SELECT * FROM {oj TABLE1 " +
"LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}");

The JDBC API provides three DatabaseMetaData methods for determining the kinds of outer joins a driver supports: supportsOuterJoins, supportsFullOuterJoins, and supportsLimitedOuterJoins.

Stored Procedures

If a database supports stored procedures, they can be invoked using JDBC escape syntax as follows:

{call [()]}

or, where a procedure returns a result parameter:

{? = call [()]}

The square brackets indicate that the (argument-list) portion is optional. Input arguments may be either literals or parameter markers. See “Setting Parameters” The method DatabaseMetaData.supportsStoredProcedures returns true if the database supports stored procedures.

LIKE Escape Characters

The percent sign (%) and underscore (_) characters are wild card characters in SQL LIKE clauses (% matches zero or more characters, and _ matches exactly one character). In order to interpret them literally, they can be preceded by a backslash (\), which is a special escape character in strings. One can specify which character to use as the escape character by including

the following syntax at the end of a query:

{escape ''}

For example, the following query uses the backslash as an escape character, and finds identifier names that begin with an underscore. Note that the Java compiler will not recognize the backslash as a character unless it is preceded by a backslash.

stmt.executeQuery("SELECT name FROM Identifiers " +
"WHERE Id LIKE '\\_%' {escape '\\'}");