11 SQL Extensions

Overview

Certain SQL features beyond SQL-2 Entry Level are widely supported and are desirable to include as part of our JDBC compliance definition so that applications can depend on the portability of these features. However, SQL-2 Transitional Level, the next higher level of SQL compliance defined by ANSI, is not widely supported. Where Transitional Level semantics are supported, the syntax is often different across DBMSs.

We therefore define two kinds of extensions to SQL-2 Entry Level that must be supported by a JDBC-Compliant driver:

  • Selective Transitional Level syntax and semantics must be supported. We currently demand just one such feature: the DROP TABLE command is required for JDBC compliance.

  • Selective Transitional Level semantics must be supported through an escape syntax that a driver can easily scan for and translate into DBMS-specific syntax. We discuss these escapes in the remainder of Section 11. Note that these escapes need only be supported where the underyling database supports the corresponding Transitional Level semantics.

An ODBC driver that supports ODBC Core SQL as defined by Microsoft complies with JDBC SQL as defined in this section.

SQL Escape Syntax

JDBC supports the same DBMS-independent escape syntax as ODBC for stored procedures, scalar functions, dates, times, and outer joins. A driver maps this escape syntax into DBMS- specific syntax, allowing portability of application programs that require these features. The DBMS-independent syntax is based on an escape clause demarcated by curly braces and a keyword: 

{keyword ... parameters ...}

This ODBC-compatible escape syntax is in general not the same as has been adopted by ANSI in SQL-2 Transitional Level for the same functionality. In cases where all of the desired DBMSs support the standard SQL-2 syntax, the user is encouraged to use that syntax instead of these escapes. When enough DBMSs support the more advanced SQL-2 syntax and semantics these escapes should no longer be necessary.

Stored Procedures

The syntax for invoking a stored procedure in JDBC is: 

{call procedure_name[(argument1, argument2, ...)]}

or, where a procedure returns a result parameter: 

{?= call procedure_name[(argument1, argument2, ...)]}

Input arguments may be either literals or parameters. To determine if stored procedures are supported, call DatabaseMetaData.supportsStoredProcedure.

Time and Date Literals

DBMSs differ in the syntax they use for date, time, and timestamp literals. JDBC supports ISO standard format for the syntax of these literals, using an escape clause that the driver must translate to the DBMS representation.

For example, a date is specified in a JDBC SQL statement with the syntax 

{d 'yyyy-mm-dd'}

where yyyy-mm-dd provides the year, month, and date, e.g. 1996-02-28. The driver will replace this escape clause with the equivalent DBMS-specific representation, e.g. 'Feb 28, 1996' for Oracle.

There are analogous escape clauses for TIME and TIMESTAMP: 

{t 'hh:mm:ss'}




{ts 'yyyy-mm-dd hh:mm:ss.f...'}









The fractional seconds (.f...) portion of the TIMESTAMP can be omitted.











    Scalar Functions


JDBC supports numeric, string, time, date, system, and conversion functions on scalar values. 
These functions are indicated by the keyword "fn" followed by the name of the desired function 
and its arguments. For example, two strings can be concatenated using the concat function






{fn concat("Hot", "Java")}















The name of the current user can be obtained through the syntax






{fn user()}









See the X/Open CLI or ODBC specifications for specifications of the semantics of the scalar 
functions. The functions supported are listed here for reference. Some drivers may not support 
all of these functions; to find out which functions are supported, use the folowing DatabaseMe
tadata methods: getNumericFunctions() returns a comma separated list of the names of 
the numeric functions supported, getStringFunctions() does the same for the string functions, 
and so on.





The numeric functions are ABS(number), ACOS(float), ASIN(float), ATAN(float), 
ATAN2(float1, float2), CEILING(number), COS(float), COT(float), DEGREES(number), 
EXP(float), FLOOR(number), LOG(float), LOG10(float), MOD(integer1, integer2), PI(), 
POWER(number, power), RADIANS(number), RAND(integer), ROUND(number, places), 
SIGN(number), SIN(float), SQRT(float), TAN(float), and TRUNCATE(number, places).





The string functions are ASCII(string), CHAR(code), CONCAT(string1, string2), DIFFERENCE(string1,
string2), INSERT(string1, start, length, string2), LCASE(string), LEFT(string, 
count), LENGTH(string), LOCATE(string1, string2, start), LTRIM(string), REPEAT(string, 
count), REPLACE(string1, string2, string3), RIGHT(string, count), RTRIM(string), SOUNDEX(string),
SPACE(count), SUBSTRING(string, start, length), and UCASE(string). 





The time and date functions are CURDATE(), CURTIME(), DAYNAME(date), DAYOFMONTH(date),
DAYHOFWEEK(date), DAYOFYEAR(date), HOUR(time), MINUTE(time), 
MONTH(time), MONTHNAME(date), NOW(), QUARTER(date), SECOND(time), TIMESTAMPADD(interval,
count, timestamp), TIMESTAMPDIFF(interval, timestamp1, 
timpestamp2), WEEK(date), and YEAR(date).





The system functions are DATABASE(), IFNULL(expression, value), and USER().





There is also a CONVERT(value, SQLtype) expression, where type may be BIGINT, BINARY,
BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY, 
LONGVARCHAR, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, 
and VARCHAR.





Again, these functions are supported by DBMSs with slightly different syntax, and the driver's 
job is either to  map these into the appropriate syntax or to implement the function directly in 
the driver.











    LIKE Escape Characters


The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more 
characters, or exactly one character, respectively). In order to interpret them literally, they can 
be preceded with a special escape character in strings, e.g. "\". In order to specify the escape 
character used to quote these characters, include the following syntax on the end of the query:






{escape 'escape-character'}









For example, the query






SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}









finds identifier names that begin with an underbar.











    Outer Joins


The syntax for an outer join is






{oj outer-join}









where outer-join is of the form






table LEFT OUTER JOIN {table | outer-join} ON search-condition









See the SQL grammar for an explanation of outer joins. Three boolean DatabaseMetaData 
methods are provided to determine the kinds of outer joins supported by a driver.









 

Contents  | Prev  | Next








jdbc@dbname.eng.sun.com
or
jdbc-odbc@dbname.eng.sun.com