#458 Java 8 Time API - PreparedStatement setObject

[Daniel Novo]

Hi, i'm trying use the new java time api (LocalDate, LocalTime, ...), using the latest version of jt400.jar.

However, when executing statement setObject as follow

stmt.setObject(++columns, LocalDate.now());

I'm getting this exception

Thread[main,5,main]  Ter jan 18 10:46:56:852 GMT 2022  as400: PreparedStatementImpl STMT0001 (1588970020) : setObject().
Thread[main,5,main]  Ter jan 18 10:46:56:852 GMT 2022  as400: PreparedStatementImpl STMT0001 (1588970020) : parameter index: 2 type: java.time.LocalDate toString():2022-01-18.
Thread[main,5,main]  Ter jan 18 10:46:56:853 GMT 2022  as400: Unknown com.ibm.as400.access.SQLDate@3c679bde (1013423070) : Unable to assign object(2022-01-18) of class(class java.time.LocalDate).
Thread[main,5,main]  Ter jan 18 10:46:56:853 GMT 2022  as400: Unknown com.ibm.as400.access.SQLDate@3c679bde (1013423070) : Throwing exception, sqlState: 07006 reason: Data type mismatch. vendor code -99999.java.sql.SQLException: Data type mismatch.
    at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:891)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:436)
    at com.ibm.as400.access.SQLDate.set(SQLDate.java:452)
    at com.ibm.as400.access.AS400JDBCPreparedStatementImpl.setValue(AS400JDBCPreparedStatementImpl.java:3655)
    at com.ibm.as400.access.AS400JDBCPreparedStatementImpl.setObject(AS400JDBCPreparedStatementImpl.java:3124)
    at com.umanxpert.jdbc.LocalDateTester.main(LocalDateTester.java:26)

Is the new java time api supported? If not, are there any workarounds?

Thanks in advance

[John Eberhard]

LocalDate is not supported. It looks like it can be converted to a String, then the string can be used.

LocalTime is not supported. The problem is that the local time could have a minisecond component which would not be supported. You can try converting LocalTime to a String, but that would probably only work if the result looks like HH:MM:SS.

[Daniel Novo]

Sorry to insist.
Is jt400 targetting jdbc api specification 4.2?
If so, are you planning to add support for java.time.LocalDate, java.timeLocalTime, java.time.LocalDateTime?

[John Eberhard]

I do see those time changes in JDBC 4.2. I'm not sure how they were missed. There will be the assumption that milliseconds will be lost when converting from LocalTime to SQLTIME. The timezone ones may not be possible.

From JDBC 4.2 spec at https://download.oracle.com/otndocs/jcp/jdbc-4_2-mrel2-spec/

Additional Mappings to Table B-4, Mapping from Java Object to JDBC Types
Added support to map java.time.LocalDate to JDBC DATE.
Added support to map java.time.LocalTime to JDBC TIME
Added support to map java.time.LocalDateTime to JDBC TIMESTAMP.
Added support to map java.time.LocalOffsetTime to JDBC
TIME_WITH_TIMEZONE.
Added support to map java.time.LocalOffsetDateTime to JDBC
TIMESTAMP_WITH_TIMEZONE.

• Additional Mappings to Table B-5, Performed by setObject and setNull between
  Java Object Types and Target JDBC Types
  Allow conversion of java.time.LocalDate to CHAR, VARCHAR,
  LONGVARCHAR, and DATE.
  Allow conversion of java.time.LocalTime to CHAR, VARCHAR,
  LONGVARCHAR, and TIME.
  Allow conversion of java.time.LocalTime to CHAR, VARCHAR,
  LONGVARCHAR, and TIMESTAMP.
  Allow conversion of java.time.OffsetTime to CHAR, VARCHAR,
  LONGVARCHAR, and TIME_WITH_TIMESTAMP.
  Allow conversion of java.time.OffsetDateTime to CHAR, VARCHAR,
  LONGVARCHAR, TIME_WITH_TIMESTAMP and
  TIMESTAMP_WITH_TIMESTAMP.

I'll see if we can find someone to work on the changes.

[Daniel Novo]

Thank you for your help

[Daniel Novo]

Hi John, do you have an ideia when will be released a version with this changes?