Jump to content
Sign in to follow this  
James McLean

Inserting dates using PreparedStatements

Recommended Posts

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})");


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!


James McLean

Edited by Guest

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.