![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
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 DBMSs 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 ManagementJDBC 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 transactions 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 ModesTwo transaction modes are usually supported by commercial DBMSs: the unchained mode and the ANSI-compatible chained mode. Check your DBMSs documentation to determine which is the default.
Be aware that stored procedures that use the unchained transaction mode may be incompatible with other chained mode transactions.
|
![]() |
|