Solved

JDBC and Dates

Posted on 2004-08-30
22
462 Views
Last Modified: 2012-05-05
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
Comment
Question by:jarleberge
  • 6
  • 5
  • 3
  • +4
22 Comments
 
LVL 92

Accepted Solution

by:
objects earned 25 total points
Comment Utility
i'd suggest using a PrepaedStayement and not worry about the formatting
0
 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 92

Expert Comment

by:objects
Comment Utility
then define the format to use as an application property and have different configurations accordingly.
0
 
LVL 35

Assisted Solution

by:girionis
girionis earned 25 total points
Comment Utility
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
 
LVL 35

Expert Comment

by:girionis
Comment Utility
>long string

should be

long number
0
 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 35

Expert Comment

by:girionis
Comment Utility
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
 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 35

Expert Comment

by:girionis
Comment Utility
AFAIK there is no standrad JDBC date format, so go with objects' suggestion.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 25 total points
Comment Utility
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
 
LVL 35

Expert Comment

by:girionis
Comment Utility
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
 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 12

Assisted Solution

by:Giant2
Giant2 earned 25 total points
Comment Utility
>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
 

Author Comment

by:jarleberge
Comment Utility
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
 
LVL 12

Expert Comment

by:Giant2
Comment Utility
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
 
LVL 3

Assisted Solution

by:RuadRauFlessa
RuadRauFlessa earned 25 total points
Comment Utility
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
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 25 total points
Comment Utility
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
 
LVL 12

Expert Comment

by:Giant2
Comment Utility
I thought to a split, because author seems not accept fully the objects answer.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now