The Microsoft SQL Server 2005 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:

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 UPPER(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

DBNAME

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

See Also