5.1.6 SQL Escape Syntax in Statements

Statement objects may contain SQL statements that use SQL escape syntax. Escape syntax signals the driver that the code within it should be handled differently. When escape processing is enabled (by calling Statement.setEscapeProcessing(true) or RowSet.setEscapeProcessing(true)), the driver will scan for any escape syntax and translate it into code that the particular database understands. This makes escape syntax DBMS-independent and allows a programmer to use features that might not otherwise be available.
An escape clause is demarcated by curly braces and a key word, which indicates the kind of escape clause.
{keyword . . . parameters . . . }
The following keywords are used to identify escape clauses:

• escape for LIKE escape characters
The percent sign (%) and underscore (_) characters work like wild cards 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 'escape-character'}

For example, the following query, using the backslash character as an escape character, finds identifier names that begin with an underbar.
stmt.executeQuery("SELECT name FROM Identifiers
WHERE Id LIKE '\_%' {escape '\'}");

• fn for scalar functions
Almost all DBMSs have numeric, string, time, date, system, and conversion functions on scalar values. One of these functions can be used by putting it in escape syntax with the keyword fn followed by the name of the desired function and its arguments. For example, the following code calls the function concat with two arguments to be concatenated:
{fn concat("Hot", "Java")};

The name of the current database user can be obtained with the following syntax:
{fn user()};

Scalar functions may be supported by different DBMSs with slightly different syntax, and they may not be supported by all drivers. Various DatabaseMetaData methods will 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.
The driver will either map the escaped function call into the appropriate syntax or implement the function directly itself. However, a driver is required to implement only those scalar functions that the DBMS supports.
• d, t, and ts for date and time literals
DBMSs differ 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 must translate to the DBMS representation. For example, a date is specified in a JDBC SQL statement with the following syntax:
{d 'yyyy-mm-dd'}

In this syntax, yyyy is the year, mm is the month, and dd is the day. The driver will replace the escape clause with the equivalent DBMS-specific representation. For example, the driver might replace {d 1999-02-28} with '28-FEB-99' if that is the appropriate format for the underlying database.
There are analogous escape clauses for TIME and TIMESTAMP:
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f . . .'}

The fractional seconds (.f . . .) portion of the TIMESTAMP can be omitted.
• call or ? = call for stored procedures
If a database supports stored procedures, they can be invoked from JDBC with the syntax shown below. Note that the square brackets ([ ]) indicate that what is between them is optional, and they are not part of the syntax.
{call procedure_name[(?, ?, . . .)]}
or, where a procedure returns a result parameter:
{? = call procedure_name[(?, ?, . . .)]}

Input arguments may be either literals or parameters. See the section "Numbering of Parameters" on page 103 for more information.
One can call the method DatabaseMetaData.supportsStoredProcedures to see if the database supports stored procedures.
• oj for outer joins
The syntax for an outer join is:
{oj outer-join}

In this syntax, outer-join has the form
table {LEFT|RIGHT|FULL} OUTER JOIN {table | outer-join}
ON search-condition

(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. ;"'PARKER', 'DOROTHY', 'USA', keyColumn";
Statement stmt = con.createStatement("SELECT * FROM {oj TABLE1 " +LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}");

Outer joins are an advanced feature and are not supported by all DBMSs; consult the SQL grammar for an explanation of them. JDBC provides three DatabaseMetaData methods for determining the kinds of outer joins a driver supports: supportsOuterJoins, supportsFullOuterJoins, and supportsLimitedOuterJoins.
The method Statement.setEscapeProcessing turns escape processing on or off, with the default being on. A programmer might turn it off to cut down on processing time when performance is paramount, but it would normally be turned on. It should be noted that the method setEscapeProcessing does not work for PreparedStatement objects because the statement may have already been sent to the database before it can be called. See page 89, the overview of the PreparedStatement interface, regarding precompilation.