Persist a LocalDateTime into a DB2 TIMESTAMP column

Question

I've been trying to persist a LocalDateTime into a TIMESTAMP column in DB2 10.5 but fails with the error:

[jcc][1091][10824][4.26.14] Invalid data conversion: Parameter instance 2020-01-02T12:34:56 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

I'm using the IBM JDBC driver: "JDBC driver IBM Data Server Driver for JDBC and SQLJ 4.26.14" that implements JDBC spec 4.1.

The table definition is:

create table t1 (
  id int,
  a timestamp
);

And the Java code is:

LocalDateTime dt = LocalDateTime.of(2020, 1, 2, 12, 34, 56);
PreparedStatement ps = conn.prepareStatement(
  "insert into t1 (id, a) values (?, ?)"
);
int id = 1;
ps.setInt(1, id);
ps.setObject(2, dt); // fails here
ps.execute();

I can see it doesn't even get to the ps.execute() method. I've read that a JDBC 4.2 driver may fix this, but I can't find it anywhere.

user_10418264 · Answer

According to the Data types that map to database data types in Java applications topic, you should use java.sql.Timestamp working with TIMESTAMP table column.

Table 1. Mappings of Java data types to database server data types for updating database tables

Java data type      Database data type
------------------  ------------------
...
java.sql.Timestamp  TIMESTAMP, ...

user_5221149 · Answer

Change code to use setTimestamp(...), using Timestamp.valueOf(LocalDateTime dateTime):

ps.setTimestamp(2, Timestamp.valueOf(dt));

If dt can be null, use ternary operator:

ps.setTimestamp(2, (dt == null ? null : Timestamp.valueOf(dt)));

Persist a LocalDateTime into a DB2 TIMESTAMP column

2 Answers