db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kent Spaulding <k...@iotabits.com>
Subject timestamp and Derby/Oracle?
Date Mon, 02 Feb 2009 22:35:15 GMT
Hi all,

I'm pretty new to SQL, Derby, Oracle, etc.  I'm attempting to write  
some JDBC that seems like it should be simple.

I read fixed sources (Oracle DB and LDAP) and write to reporting  
tables. The reporting tables can be hosted by Derby or an Oracle DB  
(version 10/XE).

Derby seems to work just fine, so maybe this is more an Oracle  
question, but hopefully someone on the list can offer advice.

Each row in the reporting tables has a column that is a TIMESTAMP,  
capturing the time of the insertion.

That's the first problem - how do I create a java.sql.Timestamp object  
that I can then insert into both Derby (10.4.2.0) and Oracle (10, XE  
version)?

// assume INSERT_TIME is SQL type TIMESTAMP and FOO is type VARCHAR(100)
String foo = "foo value";
java.sql.Timestamp ts = new Timestamp(System.currentTimeMillis());

String insert = "INSERT into MY_TABLE (INSERT_TIME, FOO) values ( '" +  
ts.toString() + "', '" + foo.toString() + "')";
...

We get a statement like:

INSERT into MY_TABLE (INSERT_TIME,FOO ) VALUES ( '2009-02-02  
14:28:08.81', 'foo value')

Not a problem in Derby; but Oracle complains about the month not being  
valid.  I expected Oracle to use the timestamp.toString() as input -  
how odd that it doesn't.

Sure, I can switch based on the driver type and use Oracle's to_date  
function, but is there a simpler solution where I don't have to switch  
per driver type?

Thanks,
--Kent

Mime
View raw message