Oracle Timestamps and java.

[Anthony Smith]

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]

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]

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.

[Anthony Smith]

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]

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]

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]

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]

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]

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]

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.

<font size="2">PreparedStatment ps = conn.prepareStatement( "select create_date FROM mcserver where hostname = 'somehostname' ");
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd hh24:mm:ss" );
SimpleDateFormat sdf2 = new SimpleDateFormat( "yyyy/MM/dd" );  
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
   java.sql.Timestamp ts = rs.getTimestamp( 1 );
   System.out.println( sdf.format( ts ) );
   System.out.println( sdf2.format( ts ) );  //same date, different viewing format
}
...
</font>

[ September 23, 2003: Message edited by: Jamie Robertson ]

[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.

[Jamie Robertson]

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]

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]

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

<font size="2">
String logFileDate = "11/22/2002 2:09:22PM"
PreparedStatement ps = con.prepareStatement("SELECT * from mcserver where to_char( create_date, 'MM/DD/YYYY HH:MI:SSAM' ) = ? " );
ps.setString( 1, logFileDate );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
    ...
}
...
</font>

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

<font size="2">String logFileDate = "11/22/2002 2:09:22PM";
SimpleDateFormat parser = new SimpleDateFormat( "MM/dd/yyyy HH:mm:ssaa" );
java.util.Date logdate = parser.parse( logFileDate );
java.sql.Timestamp logtimestamp = new java.sql.Timestamp( logdate.getTime() );
PreparedStatement ps = con.prepareStatement("SELECT * from mcserver where  create_date = ? " );
ps.setTimestamp( 1, logtimestamp );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
    ...
}
 </font>

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

[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?

[Jamie Robertson]

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!

//took out conversion code
PreparedStatement ps = con.prepareStatement("SELECT * from mcserver where  create_date = ? " );
ps.setTimestamp( 1, logtimestamp );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
    ...
}

Jamie

[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.

[Jamie Robertson]

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:

SimpleDateFormat formatter = new SimpleDateFormat( "MM/dd/yyyy HH:mm:ssaa" );
String stringLogDate = formatter.format( logFileTimestamp );
PreparedStatement ps = con.prepareStatement("SELECT * from mcserver where  to_char( create_date, 'MM/DD/YYYY HH:MI:SSAM' ) = ? " );
ps.setString( 1, stringLogDate );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
    ...
}

[Jamie Robertson]

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]

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.