wood burning stoves*
The moose likes JDBC and the fly likes how to insert timestamp into derby databse Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to insert timestamp into derby databse" Watch "how to insert timestamp into derby databse" New topic
Author

how to insert timestamp into derby databse

John Smith
Greenhorn

Joined: May 11, 2007
Posts: 13
I need to update a derby timestamp field with a java.sql.timestamp, but am getting this error when doing so

SQLException e = java.sql.SQLSyntaxErrorException: Syntax error: Encountered "00" at line 1, column 43.
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "00" at line 1, column 43.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)
at DBServer.conversation.run(conversation.java:263)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "00" at line 1, column 43.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
at org.apache.derby.client.am.Statement.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeUpdateX(Unknown Source)
... 3 more

I know the reason is due to the format of my timestamp being
0000-00-00 00:00:00

When it hits the 00:.. is when the error is thrown.


Here is the code I'm attempting to run

Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

Brandon,
You really should use a PreparedStatement rather than fiddling with date formats. This makes your query safer and more portable.

The query would then look like:
"UPDATE PATRONS SET LOGINTIME = ? and NUM_LOGINS = ? where PATRONID = ?"

And you would pass the three parameters via stmt.setXXX(). There is a setDate() that will take a Java date value so you won't have this problem.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to insert timestamp into derby databse
 
Similar Threads
Issue with Date insertion
sql date
Apache Derby Sql
import data from large csv file. Help.
Statement stmt SQL syntax error