| Contents | Prev | Next | JDBCTM Guide: Getting Started |
We therefore define two kinds of extensions to SQL-2 Entry Level that must be supported by a JDBC-Compliant(TM) driver:
{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.
{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.
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.
{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.
{escape 'escape-character'}
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underbar.
{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.