The SQL Escape Syntax

To be JDBC-compliant, a database driver must support both SQL-2 entry level and semantics for some parts of the ANSI SQL-2 transitional level. Because the syntax used for this level is often different across DBMSs, JDBC provides an escape syntax for these semantics. The JDBC drivers convert the escape syntax into a DBMS-specific syntax, enabling portability of programs that require these features.

The escape syntax is the same as the escape syntax of Open Database Connectivity (ODBC). However, it may not be the same as the ANSI one. Its form is as follows:

{keyword parameters}

For stored procedures

The following escape syntax is adopted to call stored procedures. The ?= may be dropped when the stored procedure doesn’t return a result. The procedure parameters can be IN and/or OUT or simple literals.

{[?=] call stored_procedure_name [param1[, param2 ...]]}

For example, a stored procedure that returns a value and takes two parameters would be called as shown here:

{?= call proc_purge_employees employees, emp_messages}

XREF 

Examples of Java programs calling stored procedures are provided in Chapter 8.

For the time and date

JDBC supports ISO standard formats for date, time, and timestamp. They must be escaped as shown here to be interpreted as expected:

{d ‘yyyy-mm-dd’}: To specify a date

{t ‘hh:mm:ss’}: To specify a time literal

{ts ‘yyyy-mm-dd hh:mm:ss.f...’} or {ts ‘yyyy-mm-dd hh:mm:ss’}: To specify a timestamp

For scalar functions

Scalar functions and their arguments must be escaped and preceded by the fn keyword. In JDBC, the following scalar functions, if supported by the driver, are translated into the DBMS’s specific syntax for these functions:

{fn function(args, ...)}: For scalar functions

System functions

{fn database()}: The database name

{fn user()}: The user’s name

Numeric functions

{fn abs(number)}

{fn acos(float)}

{fn asin(float)}

{fn atan(float)}

{fn atan2(float1, float2)}

{fn ceiling(number)}

{fn cos(float)}

{fn cot(float)}

{fn degrees(number)}

{fn exp(float)}

{fn floor(number)}

{fn log(float)}

{fn log10(float)}

{fn mod(int1, int2)}

{fn pi()}

{fn power(number, power)}

{fn radians(number)}

{fn rand(int)}

{fn round(number, places)}

{fn sign(number)}

{fn sin(float)}

{fn sqrt(float)}

{fn tan(float)}

{fn truncate(number, places)}

String functions

{fn ascii(string)}

{fn char(code)}

{fn concat(str1, str2)}

{fn difference(str1, str2)}

{fn insert(str1, start, len, str2)}

{fn lcase(string)}

{fn left(string, count)}

{fn length(string)}

{fn locate(str1, str2, start)}

{fn ltrim(string)}

{fn repeat(string, count)}

{fn replace(str1, str2, str3)}

{fn right(string, count)}

{fn rtrim(string)}

{fn soundex(string)}

{fn space(count)}

{fn substring(string, start, len)}

{fn ucase(string)}

Date and time functions

{fn curdate()}

{fn curtime()}

{fn dayname(date)}

{fn dayofmonth(date)}

{fn dayofweek(date)}

{fn dayofyear(date)}

{fn hour(time)}

{fn minute(time)}

{fn month(date)}

{fn monthname(date)}

{fn now()}

{fn quarter(date)}

{fn second(time)}

{fn timestampadd(interval, count, timestamp)}

{fn timestampdiff(interval, tstp1, tstp2)}

{fn week(date)}

{fn year(date)}

Other functions

{fn ifnull(expr, value)}

{fn convert(value, type)}: type can be any SQL datatype

For characters that have a special meaning

Special characters used for character matching, such as % and _ in LIKE clauses, must be escaped with an escape character to be interpreted literally. This escape character must be declared as

{escape ‘escapechar’}

This declaration must be included at the end of any SQL text where these characters have to be interpreted literally. In the example that follows, the qualification clause matches any value of field_n that begins with an underscore character:

SELECT * FROM table WHERE field_n LIKE ‘\_%’ {escape ‘\’} 

For outer joins

Grammar for outer joins is database-dependent, but JDBC provides a portable way of using them. The JDBC escape syntax for outer joins is

{oj outerjoin}

where outerjoin includes the following:

table LEFT OUTER JOIN {table | outerjoin} ON searchcondition

For example, the following query can be used to list all employees and their pending messages (0 or more). Even employees who don’t have message entries will be returned by the query:

SELECT employees.name, emp_messages.message FROM {oj employees LEFT OUTER JOIN emp_messages ON employees.emp_id = emp_messages.emp_id}

For Sybase XI, this SQL statement would translate to the following query:

SELECT employees.name, emp_messages.message FROM employees, emp_messages WHERE employees.emp_id *= emp_messages.emp_id

Other DBMSs would translate the code into their specific dialect.

Note 

Remember that the Connection.nativeSQL (String anySqlString) can be used to discover the translation of all escaped syntaxes for your own DBMS.