HSQL problem when using TIMESTAMP value as IN parameter in stored procedure

Question

Overview HSQL IN parameter of type TIMESTAMP doesn't work as expected for HSQL stored procedure.

Given the following DDL :

CREATE TABLE TS_STORE (
    ID_COL VARCHAR(20) NOT NULL PRIMARY KEY,
    TS TIMESTAMP
);

A DML statement such as :

INSERT INTO TS_STORE (ID_COL, TS) VALUES ('key1', '2020-02-19 12:17:53');

will successfully insert a row.

Then when I attempt to create a stored procedure to do the same as:

CREATE PROCEDURE TEST_PROC(IN IN_KEY VARCHAR(20), IN IN_TS TIMESTAMP)
    MODIFIES SQL DATA
BEGIN ATOMIC
    INSERT INTO TS_STORE(ID_COL, TS)
    VALUES (IN_KEY, IN_TS);
END;

and attempt to call it as:

CALL TEST_PROC('key2', '2020-02-19 12:17:53');

Then I get an error: "incompatible data type in conversion".

This is a problem for me, since I am not allowed to change the signature of the the stored procedure to bypass the problem, since in my case HSQL is used as a mock for a production database (DB2) where the equivalent procedure works as expected.

Answer 1

It works if you call the procedure with a TIMESTAMP value, as opposed to the character string.

CALL TEST_PROC('key2', TIMESTAMP'2020-02-19 12:17:53');