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.
The escape sequence for date, time, and timestamp literals is:
{literal-type 'value'}
where literal-type is one of the following:
UPDATE Orders SET OpenDate={d '1995-01-15'}
WHERE OrderID=1023
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.
SELECT id, name FROM emp WHERE name LIKE {fn UCASE('Smith')}
JDBC supports the SQL92 left, right, and full outer join syntax. The escape sequence for outer joins is:
{oj outer-join}
table-reference {LEFT | RIGHT | FULL} OUTER JOIN
{table-reference | outer-join} ON search-condition
table-reference is a database table name.
search-condition is the join condition you want to use for the tables.
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.
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 '*'}
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]...)]}
procedure-name specifies the name of a stored procedure.
parameter specifies a stored procedure parameter.
Note: | 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. |