JDBC Date conversion
Dear Tod!
We downloaded your web application framework. This is the best
framework I've ever seen. It's really great.
We've tested JATO 1.2 and 1.2.1 on JRun 3.1. It works fine.
We encountered some problems during the test of QueryModelBase. We
are using Oracle 8.1.6EE with the Oracle JDBC driver. We have added a
new field to your CustomerModel in the JATO tutorial as follows:
FIELD_SCHEMA.addFieldDescriptor(
new QueryFieldDescriptor(
FIELD_BORNDATE,
"BornDate",
"ndnwCustomers.BornDate",
java.sql.Date.class));
The insert statement generated by the getInsertSQL() method looks
like this:
INSERT INTO ndnwCustomers
(CustomerID,CompanyName,ContactName,Phone,BornDate)
VALUES ('GGY1024','Capsys Ltd.','Geiszter Gyorgy', '288-7025' , 2002-
04-05)
We could not run the generated query because we got the following
SQLException:
java.sql.SQLException: ORA-00932: inconsistent datatypes
or
java.sql.SQLException: ORA-01861: literal does not match format string
if date string is also quoted ('2002-04-05').
The JDBC driver seems not to set the JDBC default date format for the
current database session (yyyy-mm-dd : see JDBC API doc). It's a
little bit suprising! We've found the following workarrounds for
this problem:
1. Explicit date conversion with SQL function call:
to_date(`2002-04-05', `YYYY-MM-DD')
2. Set the default date format explicitly for the current session by
executing the following query:
Connection con = ....;
Statement stmt = con.createStatement();
stmt.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
Neither of this solutions are database vendor independent and neither
of this solution are currently supported by JATO!
3. Set the default date format at database level. However I'm
affraid fixing the date format at db level in an eventually
multilingual enviroment can cause unwanted side effects at other
database clients. I think the JDBC driver should work independently
on database locale settings. Am I wrong?
Do you have any better idea?
Thanks
George Geiszter
