Using SQL Escape Sequences
The Microsoft SQL Server JDBC Driver 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
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