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 WebLogic 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:

Table C-1 Literal Types for Date, Time, and Timestamp Escape Sequences
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...]

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 WebLogic Type 4 JDBC drivers, as listed in Table C-2.

Example: 

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

Table C-2 Scalar Functions Supported
Data Store String
Functions 		Numeric
Functions 		Timedate
Functions 		System
Functions
DB2 ASCII BLOB CHAR CHR CLOB CONCAT DBCLOB DIFFERENCE GRAPHIC HEX INSERT LCASE or LOWER LCASE
(SYSFUN schema) LEFT LENGTH LOCATE LONG_VARCHAR LONG_VARGRAPHIC LTRIM LTRIM
(SYSFUN schema) POSSTR REPEAT REPLACE RIGHT RTRIM RTRIM
(SYSFUN schema) 		ABS or ABSVAL ACOS ASIN ATAN ATANH ATAN2 BIGINT CEILING
or CEIL COS COSH COT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL 		CURDATE CURTIME DATE DAY DAYNAME DAYOFWEEK DAYOFYEAR DAYS HOUR JULIAN_DAY MICROSECOND MIDNIGHT_SECONDS MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIME TIMESTAMP TIMESTAMP_ISO TIMESTAMPDIFF WEEK YEAR COALESCE DEREF DLCOMMENT DLLINKTYPE DLURLCOMPLETE DLURLPATH DLURLPATHONLY DLURLSCHEME DLURLSERVER DLVALUE EVENT_MON_STATE GENERATE_UNIQUE NODENUMBER NULLIF PARTITION RAISE_ERROR TABLE_NAME TABLE_SCHEMA TRANSLATE TYPE_ID TYPE_NAME TYPE_SCHEMA VALUE
DB2
(continued) 		SOUNDEX SPACE SUBSTR TRUNCATE or TRUNC UCASE or UPPER VARCHAR VARGRAPHIC ROUND SIGN SIN SINH SMALLINT SQRT TAN TANH TRUNCATE
Informix CONCAT LEFT LENGTH LTRIM REPLACE RTRIM SUBSTRING ABS ACOS ASIN ATAN ATAN2 COS COT EXP FLOOR LOG LOG10 MOD PI POWER ROUND SIN SQRT TAN TRUNCATE CURDATE CURTIME DAYOFMONTH DAYOFWEEK MONTH NOW TIMESTAMPADD TIMESTAMPDIFF YEAR DATABASE USER
Oracle ASCII BIT_LENGTH CHAR CONCAT INSERT LCASE LEFT LENGTH LOCATE LOCATE2 LTRIM OCTET_LENGTH REPEAT REPLACE RIGHT RTRIM SOUNDEX SPACE SUBSTRING UCASE ABS ACOS ASIN ATAN ATAN2 CEILING COS COT EXP FLOOR LOG LOG10 MOD PI POWER ROUND SIGN SIN SQRT TAN TRUNCATE CURDATE DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND WEEK YEAR IFNULL USER
SQL Server 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 DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIMESTAMPADD TIMESTAMPDIFF WEEK YEAR DATABASE IFNULL USER
Sybase ASCII CHAR CONCAT DIFFERENCE INSERT LCASE LEFT LENGTH LOCATE LTRIM REPEAT 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
DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIMESTAMPADD TIMESTAMPDIFF WEEK YEAR DATABASE IFNULL USER

 

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 WebLogic Type 4 JDBC drivers for each data store.

Table C-3 Outer Join Escape Sequences Supported
Data Store Outer Join Escape Sequences
DB2 Left outer joins
Right outer joins
Nested outer joins
Informix Left outer joins
Right outer joins
Nested outer joins
Oracle Left outer joins
Right outer joins
Nested outer joins
SQL Server Left outer joins
Right outer joins
Full outer joins
Nested outer joins
Sybase Left outer joins
Right outer joins
Nested outer joins

 

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.

For DB2 for Linux/UNIX/Windows, a catalog name cannot be used when calling a stored procedure. Also, for DB2 v8.1 and v8.2 for Linux/UNIX/Windows, literal parameter values are supported for stored procedures. Other supported DB2 versions do not support literal parameter values for stored procedures.