StoredProcedure outputs wrong Date (time is always 00:00:00.0)

[racumin]

Hi,

I'm using Spring 2.5.6, ojdbc-14 and Java 6.

I have a StoredProcedure that has a date output.

This is my code:



public GetVersionElementSP(DataSource ds) {
super(ds, STORED_PROC_NAME);
declareParameter(new SqlOutParameter("P_OUT_TIMESTAMP", Types.TIMESTAMP));
compile();
}




public void execute() throws Exception {
Map<Object, Object> inParams = new HashMap<Object, Object>();
Map<?, ?> outParams = execute(inParams);
Timestamp rawDate = (Timestamp) outParams.get("P_OUT_TIMESTAMP");

log.info(outParams.get("P_OUT_TIMESTAMP"));
log.info(rawDate);


The output is:
2011-04-05 00:00:00.0

But if I check in the database, the timestamp should be:
2011-04-05 09:04:19.0

If I use JdbcTemplate.queryForObject(), the output is correct (2011-04-05 09:04:19.0)

I also tried Types.DATE, OracleTypes.TIMESTAMP, OracleTypes.DATE as a parameter for my SqlOutParameter but the output is still wrong. I also tried to cast the outParams.get("P_OUT_TIMESTAMP") to java.util.Date and java.sql.Date

Is there anything I missed or done wrong?

Thanks!

[dr_pompeii]

Hello

I am not an expert about StoredProdures

but according with this



If I use JdbcTemplate.queryForObject(), the output is correct

I think because the template are making the correct conversions about the Date types like you did mention below



I also tried Types.DATE, OracleTypes.TIMESTAMP, OracleTypes.DATE as a parameter for my SqlOutParameter but the output is still wrong. I also tried to cast the outParams.get("P_OUT_TIMESTAMP") to java.util.Date and java.sql.Date


Is there anything I missed or done wrong?
Perhaps in your StoredProdure you must handle or give a format to this Date (something like DateFormat (http://download.oracle.com/javase/6/docs/api/java/text/DateFormat.html)) to show the hours, seems since you didnt this by default are showing the hours in blank or with 00

BTW could you post the table and such field?

[racumin]

Perhaps in your StoredProdure you must handle or give a format to this Date (something like DateFormat) to show the hours, seems since you didnt this by default are showing the hours in blank or with 00

I'll try to use DateFormat and see what happens.



BTW could you post the table and such field?


I have a table with this column:

COLUMN NAME: TIME_STAMP
DATA TYPE: 91
TYPE NAME: DATE
COLUMN SIZE: 7

I'm using Oracle by the way.

[racumin]

After some research, I stumbled upon this site (http://www.thunderguy.com/semicolon/2003/08/14/java-sql-date-is-not-a-real-date/) and learned that if I use java.sql.Date, the time (hours, minutes, seconds) will be dropped (set to 0)


java.sql.Date stores only date information, not times. Simply converting a java.util.Date into a java.sql.Date will silently set the time to midnight. So, to store date/times to be manipulated as java.util.Date objects, don’t do this:

It was also stated in the javadocs (http://download.oracle.com/javase/6/docs/api/index.html)


public class Date
extends Date
A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT.

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Instead of using java.sql.Date, I should use java.sql.Timestamp (since it is accurate up to the milliseconds). The problem is, even if I use Timestamp, the output of the StoredProcedure is still wrong (time is set to 00:00:00).

Now, my questions are:

(1) Since the data type of the column that I am retrieving in the database is of type DATE, does Spring StoredProcedure automatically convert it to java.sql.Date even though I specified in my code that java.sql.Types.TIMESTAMP should be its return value? Please see code below:


declareParameter(new SqlOutParameter("P_OUT_TIMESTAMP", Types.TIMESTAMP));

(2) Why does the StoredProcedure return wrong (time was 'normalized') Date but JdbcTemplate.queryForObject returns the accurate Date?

(3) Is that considered a bug in the StoredProcedure?

I really need help on this one. Thanks!

PS: I checked the oracle stored procedure and I'm sure that there is nothing wrong with it. We tried it using Oracle Client (in C) and it produces the accurate date.

[dr_pompeii]

Hello



Instead of using java.sql.Date, I should use java.sql.Timestamp (since it is accurate up to the milliseconds).

It has more sense, I recall these wonderful problems before learn Hibernate years ago, why so complicated JDBC and such days, thanks to Spring for JdbcTemplate :)


The problem is, even if I use Timestamp, the output of the StoredProcedure is still wrong (time is set to 00:00:00).
I am not an expert about StoredProcedure

Alfa:
but perhaps you must work with DateFormat prior to insert the value and post when you get the field

About your questions
(1) Really I dont know
(2) Because JdbcTemplate is created to avoid these headaches about JDBC burden steps
(3) I dont think so, seems you need more burden steps, recall is classic JDBC


PS: I checked the oracle stored procedure and I'm sure that there is nothing wrong with it.
I think is correct, but seems that some work around with Date/Time objects is mandatory


We tried it using Oracle Client (in C) and it produces the accurate date.
Perhaps such tool are handling internally or under the hood these convertions, all is from Oracle

Let me know your advance about my Alfa Suggestion

[racumin]

but perhaps you must work with DateFormat prior to insert the value and post when you get the field

My application does not insert the DATE value. It was generated inside the database and all I need to do is to retrieve it.

By the way, what do you mean by "work with DateFormat prior to post when you get the field"?

Thanks!

edited: My problem is solved already. The mistake was that the column of the table was of type DATE but the stored procedure returns a VARCHAR. We made some adjustments to the stored proc and it is now working as it should be.

Sorry for the troubles :)