Two Laptop Bag*
The moose likes JDBC and the fly likes Problems setting sql Date in prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problems setting sql Date in prepared statement" Watch "Problems setting sql Date in prepared statement" New topic
Author

Problems setting sql Date in prepared statement

David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
Hi Everyone,

I am really struggling and would appreciate some help please.

I have a string variable called nextLine[14] which holds a string with "23/10/2009" as a value. I am using a prepared statement to update the database with this value, the database is MySQL and the field is defined as a DATE field. No matter what I try I get an error. My latest attempt looked like this:

SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" ) ;
java.sql.Date convDate = sdf.parse(nextLine[12]);

pstmtInsert.setDate(13, convDate );

this refuses to compile at all, complaining it found a java.util.Date where it expected a java.sql.Date

other attempts that do compile, give me runtime errors, always the same as

java.lang.IllegalArgumentException
java.sql.Date.valueOf(Unknown Source)

I would really appreciate if someone can just point me to a working example of copying a string variable into the prepared statement that actually works (not an example please that uses current date/time but actually uses a string variable as the starting point).

Many, many thanks in advance.

Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874


David,

Try java.sql.Date.valueOf(nextLine[12]) and assign it to prepared statement setDate().
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
Hi thanks for the suggestion.

I changed the code to read, which is what I think you meant(?)

pstmtInsert.setDate(13, java.sql.Date.valueOf(nextLine[12]) );

this compiles fine, but I then get the runtime error of :

java.lang.IllegalArgumentException
java.sql.Date.valueOf(Unknown Source)
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874



what is the value of nextLine[12] , it should be in the format "yyyy-mm-dd"
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

What does SimpleDateFormat.parse() return? (Hint: not a java.sql.Date.)
Moojid Hamid
Ranch Hand

Joined: Mar 07, 2009
Posts: 120
is it nextLine[14] or nextLine[12]? You mention nextLine[14] in your discription but use nextLine[12] in your code.

Your string is in MM/dd/yyyy format, java.sql.Date.valueOf() expects string in yyyy-mm-dd format. Using your first approach, correct the format pattern to MM/dd/yyyy. Since the parse() returns a java.util.Date you can use the getTime() function to get the date as long and pass it to java.sql.Date constructor that takes a long.
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
The variable nextLine[12] contains 26/09/2008, this is read into the variable from a CSV file of which I cannot change the format.
Moojid Hamid
Ranch Hand

Joined: Mar 07, 2009
Posts: 120
David, please read my last post again, I am not suggesting to change the format in the CSV file. By "format pattern" I meant the String you pass to the constructor of java.lang.SimpleDateFormat
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
Hi Moojid

Sorry crossed lines, I have just changed the code to read as follows:

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
java.sql.Date convDate = sdf.parse(nextLine[12]);
pstmtInsert.setDate(13, java.sql.Date.valueOf(convDate));

with regards to the previous reference of using getTime(), sorry but I wasn't clear where this should go. The above code fails to compile complaining of the use of a java.util.Date where it expected a java.sql.Date, but then you probably knew it would do that. Sorry, if I appear to be a little dim, but this date thing has scrambled my brain.

Thanks for putting up with me

Moojid Hamid
Ranch Hand

Joined: Mar 07, 2009
Posts: 120
sdf.parse() returns java.util.Date, not java.sql.Date. You need to convert it to java.sql.Date. Like I told you before, once you have java.util.Date, call its getTime method and create a java.sql.Date using that value.
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
OK guys, now I have the following code:

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
java.util.Date convDate = sdf.parse(nextLine[12]);
java.sql.Date convSQLDate = new java.sql.Date(convDate.getTime());
pstmtInsert.setDate(13, convSQLDate);

This refuses to compile, complaining of unreported exception java.text.parseexception at sdf.parse(nextLine[12]

So I must have made another school boy error? But I can't see it.

David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

...

Are you declaring that the method throws the exception in question, or are you putting it in a try/catch block? You may want to move this over to one of the beginner/intermediate Java forums.
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
Sorry, I thought I had put this in the beginner forum, though I see that is not where it currently is, if anyone wants to tell me how to move it I will. I obviously have irritated at least one person with no patience, that was not my intention, I am only seeking a little genuine help with something that has had me pulling my hair out for two days.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

I don't think anybody's irritated, I just think that the thread is teetering between being JDBC-related and being Java-inexperience-related. That's just my opinion; no reason to be upset or defensive, as my opinion counts for very little.

Regardless, my response regarding how you're handling exceptions is valid.
David Cowan
Greenhorn

Joined: May 01, 2009
Posts: 17
I genuinely thought I had posted this into the beginner forum, as I didn't want to waste anyone's time and I am new-ish to Java, so I have assumed my problem is due to inexperience, though I have been programming for 30+ years now on many other platforms. Sorry if my last post sounded harsh, the last thing I want to do is cause offensive to anyone - please accept a virtual round of beer on me.

To answer the question on handling exceptions, I am not doing anything specific for trying to convert this date. The date conversion itself is sitting inside a larger try-catch construct but this try-catch is only handling exceptions related to the SQL or to a failure to get a database connection. The crazy thing to me, is this code did compile with no errors until I added the cast conversion from java.util.Date to java.sql.Date and yet this is not what it appears to be complaining about.

I am going to try to attach my source code to this post, to let people see the whole picture - just don't laugh at me please
Moojid Hamid
Ranch Hand

Joined: Mar 07, 2009
Posts: 120
look at http://java.sun.com/j2se/1.5.0/docs/api/java/text/DateFormat.html#parse(java.lang.String) and see what exception you need to handle. Instead of utilizing that large try/catch bloack it would be a good idea to handle it close to the code throwing the exception.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

That's not a cast, it's just a conversion; casting is something else.

SimpleDateFormat.parse() may throw an exception, so should be wrapped in a try/catch, specific to that exception. (Or use a utility method that deals with the exception.) As Moojid said it's almost always cleanest to handle exceptions near where they occur; depending on the code in question, of course.

(You may well have posted this in a beginner's forum; it may have been moved by someone since it started off being about JDBC :) My comment about moving it to a beginner's forum was more related to the exception handling concepts, not the JDBC part.)

And there's no reason to be embarrassed by code (well, that's not really true, but in *this* case it is)--it all starts somewhere, yanno?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38902
    
  23
I can't see that anybody on this thread has caused or taken offence. It has obviously been moved from beginner's to JDBC because of the mention of the java.sql.Date class.

You can't move threads; I can, but I shall leave it in the JDBC forum at least for now, since SQL dates and PreparedStatements are definitely JDBC related topics. And I think the subject matter is more difficult than the normal "beginner's" problem.

Can you cast that java.util.Date to a java.sql.Date? I don't think you can. I think it would be a java.util.Date full stop.

But if you look at the constructors for java.sql.Date you find one taking a long, and there is a java.util.Date method which returns a long; you might try taking that Date into a long and creating a java.sql.DateSee whether that works.

Beware:
  • I can perceive a performance overhead to the creation of two objects.
  • The parse() method declares a ParseException which is checked, and must therefore be handled. If you are unfamiliar with Java™ exception handling, have a look here.
  • It is likely there is an easier way to get the date into your statement, maybe with a Timestamp.


  • Anybody else able to help, please?
    David Cowan
    Greenhorn

    Joined: May 01, 2009
    Posts: 17
    Cheers guys,

    Once I added the exception handling for the ParseException, it finally worked. What a relief. Many thanks to you all for your time and trouble.

    I appreciate your comments Campbell on there may be an easier way, I will a bit more playing around to see what happens, but I am very happy to have anything working at the moment and I can live with a small performance hit on this task as the program will run infrequently.

    It does strike me as an afterthought that in all my years of coding this must be one of the best examples in a language of making what should be an easy task, hard. Whatever were the language designers thinking when they dreamed up this way of handling dates.

    Thanks again everyone
    Campbell Ritchie
    Sheriff

    Joined: Oct 13, 2005
    Posts: 38902
        
      23
    Many people (myself included) think the Date class was a disaster of design.

    And . . . you're welcome (and well done)
    David Newton
    Author
    Rancher

    Joined: Sep 29, 2008
    Posts: 12617

    Aye, date stuff is pretty horrid. IIRC that'll make it in to Java 7, though.

    In all fairness, that's a library issue, though, not a language issue. In the meantime, Joda Time is probably the canonical replacement.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Problems setting sql Date in prepared statement