java 8 date support in TaskExecution breaks DB2 support

[Cuball0]

The implementation of this commit : a0fda4b

Breaks DB2 LUW support.

return new TaskExecution(id, getNullableExitCode(rs), rs.getString("TASK_NAME"),
					rs.getTimestamp("START_TIME"), rs.getTimestamp("END_TIME"), rs.getString("EXIT_MESSAGE"),

The code above was changed into the code below: (rs.getTimestamp("field") -> rs.getObject("field",LocalDateTime.class)

return new TaskExecution(id, getNullableExitCode(rs), rs.getString("TASK_NAME"),
					rs.getObject("START_TIME", LocalDateTime.class), rs.getObject("END_TIME", LocalDateTime.class),

The latter however throws and exception when the column has a is null value (which is the case for START_TIME and END_TIME at the start of a task excecution. Because of this, it's impossible to run spring-cloud tasks anymore that use DB2 as a database.

Simple test to reproduce the problem with DB2 driver

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;


public class DB2Test {

    public static void main(String[] args) {
        // Replace with your actual database connection details
        String dbUrl = "jdbc:db2://server.name:50012/DB";
        String dbUsername = "user";
        String dbPassword = "pass";

        var tableName = "TEST";

        String drop = "DROP TABLE IF EXISTS " + tableName;
        String create = "CREATE TABLE " + tableName + "  ( ID BIGINT, YOUR_TIMESTAMP_COLUMN TIMESTAMP(9) )";
        String insert1 = "INSERT INTO " + tableName + "( ID, YOUR_TIMESTAMP_COLUMN) VALUES ( 0,  current_timestamp )";
        String insert2 = "INSERT INTO " + tableName + "( ID ) VALUES ( 1 )";

        String query = "SELECT YOUR_TIMESTAMP_COLUMN FROM " + tableName;


        try (Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        ) {
            PreparedStatement dropStatement = connection.prepareStatement(drop);
            PreparedStatement createStatement = connection.prepareStatement(create);
            PreparedStatement insertStatement1 = connection.prepareStatement(insert1);
            PreparedStatement insertStatement2 = connection.prepareStatement(insert2);
            PreparedStatement queryStatement = connection.prepareStatement(query);


            dropStatement.executeUpdate();
            createStatement.executeUpdate();
            insertStatement1.executeUpdate();
            insertStatement2.executeUpdate();

            var resultSet = queryStatement.executeQuery();


            while (resultSet.next()) {

                System.out.println("------ ROW " + resultSet.getRow() + " -------");
                //via
                try {
                    LocalDateTime localDateTime = resultSet.getObject("YOUR_TIMESTAMP_COLUMN", LocalDateTime.class);
                    System.out.println("Successful retrieved LocalDateTime : " + localDateTime);
                } catch (Exception e) {
                    System.out.println("Unable to retrieve YOUR_TIMESTAMP_COLUMN via getObject(YOUR_TIMESTAMP_COLUMN, LocalDateTime.class)");
                    System.out.println("Error message: " + e.getMessage());
                }


                try {
                    var yourTimestampColumn = resultSet.getTimestamp("YOUR_TIMESTAMP_COLUMN");

                    java.util.Date dateFromTimestamp = (yourTimestampColumn != null) ? (java.util.Date) yourTimestampColumn.clone() : null;

                    System.out.println("Successful retrieved Date from Timestamp : " + dateFromTimestamp);
                } catch (Exception e) {
                    System.out.println("Unable to retrieve YOUR_TIMESTAMP_COLUMN via getTimestamp(YOUR_TIMESTAMP_COLUMN)");
                    System.out.println("Error message: " + e.getMessage());

                }

            }

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error executing query: " + e.getMessage());
        }
    }
}

Output :

------ RECORD -------1
Successful retrieved LocalDateTime : 2023-12-22T08:16:50.674910
Successful retrieved Date from Timestamp : 2023-12-22 08:16:50.67491
------ RECORD -------2
Unable to retrieve YOUR_TIMESTAMP_COLUMN via getObject(YOUR_TIMESTAMP_COLUMN, LocalDateTime.class)
Error message: Cannot invoke "java.sql.Timestamp.toLocalDateTime()" because "" is null
Successful retrieved Date from Timestamp : null

[cppwfs]

Hello,
I created a sample task from start.spring.io that used Spring Cloud Task on a DB2 (ibmcom/db2:latest) instance but was unable to reproduce . Can you provide a sample project the reproduces the problem?

[Cuball0]

Hello,

I'm not sure what's the best way to create a test to reproduce it.
I'll try to give a bit more context when and how the problem occurs:

We register our Task in SCDF server (this application is also using DB2). The Task is registered as boot3 application, and thus using the BOOT3_ schema tables.
Then the task is launched via the SCDF (via Inteface / REST / CLI)
We are using Kubernetes as Target platform the run the Task (should not really matter I guess)
By launching from SCDF a new record is inserted in the table BOOT3_TASK_EXECUTION by the server before running our task (this record does not yet have a value in the START_TIME collumn)
The id of this new record is passed as argument to the Sping Cloud Task application (--spring.cloud.task.executionid=XXX')
When the Task is started it's trying to load the record from the database associated with this executionId
(org.springframework.cloud.task.repository.dao.JdbcTaskExecutionDao.getTaskExecution)

This is where it fails, the getTaskExcetion throws an exception in the mapRow function.

Caused by: java.lang.NullPointerException: Cannot invoke "java.sql.Timestamp.toLocalDateTime()" because "<local4>" is null
at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:2020) ~[jcc-11.5.9.0.jar:na]
at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:2045) ~[jcc-11.5.9.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~[HikariCP-5.0.1.jar:na]
at org.springframework.cloud.task.repository.dao.JdbcTaskExecutionDao$TaskExecutionRowMapper.mapRow(JdbcTaskExecutionDao.java:621) ~[spring-cloud-task-core-3.1.1.jar:3.1.1]

[cppwfs]

Thank you for the additional notes. They were helpful!

[cppwfs]

@Cuball0 Thanks for reporting the issue!