1

Consider the following DDL I am using to create an H2 database table in one of my JUnit test cases :

CREATE TABLE "CONFIG_INFO" 
   (    "ID" VARCHAR2(12 BYTE), 
        "RUN_DATE" DATE, 
   );

The class that I am writing a unit test for tries to insert a record in this table. I can see that the following query is executed for inserting a record :

insert into CONFIG_INFO(ID,RUN_DATE) values (?,?)

However, the insertion fails with the following exception :

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into CONFIG_INFO(ID,RUN_DATE) values (?,?)]; Cannot parse "DATE" constant "31-Jan-20";

I looked around and found that this issue usually occurs when you have a time component as part of the date; however, debugged the code and I can see that the Java code is passing "31-Jan-20" with no time stamp component as can also be seen from the error message above.

How can I resolve this error?

Ping
  • 587
  • 5
  • 27
  • 1
    Why do you believe that H2 would accept the text value `31-Jan-20` as a `DATE` literal? Where did you read that H2 supports that date format? --- I recommend you **read the H2 manual** and learn what date formats are actually supported by H2. --- Alternatively, don't give the date value to the database as a `String`, given it as a `java.sql.Date` object. That way, parsing the date string is something you need to do in your Java code, where you have full control over the parsing. – Andreas Apr 13 '20 at 07:59
  • Does this answer your question? [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2) – Ole V.V. Apr 13 '20 at 08:04
  • 1
    Don’t transfer dates as strings to or from your database. Transfer `LocalDate` objects. In general too, don’t handle dates as strings. Use `LocalDate`. – Ole V.V. Apr 13 '20 at 08:06
  • @Andreas Thanks for the response. I did not read about H2 date formats. I made an assumption that this would work out of the box which I see now was an incorrect assumption. That said, I mentioned in my question that I am writing a Junit test where I get this error. In the real production code, we use Oracle and this works fine. If I don't want to change my production code to accommodate H2 date formats, can I do something at the DDL level for the H2 table? – Ping Apr 13 '20 at 08:12
  • @OleV.V. The linked question helps; however, I would want to avoid changing my Java code to accommodate H2 date formats. This works fine with Oracle which is the database I use in production. Is there something I can do at the DDL level when creating the H2 database table itself? – Ping Apr 13 '20 at 08:15
  • @Ping *"we use Oracle and this works fine"* Yes of course, because Oracle supports that date format. To make your code run on both databases without date format issues, do what I already suggested: **Parse the string in Java** to a `java.sql.Date` object, and give that to the JDBCTemplate instead of the string value. You should have done that to begin with, so you're not dependent on the configured date format in the database, because it *can* be changed, and then even you Oracle access would fail. – Andreas Apr 13 '20 at 08:15
  • @Andreas Yes I do understand that Oracle supports this date format and that is why this works fine. Sicne my entire Java code base is written and tested using Oralce as the databse (yes the Java code is database dependent I can only see that now), can I do this without changing my Java code? Something while creating the H2 database table itself? – Ping Apr 13 '20 at 08:17
  • 2
    *"can I do this without changing my Java code?"* Yes, use Oracle for your testing code too. That way you're actually doing a true test, since databases are different. Just because your SQL works for H2, wouldn't necessarily mean it works for Oracle, so using H2 makes the test flawed. – Andreas Apr 13 '20 at 08:18
  • @Andreas I changed the DDL such that the `RUN_DATE` field is created as a`VARCHAR2` in H2 rather than a date. One might argue that this is not the "right" or "ideal" solution; however, in my particular case, my objective is not to accommodate H2 database. My objective is to test the Java code and the SQL queries that it is executing on Oracle. If my underlying database does change in the future and it cannot support this date format, I will refactor my Java code to use 'setObject' instead of 'setString'. YMMV - This is a personal preference and an idealistic solution is not always required. – Ping Apr 13 '20 at 09:25
  • 1
    @Ping Then we can just hope that there isn't a query somewhere in the code that tries to sort by the date field, because that would not return the records in the wrong order, or that there is some code that actually expects the column to be a date object when returned in a query. At least it's only the tests that will fail, not the production code. – Andreas Apr 13 '20 at 11:11

2 Answers2

2

java.time

The best solution is to use smart objects rather than dumb strings to exchange values with your database. Specifically, use the modern java.time classes.

Your inputs use a month name in all-uppercase. This does not conform with localization for English, at least not in US or UK English. So we must use DateTimeFormatterBuilder to get a DateTimeFormatter that tolerates all uppercase.

DateTimeFormatterBuilder builder = new DateTimeFormatterBuilder().parseCaseInsensitive().appendPattern( "dd-MMM-uu" ) ;
DateTimeFormatter formatter = builder.toFormatter() ;

Parse your input string.

String input = "31-JAN-20" ;
LocalDate localDate = LocalDate.parse( input , formatter ) ;

Trap for DateTimeParseException in case of faulty inputs.

Pass to the database using a JDBC driver compliant with JDBC 4.2 or later.

myPreparedStatement.setObject( … , LocalDate ) ;

Retrieve.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

Tip: Educate the publisher of your data about the ISO 8601 standard formats for exchanging date-time values as text.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • It’s the good answer. It should work with both Oracle, H2 and many other vendors of database engines/RDBMSs. – Ole V.V. Apr 13 '20 at 16:23
1

You'll need to use the H2 function PARSEDATETIME() to format the date in the format that the database expects.

In the second answer here you'll see an example that uses almost your exact format.

Convert special String into Date in H2

Or reformat your data into the ISO standard which in your case should be yyyy-MM-dd

Joe W
  • 2,773
  • 15
  • 35
  • This would have made sense if I was inserting data using SQL. As mentioned in my question, the insert is being done by Java code.. – Ping Apr 13 '20 at 08:17
  • 2
    Post the java you're using for the insert. – Joe W Apr 13 '20 at 08:22