Using SQL escape sequences

Download JDBC driver

The Microsoft JDBC Driver for SQL Server supports the use of SQL escape sequences, as defined by the JDBC API. Escape sequences are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently. When the JDBC driver processes the escaped part of an SQL string, it translates that part of the string into SQL code that SQL Server understands.

There are five types of escape sequences that the JDBC API requires, and all are supported by the JDBC driver:

  • LIKE wildcard literals
  • Function handling
  • Date and time literals
  • Stored procedure calls
  • Outer joins
  • Limit escape syntax

The escape sequence syntax used by the JDBC driver is the following:

{keyword ...parameters...}

Note

SQL escape processing is always turned on for the JDBC driver.

The following sections describe the five types of escape sequences and how they are supported by the JDBC driver.

LIKE wildcard literals

The JDBC driver supports the {escape 'escape character'} syntax for using LIKE clause wildcards as literals. For example, the following code will return values for col3, where the value of col2 literally begins with an underscore (and not its wildcard usage).

ResultSet rst = stmt.executeQuery("SELECT col3 FROM test1 WHERE col2   
LIKE '\\_%' {escape '\\'}");  

Note

The escape sequence must be at the end of the SQL statement. For multiple SQL statements in a command string, the escape sequence needs to be at the end of each relevant SQL statement.

Function handling

The JDBC driver supports function escape sequences in SQL statements with the following syntax:

{fn functionName}  

where functionName is a function supported by the JDBC driver. For example:

SELECT {fn UCASE(Name)} FROM Employee  

The following table lists the various functions that are supported by the JDBC driver when using a function escape sequence:

String Functions Numeric Functions Datetime Functions System Functions
ASCII

CHAR

CONCAT

DIFFERENCE

INSERT

LCASE

LEFT

LENGTH

LOCATE

LTRIM

REPEAT

REPLACE

RIGHT

RTRIM

SOUNDEX

SPACE

SUBSTRING

UCASE
ABS

ACOS

ASIN

ATAN

ATAN2

CEILING

COS

COT

DEGREES

EXP

FLOOR

LOG

LOG10

MOD

PI

POWER

RADIANS

RAND

ROUND

SIGN

SIN

SQRT

TAN

TRUNCATE
CURDATE

CURTIME

DAYNAME

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

EXTRACT

HOUR

MINUTE

MONTH

MONTHNAME

NOW

QUARTER

SECOND

TIMESTAMPADD

TIMESTAMPDIFF

WEEK

YEAR
DATABASE

IFNULL

USER

Note

If you try to use a function that the database does not support, an error will occur.

Date and time literals

The escape syntax for date, time, and timestamp literals is the following:

{literal-type 'value'}  

where literal-type is one of the following:

Literal Type Description Value Format
d Date yyyy-mm-dd
t Time hh:mm:ss [1]
ts TimeStamp yyyy-mm-dd hh:mm:ss[.f...]

For example:

UPDATE Orders SET OpenDate={d '2005-01-31'}
WHERE OrderID=1025  

Stored procedure calls

The JDBC driver supports the {? = call proc_name(?,...)} and {call proc_name(?,...)} escape syntax for stored procedure calls, depending on whether you need to process a return parameter.

A procedure is an executable object stored in the database. Generally, it is one or more SQL statements that have been precompiled. The escape sequence syntax for calling a stored procedure is the following:

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

where procedure-name specifies the name of a stored procedure and parameter specifies a stored procedure parameter.

For more information about using the call escape sequence with stored procedures, see Using Statements with Stored Procedures.

Outer joins

The JDBC driver supports the SQL92 left, right, and full outer join syntax. The escape sequence for outer joins is the following:

{oj outer-join}  

where outer-join is:

table-reference {LEFT | RIGHT | FULL} OUTER JOIN
{table-reference | outer-join} ON search-condition  

where table-reference is a table name and search-condition is the join condition you want to use for the tables.

For example:

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders.Status
   FROM {oj Customers LEFT OUTER JOIN
      Orders ON Customers.CustID=Orders.CustID}
   WHERE Orders.Status='OPEN'  

The following outer join escape sequences are supported by the JDBC driver:

  • Left outer joins
  • Right outer joins
  • Full outer joins
  • Nested outer joins

Limit escape syntax

Note

The LIMIT escape syntax is only supported by Microsoft JDBC Driver 4.2 (or higher) for SQL Server when using JDBC 4.1 or higher.

The escape syntax for LIMIT is as follows:

LIMIT <rows> [OFFSET <row offset>]  

The escape syntax has two parts: <rows> is mandatory and specifies the number of rows to return. OFFSET and <row offset> are optional and specify the number of rows to skip before beginning to return rows. The JDBC driver supports only the mandatory part by transforming the query to use TOP instead of LIMIT. SQL Server does not support the LIMIT clause. The JDBC driver does not support the optional <row offset> and the driver will throw an exception if it is used.

See also

Using statements with the JDBC driver