aspose file tools*
The moose likes JDBC and the fly likes Oracle Timestamps and java. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle Timestamps and java." Watch "Oracle Timestamps and java." New topic
Author

Oracle Timestamps and java.

Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
Ok guys bascialyl what I am doing is this. I get a date/time from a log file and it is in the format of 2002-11-12 14:09:22.0 Well I do a prepared statement and then inside the db it looks like this...
11/22/2002 2:09:22PM SO when I do a select I have to use 2002-11-12 14:09:22.0 and it does nto return any results. How can I insert it into the db the correct date format.
I tried in my preparedstatement this:
insert into mcserver (create_date, hostname, line_number, type, duration) values (to_date(? , 'yyyy-mm-dd hh24:mm:ss'), ?, ?, ?, ?)";
and then for my timestamp top set the first field:
preparedstatement.setTimestamp(1, mcserverpk.createDate);
Well doign it that way I get the error:
ORA-01810: format code appears twice
I just want to get it to work.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Anthony, if create_date is a date/time type then use:
"insert into mcserver (create_date, hostname, line_number, type, duration) values ( ? , ?, ?, ?, ? )";
and
preparedstatement.setTimestamp(1, mcserverpk.createDate);
You don't have to do any conversion to date, that's what the setTimestamp() method does.
Jamie
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
Maybe I am missing something but the error message states the problem, doesn't it?
Within your SQL string you specified the to_date() function and you gave the format. Then you call setTimestamp and you give the format again.
Comment out the call to setTimestamp and try it.


Normal is in the eye of the beholder
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
If I coment it out, how do I associate the date with the prepared statement. Also Jamie in your post I do that and it works, but I cannot do a select on the date using the format that the log file has. Oracle somehow transforms the date.
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
Since the data comes from a file, I think the to_date() approach is more simple. However, setTimestamp is more flexible.
While on the topic, is there an easy way to convert the date from the file to a Timestamp?
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
Originally posted by Anthony Smith:
If I coment it out, how do I associate the date with the prepared statement.

You simply use setString.
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
Ok, maybe I am not getting enough information. I get the time already as a timestamp and it is in the form of 2002-11-12 14:09:22.0
So I have to use setTimestamp...
It never gets changed but the database still says. 11/22/2002 2:09:22PM
When I do a select I do a setTimestamp but I will only be able to search with the 2002-11-12 14:09:22.0 format.
I do not understand how it is changing
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
Just to be clear, what is the .0 at the end of the date string?
Also, in the format mask from your original post you use 'mm' for the minutes when it should be 'mi'. That's the reason, Aha
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
The .0 is just millisecs.
Even when doing mi I get...
Like I said, I get the value in a timestamp so I have to use setTimestamp.
So even changing to mi I sitll will get
ORA-01810: format code appears twice
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Anthony, I think you are getting a little confused, and it can be very confusing! The main principle that I think you may be missing is that a date does not have a format. Different languages and databases have their own internal(efficient) way to deal with what a date is. In java, it is a long value. There is no format built in to the date value. Usually, each vendor will have some sort of default way to display the internal number, but they are never the same!! What you need to do is format the date the way you want it, when you want to view it.
So use the setTimestamp() method to insert dates, then when you want to view it you can use a SimpleDateFormat to convert it to any display form you like:
eg.

[ September 23, 2003: Message edited by: Jamie Robertson ]
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
I see hwta you are saying but my probem is that I have to use the date in my select statement. My date format does nto match what is in the db. Becasue I am given a timestamp I have to use setTimestamp. My timestamp is the same as what I wanted to enter in the db but it never stores as such.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Anthony Smith:
I see hwta you are saying but my probem is that I have to use the date in my select statement. My date format does nto match what is in the db. Becasue I am given a timestamp I have to use setTimestamp. My timestamp is the same as what I wanted to enter in the db but it never stores as such.

Anthony, it is never stored in any format at the DB end. That's my point. If you want to override Oracle's default formatting use the to_char ( date, "< format that you want to see it >" ) in your select statement and use the ResultSet.getString() method to retrieve the value from the database.
Remember you can always specify what format a date is displayed rather than relying on the default formats( both in java and in Oracle ).
What is the other scenario you are having problems with( I think you have the insert working ). If you post the details, maybe we can walk through the specific problem and through the specific problem you can see the solution.
Example:
-have a String timestamp in a log_file and other data
-need to search the create_date ( timestamp field ) for matches on the log_file date
Jamie
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
Ok, here is the code:
preparedstatement = connection.prepareStatement("select create_date, hostname, line_number from mcserver where create_date = ? AND hostname = ? AND line_number = ?");
preparedstatement.setTimestamp(1, mcserverpk.createDate);
How do I implement the to_char method?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I see hwta you are saying but my probem is that I have to use the date in my select statement.
there are a few ways to tackle this problem:
1 - take your Date value from the database and convert it to a character field ( to_char( create_date, "< log file format >" in the same format as the log file, then compare Strings

or
convert the log file String date to a java date/timestamp and compare:

there are other variations, but that should help
Jamie
[ September 23, 2003: Message edited by: Jamie Robertson ]
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
I think I see what you are syang but it is already a Timestamp. I never recive it as a string. How can I reformat the timestamp?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Anthony Smith:
I think I see what you are syang but it is already a Timestamp. I never recive it as a string. How can I reformat the timestamp?

if it refers to the log file timestamp, then just delete the conversion. This is what you need!

Jamie
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

now a much slower comparison using String can be done as well, which is what I think you're getting at, so here it is:
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Anthony Smith:
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.

Alright, so the real issue is that you are doing a select using timestamps, and your not getting any results. So you check to see if it is there, but on the select, it shows up in Oracle's default format. The problem here is not that they are in different formats, but that the timestamps are not equal. I don't know if this is a rounding/precision error when the log file date value was written to the file or on the insert to the database, but the values are different, if only by nanoseconds ( nanos not being set on your timestamp --> see the note about this here ). So your best bet might be to convert to Strings ( see previous post ) at a lesser precision to accomodate this discrepancy...
other than that I'm all out of ideas
[ September 23, 2003: Message edited by: Jamie Robertson ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Anthony Smith:
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.

I thought you were still stuck on getting the query to execute, not the fact that it was running but with no results...little mix up there.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle Timestamps and java.