JDBC and Dates
I have some SQL statements containing date references, using the syntax {d'2004-08-30'} for those fields (Earlier I used prepared statement to handle that thru ps.setDate, but now a single statement has that role). When swithing between JDBC drivers and databases with different language settings, this date field syntax seems to be unstable. How can I write a String based SQL containing dates which is generic and which will work for all drivers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then define the format to use as an application property and have different configurations accordingly.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>long string
should be
long number
should be
long number
ASKER
Not bad idea, but the database could be of both SQL Server and Oracle type, and my system is not the only which is using it, so db changes could be difficult.
Well, I am not talking about the database itself but about one specific table, since the change should affect one column in one table. You only need to make the change in one table. If other applications are also using this table then the other thing you can do is to add one more table that is somehow linked with the main table (the one that has the date in the format {d'2004-08-30'}). Then in the second table have two fields. A "date" field of type long and an "id" field that links to the first table.
Otherwise go with what objects suggested and define a date format for each configuration.
Otherwise go with what objects suggested and define a date format for each configuration.
ASKER
The dates are all around in the database, and changes to a commonly used database is excluded, so object's suggestion may be the best approach, except for if anyone can confirm that this is the only solution, and that the date formatting cannot be standardized in JDBC.
AFAIK there is no standrad JDBC date format, so go with objects' suggestion.
ASKER
What about this article...
http://www.csee.umbc.edu/help/oracle8/java.815/a64685/advanc4.htm
How can we conclude that there's no standard?
http://www.csee.umbc.edu/help/oracle8/java.815/a64685/advanc4.htm
How can we conclude that there's no standard?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jarleberge, is there any way to send a long to the database and let the database do the conversion on the defautl date format it's expecting? My db experience is limited so I am not sure if this can be done but I guess databases like Oracle or SQL Server should have something like that.
ASKER
Gironis, you can do it by accessing a parameter driven view which calls customised procedures / functions doing the convertion itself, but most "on-the-edge" technologies is for the moment pulling out database logics, even triggers and constraints, so that applications can be more platform independent. Thus, logics should be kept in the application logics.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With unstable I mean that the database may retrieve wrong data, which means the datefield must differ from database to database, even though I thought JDBC was standarized at {d'yyyy-mm-dd'}, independent of databases. What you say now is that it differs between SQL Server and Oracle?... then that could be the reason.
I did a migration from SQLServer2000 to Oracle 9ir2.
The problem I had with date was only the correct interpretation of them.
This was solved setting the Oracle to the same sintax of SQLServer.
The Oracle I had, saw the data like: mmm-dd-yyyy. Now it see the data: dd-mm-yyyy (like my old SQLServer).
I didn't change the java code (and do not use the PrepareStatement).
Just only set correctly the OracleDB.
The problem I had with date was only the correct interpretation of them.
This was solved setting the Oracle to the same sintax of SQLServer.
The Oracle I had, saw the data like: mmm-dd-yyyy. Now it see the data: dd-mm-yyyy (like my old SQLServer).
I didn't change the java code (and do not use the PrepareStatement).
Just only set correctly the OracleDB.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I thought to a split, because author seems not accept fully the objects answer.
ASKER