Type 4 JDBC Drivers

SQL Escape Sequences for JDBC

Language features, such as outer joins and scalar function calls, are commonly implemented by database systems. The syntax for these features is often database-specific, even when a standard syntax has been defined. JDBC defines escape sequences that contain the standard syntax for the following language features:

The escape sequence used by JDBC is:

   {extension}

The escape sequence is recognized and parsed by the Type 4 JDBC drivers, which replace the escape sequences with data store-specific grammar.

Date, Time, and Timestamp Escape Sequences

The escape sequence for date, time, and timestamp literals is:

   {literal-type 'value'}

where literal-type is one of the following:

Example:

   UPDATE Orders SET OpenDate={d '1995-01-15'} 
   WHERE OrderID=1023

Scalar Functions

You can use scalar functions in SQL statements with the following syntax:

   {fn scalar-function}

where scalar-function is a scalar function supported by the Type 4 JDBC drivers, as listed in Table C-2.

Example:

   SELECT id, name FROM emp WHERE name LIKE {fn UCASE('Smith')}

Outer Join Escape Sequences

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

   {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 database table name.

   search-condition is the join condition you want to use for the tables.

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'

Table C-3 lists the outer join escape sequences supported by Type 4 JDBC drivers for each data store.

LIKE Escape Character Sequence for Wildcards

You can specify the character to be used to escape wildcard characters (% and _, for example) in LIKE clauses. The escape sequence for escape characters is:

{escape 'escape-character'}

where escape-character is the character used to escape the wildcard character.

For example. the following SQL statement specifies that an asterisk (*) be used as the escape character in the LIKE clause for the wildcard character %:

SELECT col1 FROM table1 WHERE col1 LIKE '*%%' {escape '*'}

Procedure Call Escape Sequences

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

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

where:

   procedure-name specifies the name of a stored procedure.

   parameter specifies a stored procedure parameter.