Inserting dates using PreparedStatements

[James McLean]

I am attempting to insert dates using a PreparedStatement. The PreparedStatement is being constructed by an Object Relational Mapper (Hibernate), whereupon I am attempting to set the value of a date field using the methods provided by PreparedStatement for setting query parameters. For example, if the PreparedStatement is obtained by the following code:

PreparedStatement smt = con.prepareStatement("insert into Test(testDate) values (?)");

Then, most JDBC drivers will allow you to do the following:

smt.setDate(1, new Date(new java.util.Date().getTime()));

Unfortunately, this approach doesn't work for inserts, although strangely it does work for updates.



The SequeLink driver does, however, permitted the following:

PreparedStatement smt = con.prepareStatement("insert into Test(testDate) values ({6/5/2004})");

smt.execute();

The problem with this is that I have no control over the creation of the PreparedStatement. This coupled with the fact that PreparedStatements are not mutable means that I cannot simply alter the parameterized SQL directly. I thought a logical next-step would be to try the following:

PreparedStatement smt = con.prepareStatement("insert into Test(testDate) values (?)");

smt.setString(1, "{10/10/1999}");

What is surprising is that this almost works, however, the value that ends up in the field is "10/10/2019".

I tried several other variations of this approach with no success. Does anyone have any suggestions?

Thanks in advance!

Sincerely,

James McLean

Edited May 19, 2007 by Guest

[Jesse Barnum]

We've run into this exact same problem, along with many others, with the JDBC driver that ships with FileMaker JDBC. We've written a replacement JDBC driver ( https://woof.dev.java.net ) that fixes this driver. It is free under the GPL license.

[mwarren2]

This is a very late reply because I've only just seen your post, but I've been through much of what you've done so I can maybe help somewhat.

I tried to go the Hibernate route, but as far as I could see Hibernate would only work if it had a Filemaker dialect, which at the moment doesn't exist.

Fine, I decided to try to write the Filemaker dialect, but even though the SequeLink driver in my experience has proved very good and is a finally a real Jdbc driver, there were a couple of good reasons why I couldn't finish the job, of which the most important is that access to the RecordId field is very limited via Jdbc in Filemaker.

So I gave up the Hibernate route and switched to using the Spring Jdbc classes, which take some of the pain out of writing Jdbc code and work very well with Sequelink, but don't offer any of the Hibernate opportunities.

I have tried your code and the first setDate you mention works perfectly for me although I would change the setDate line to (shouldn't make any difference):

smt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));

The setString() works fine as long as you do it this way yyyy-MM-dd and you remove the brackets:

smt.setString(1, "1999-10-10");

Hope this helps.