Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Using SQL Escape Sequences

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 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...}

System_CAPS_noteNote

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.

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 '\\'}");
System_CAPS_noteNote

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.

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

System_CAPS_noteNote

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

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

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.

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

System_CAPS_noteNote

The LIMIT escape syntax is only supported by Microsoft JDBC Driver 4.2 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.

Show:
© 2015 Microsoft