Setter method for Date entry

[francan00]

For PreparedStatement Oracle insert on a date field, what is the
difference between using
setTimestamp and setString.


The reason I ask is because I can use this if not formating my date
entry:
setTimestamp(1, sub_date)

Or use this if formatting my date entry:
setString(1, fmt.format(sub_date))

 

[Arne Vajhøj]

'setTimestamp()' takes a 'java.sql.Timestamp' argument, known already to
be type compatible with a SQL TIMESTAMP column. The correct matchup for
a SQL DATE column is 'java.sql.Date'. If 'setString()' works, it's
because the DBMS accepts string arguments for a DATE column and converts
the string according to its rules. Those rules will not match Java's.

What a given DBMS calls its data types is subject to variation; one has
to know the local-to-SQL data type mapping. I think Oracle calls its
TIMESTAMP type "TIMESTAMP" but I don't feel like looking it up this minute.

You can use DATE if can live without fractions of seconds and
timezones and TIMESTAMP if you want fractions of seconds and/or timezones.

Both DATE and TIMESTAMP should be fine with java.sql.Timestamp and
PreparedStatement.setTimestamp.

Arne

 

[Arne Vajhøj]

For PreparedStatement Oracle insert on a date field, what is the
difference between using
setTimestamp and setString.

The reason I ask is because I can use this if not formating my date
entry:
setTimestamp(1, sub_date)

Or use this if formatting my date entry:
setString(1, fmt.format(sub_date))

The last one takes a binary date and converts it to a string
and then convert it back to a binary date.

That does not make sense to me.

Arne

 

[Arne Vajhøj]

The SQL DATE type also doesn't hold hours or minutes.

The Oracle one does.

Oracle:


Oracle deviates from the standard regarding DATE.
Yep.


Actually, you shouldn't count on that, really. Of course, hacks are
possible since java.util.Date and its offspring are one part of the Java
API that didn't come out quite right. Stick with java.sql.Timestamp to
match TIMESTAMP and java.sql.Date to match DATE.

Only if you can live with loosing hhmmss information stored in
the DATE field.

It does not sound very attractive to me.

Arne

 

[Arne Vajhøj]

This group puzzles me. On the one hand, there is a strong vocal
contingent stressing the importance of being portable between
databases. On the other hand, there is this advice to use the SQL DATE
type in a non-portable manner.

Which is better, to use DATE as the standard calls for it to be used,
holding only date information, or to take advantage of a DBMS-specific
extension to hold time information in violation of the standard and
portability?

Oracle is not following the standard.

Before 9i then DATE was the *only* option, because TIMESTAMP did
not exist.

If you have an existing table with DATE type, then it can very
likely contain hhmmss info.

I don't think it will be good practice to ignore that in a Java
app using that table just because the SQL standard type does not
contain hhmmss.

If you use java.sql.Timestamp, then the problem is that fractions of
seconds are lost when saving from Java to Oracle.

I consider that a much smaller problem.

If it is a new table, then it is easy just use TIMESTAMP and
no problem.

BTW a quick googling find that there are also some JDBC driver
issues to consider:

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#08_01

Portability is great. But the reality in SQL dialects is problems.

Arne