Search code examples
javamysqlsqlhsqldb

SQLSyntaxErrorException: data type exception due to `COALESCE` in HSQLDB

I had to upgrade the org.hsqldb library version from 2.2.9 to 2.4.0, in order to support schemas with NOT EXIST keyword as suggested in another question at this site, but then I encountered lots of JUnit tests which fails with 

Caused by: java.sql.SQLSyntaxErrorException:                             
data type cast needed for parameter or null literal in statement
[INSERT INTO xxx(y) VALUES (COALESCE(?,?))]

my hsqldb looks like:

CREATE TABLE xxx (
    y VARCHAR (32) NOT NULL
)

they only thing that worked for me is to move the COALESCE logic to my Java sources before inserting.

Questions:

  1. it makes me think that the requirement NOT NULL in the schema didn't worked until the version 2.4.0, and that's why my tests fails only now. beside deleting this requirement, there is anything I can do to avoid that restriction?

  2. is there a function like COALESCE which insert default value if all the parameters are null?

Solution

  • You can fix it with a cast:

    INSERT INTO xxx(y) VALUES (COALESCE(CAST(? AS VARCHAR(32)),?)