• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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.
0
jarleberge
Asked:
jarleberge
  • 6
  • 5
  • 3
  • +4
6 Solutions
 
objectsCommented:
i'd suggest using a PrepaedStayement and not worry about the formatting
0
 
jarlebergeAuthor Commented:
This is a heavy loaded system which does a lot of SQL calls, where the preparedStatement cannot be reused, so the overhead by initializing it is to large. We are forced to use single statements.
0
 
objectsCommented:
then define the format to use as an application property and have different configurations accordingly.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
girionisCommented:
Why not write it as long and then you recreate the date by reading the long string from the database. By doing this you are guaranteed to have a uniform format in your dataabse for the dates.
0
 
girionisCommented:
>long string

should be

long number
0
 
jarlebergeAuthor Commented:
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.
0
 
girionisCommented:
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.
0
 
jarlebergeAuthor Commented:
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.
0
 
girionisCommented:
AFAIK there is no standrad JDBC date format, so go with objects' suggestion.
0
 
jarlebergeAuthor Commented:
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?
0
 
MogalManicCommented:
Oracle uses a statement cache so (I'm not sure about SQL server).  So prepared statements are, theoretically, faster than single statements.  For example if your app uses alot of get Employee by ID, then the following statement would be compiled by Oracle only once:
   select * from EMPLOYEES
   where EMP_ID=?

In my experience, I have not found getting the statement the big bottleneck in quering the database.  The biggest bottleneck is just executing the query and retrieving the results.  So to get the best bang for the buck, optomize the queries to use the indexes as much as possible.
0
 
girionisCommented:
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.
0
 
jarlebergeAuthor Commented:
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.
0
 
Giant2Commented:
>this date field syntax seems to be unstable

{d 'yyyy-mm-dd'} is a standard.

What do you mean with unstable?
The only problem I see migrating from SQL-Server to Oracle is the format (yyyy-mm-dd or mm-dd-yyyy) and this could be resolved using the particular DB settings (out of the java code).

Giant.
0
 
jarlebergeAuthor Commented:
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.
0
 
Giant2Commented:
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.
0
 
RuadRauFlessaCommented:
Why not just retrieve the date format and use it to convert your date to the appropriate format which the database is looking for.
0
 
grim_toasterCommented:
I would go with Objects's original answer and go with the PreparedStatement approach.  For several reasons, firstly your code will be simpler, as you will not have to parse the String to a date yourself in any database and region specific manner, but mainly for performance reasons.

Using direct SQL calls via Statement objects will cause significant performance impact on the database.  The reasoning for this is that passing the String directly for execution will prevent the use of bind variables, which are the single most important aspect of having a scalable database.  Each time a different query is passed to the database it will be hard-parsed (as it is a new SQL statement).  And if it truely is a heavy-loaded system, then hard-parsing every call to it will very soon fill up the database servers available memory with similar execution plans for each and every query that would only ever be used once (unless of course the dates are always the same).
0
 
Giant2Commented:
I thought to a split, because author seems not accept fully the objects answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now