Solved

JDBC and Dates

Posted on 2004-08-30
22
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +4
22 Comments
 
LVL 92

Accepted Solution

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

Author Comment

by:jarleberge
ID: 11929330
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
ID: 11929342
then define the format to use as an application property and have different configurations accordingly.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 35

Assisted Solution

by:girionis
girionis earned 25 total points
ID: 11929370
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
ID: 11929371
>long string

should be

long number
0
 

Author Comment

by:jarleberge
ID: 11929429
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
ID: 11929444
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
ID: 11929470
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
ID: 11929535
AFAIK there is no standrad JDBC date format, so go with objects' suggestion.
0
 

Author Comment

by:jarleberge
ID: 11929556
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
ID: 11929599
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
ID: 11929623
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
ID: 11929749
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
ID: 11930016
>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
ID: 11930035
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
ID: 11930065
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
ID: 11931131
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
ID: 11940225
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
ID: 12276702
I thought to a split, because author seems not accept fully the objects answer.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This video teaches viewers about errors in exception handling.
Suggested Courses

738 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