Using SQL escape sequences
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 a 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 a 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.