0

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.

garfield
  • 571
  • 9
  • 21

1 Answers1

0

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');
fredt
  • 24,044
  • 3
  • 40
  • 61