Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click Here!
Click Here!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


For Time/Date

JDBC supports ISO standard formats for date, time, and timestamp. They must be escaped as shown here to be interpreted as expected:

{d ‘yyyy-mm-dd’}      to specify a date
{t ‘hh:mm:ss’}        to specify a time literal
{ts ‘yyyy-mm-dd hh:mm:ss.f...’} or {ts ‘yyyy-mm-dd hh:mm:ss’}      to specify a timestamp
{fn function(args, ...)}      for scalar functions

Scalar functions and their arguments must be escaped and preceded by the “fn” keyword. In JDBC, the following scalar functions, if supported by the driver, are translated into the DBMS’s specific syntax for these functions:

System Functions

{fn database()}
{fn user()}

Numeric Functions

{fn abs(number)}
{fn acos(float)}
{fn asin(float)}
{fn atan(float)}
{fn atan2(float1, float2)}
{fn ceiling(number)}
{fn cos(float)}
{fn cot(float)}
{fn degrees(number)}
{fn exp(float)}
{fn floor(number)}
{fn log(float)}
{fn log10(float)}
{fn mod(int1, int2)}
{fn pi()}
{fn power(number, power)}
{fn radians(number)}
{fn rand(int)}
{fn round(number, places)}
{fn sign(number)}
{fn sin(float)}
{fn sqrt(float)}
{fn tan(float)}
{fn trauncate(number, places)}

String Functions

{fn ascii(string)}
{fn char(code)}
{fn concat(str1, str2)}
{fn difference(str1, str2)}
{fn insert(str1, start, len, str2)}
{fn lcase(string)}
{fn left(string, count)}
{fn length(string)}
{fn locate(str1, str2, start)}
{fn ltrim(string)}
{fn repeat(string, count)}
{fn replace(str1, str2, str3)}
{fn right(string, count)}
{fn rtrim(string)}
{fn soundex(string)}
{fn space(count)}
{fn substring(string, start, len)}
{fn ucase(string)}

Date and Time Functions

{fn curdate()}
{fn curtime()}
{fn dayname(date)}
{fn dayofmonth(date)}
{fn dayofweek(date)}
{fn dayofyear(date)}
{fn hour(time)}
{fn minute(time)}
{fn month(date)}
{fn monthname(date)}
{fn now()}
{fn quarter(date)}
{fn second(time)}
{fn timestampadd(interval, count, timestamp)}
{fn timestampdiff(interval, tstp1, tstp2)}
{fn week(date)}
{fn year(date)}

Other Functions

{fn ifnull(expr, value)}
{fn convert(value, type)}   type may be any SQL datatype

For Characters That Have a Special Meaning

Special characters used for character matching, such as “%” and “_” in LIKE clauses, must be escaped with an escape character to be interpreted literally. This escape character must be declared as:

{escape ‘escapechar’}

This declaration must be included at the end of any SQL text where these characters have to be interpreted literally. In the example that follows, the qualification clause matches any value of field_n that begins with an underscore character:

SELECT * FROM table WHERE field_n LIKE ‘\_%’ {escape ‘\’}

For Outer Joins

Grammar for outer joins is database-dependent. The JDBC escape syntax for outer joins is:

{oj outerjoin}

where outerjoin respects:

table LEFT OUTER JOIN {table | outerjoin} ON searchcondition

For example, the next query may be used to list all employees and their pending messages (0 or more). Even employees who do not have message entries will be returned by the query:

SELECT employees.name, emp_messages.message
FROM {oj employees LEFT OUTER JOIN emp_messages
ON employees.emp_id = emp_messages.emp_id}

This SQL string would translate to this (using a Sybase System 11):

SELECT employees.name, emp_messages.message
FROM employees, emp_messages
WHERE employees.emp_id *= emp_messages.emp_id

Other DBMSs would translate the code into their specific dialect. Remember that the Connection.nativeSQL (String anySqlString) may be used to discover the translation of all escaped syntaxes for your own DBMS.

Transaction Management

JDBC supports database transaction management. Transactions provide a way to group SQL statements so they are treated as a whole — either all statements in the group are executed or no statements are executed. All statements within a transaction are treated as a work unit. Transactions are thus useful to guarantee, among other things, data consistency.

Completing a transaction is called committing the transaction, while aborting it is called rolling back the transaction. A rollback undoes the whole transaction. A transaction’s boundaries are the beginning of its block and the commit or rollback. Once a commit has been issued, the transaction cannot be rolled back. Note that some DBMSs support nested transactions as well as intermediate markers within a transaction to indicate a point to which it can be rolled back.

Transaction Modes

Two transaction modes are usually supported by commercial DBMSs: the unchained mode and the ANSI-compatible chained mode. Check your DBMS’s documentation to determine which is the default.

  The unchained mode requires explicit statements to identify the beginning of a transaction block and its end, which will always be a commit or rollback statement. The transaction block may be composed of any SQL statements.
  The chained mode does not require explicit statements to delimit the transaction statements because it implicitly begins a transaction before any SQL statement that retrieves or modifies data. The transaction must still be explicitly ended with a transaction commit or rollback.

Be aware that stored procedures that use the unchained transaction mode may be incompatible with other chained mode transactions.


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.