CODEWITHSUNDEEP
jdbchsqldb
Henil Shah
Henil Shah

Reputation: 137

Exception in setArray() in HSQL

I am using HSQL as an EmbeddedDatabaseType in my unit tests. I have created a table using this query:

CREATE TABLE Table1 (array1 VARCHAR(256) ARRAY, obj1 VARCHAR(256) );

This is the upsert query:

MERGE INTO Table1 USING (VALUES ?, ?) vals(array1, obj1) 
ON Table1.obj1 = vals.obj1 
WHEN MATCHED THEN 
UPDATE SET Table1.array1 = vals.array1 
WHEN NOT MATCHED THEN 
INSERT (array1, obj1) VALUES (vals.array1, vals.obj1);

This is the PreparedStatementSetter:

private static ParameterizedPreparedStatementSetter<data> preparedStatementSetter() {
    return (preparedStatement, data) -> {           
        JDBCArrayBasic array = new JDBCArrayBasic(data.getY(), type);
        preparedStatement.setArray( 1,  array);
        preparedStatement.setString( 2, data.getX() );
    };
}

I am trying to write an array to the database but it gives me this exception:

Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setArray(Unknown Source)

Can someone tell me what I am missing here?

Upvotes: 0

Views: 146

Answers (1)

fredt
fredt

Reputation: 24372

The MERGE statement cannot determine the types of the parameters in the USING clause. You simply cast the parameters to their intended data types.

MERGE INTO Table1 USING (VALUES CAST(? AS VARCHAR(256) ARRAY), CAST(? AS VARCHAR(256))) 
vals(array1, obj1) ON ...

In the case of the second parameter, the parameter default to VARCHAR but it is better to state explicitly what the type is.

In INSERT, SELECT, UPDATE, and DELETE statements, the types of parameters are generally determined by the database engine without using a CAST.

Upvotes: 1

Related Questions