SQL Escape Sequences for JDBC

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

• Date, time, and timestamp literals
• Scalar functions such as numeric, string, and data type conversion functions
• Outer joins
• Procedure calls

The escape sequence used by JDBC is:

{extension}

The escape sequence is recognized and parsed by the BEA 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:

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

For example:

SELECT {fn UCASE(NAME)} FROM EMP

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 REPLACERIGHT RTRIM	ABS or ABSVAL ACOS ASIN ATAN ATANHATAN2 BIGINT CEILING or CEILCOS COSHCOT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND	DATE DAY DAYNAME DAYOFWEEK DAYOFYEAR DAYS HOUR JULIAN_DAY MICROSECOND MIDNIGHT_SECONDS MINUTE MONTH MONTHNAME QUARTER SECOND TIME TIMESTAMP TIMESTAMP_ISO TIMESTAMPDIFF WEEK YEAR	COALESCE DEREF DLCOMMENT DLLINKTYPE DLURLCOMPLETEDLURLPATH DLURLPATHONLYDLURLSCHEME DLURLSERVER DLVALUE EVENT_MON_STATEGENERATE_UNIQUENODENUMBERNULLIF PARTITION RAISE_ERROR TABLE_NAME TABLE_SCHEMA TRANSLATE TYPE_ID TYPE_NAME TYPE_SCHEMA VALUE
DB2(continued)	RTRIM (SYSFUN schema)SOUNDEX SPACE SUBSTR TRUNCATE or TRUNCUCASE or UPPERVARCHARVARGRAPHIC	REAL ROUND SIGN SIN SINHSMALLINT SQRT TAN TANHTRUNCATE
Informix	CONCATLEFTLENGTHLTRIMREPLACERTRIMSUBSTRING	ABSACOSASINATANATAN2COSCOTEXPFLOORLOGLOG10MODPIPOWERROUNDSINSQRTTANTRUNCATE	CURDATECURTIMEDAYOFMONTHDAYOFWEEKMONTHNOWTIMESTAMPADDTIMESTAMPDIFFYEAR	DATABASEUSER
Oracle	ASCIIBIT_LENGTHCHARCONCATINSERTLCASELEFTLENGTHLOCATELOCATE2LTRIMOCTET_LENGTHREPEATREPLACERIGHTRTRIMSOUNDEXSPACESUBSTRINGUCASE	ABSACOSASINATANATAN2CEILINGCOSCOTEXPFLOORLOGLOG10MODPIPOWERROUNDSIGNSINSQRTTANTRUNCATE	CURDATEDAYNAMEDAYOFMONTHDAYOFWEEKDAYOFYEARHOURMINUTEMONTHMONTHNAMENOWQUARTERSECONDWEEKYEAR	IFNULLUSER
SQL Server	ASCIICHARCONCATDIFFERENCEINSERTLCASELEFTLENGTHLOCATELTRIMREPEATREPLACERIGHTRTRIMSOUNDEXSPACESUBSTRINGUCASE	ABSACOSASINATANATAN2CEILINGCOSCOTDEGREESEXPFLOORLOGLOG10MODPIPOWERRADIANSRANDROUNDSIGNSINSQRTTANTRUNCATE	DAYNAMEDAYOFMONTHDAYOFWEEKDAYOFYEAREXTRACTHOURMINUTEMONTHMONTHNAMENOWQUARTERSECONDTIMESTAMPADDTIMESTAMPDIFFWEEKYEAR	DATABASEIFNULLUSER
Sybase	ASCIICHARCONCATDIFFERENCEINSERTLCASELEFTLENGTHLOCATELTRIMREPEATRIGHTRTRIMSOUNDEXSPACESUBSTRINGUCASE	ABSACOSASINATANATAN2CEILINGCOSCOTDEGREESEXPFLOORLOGLOG10MODPIPOWERRADIANSRANDROUNDSIGNSINSQRTTAN	DAYNAMEDAYOFMONTHDAYOFWEEKDAYOFYEARHOURMINUTEMONTHMONTHNAMENOWQUARTERSECONDTIMESTAMPADDTIMESTAMPDIFFWEEKYEAR	DATABASEIFNULLUSER

 

---

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

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

 

---

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.

Note: For DB2, a schema name cannot be used when calling a procedure. Also, literal parameter values are not supported.