Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JDBC and Dates

Posted on 2004-08-30
22
Medium Priority
?
472 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 100 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
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 35

Assisted Solution

by:girionis
girionis earned 100 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 100 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 100 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 100 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 100 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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…
The viewer will learn how to implement Singleton Design Pattern in Java.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

722 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