Jump to content

Inserting dates using PreparedStatements


This topic is 6137 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 by Guest
Link to comment
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.

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

This topic is 6137 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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