H2 user-defined-function org.h2.jdbc.JdbcSQLException non-hex character error

Question

When attempting to pass a List<String> to an H2 user defined function, I'm getting the following error:

[{call MY_USER_DEFINED_FUNCTION(?)}]; SQL state 
[90004]; error code [90004]; Hexadecimal string contains non-hex character: 
"[ABCD, EFGH]"; SQL statement:
call MY_USER_DEFINED_FUNCTION(?)  [90004-192]; nested 
exception is org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-
hex character: "[1234, 5678]"; SQL statement:
call MY_USER_DEFINED_FUNCTION(?)  [90004-192]

As the snippet from the log shows, the list contains two strings: "ABCD" and "EFGH". I'm attempting to pass these values to a SQL statement's IN clause. The user-defined-function appears as follows:

CREATE ALIAS MY_USER_DEFINED_FUNCTION AS
'
java.sql.ResultSet getTableContent(java.sql.Connection con, List<String> idList) throws Exception {
final PreparedStatement statement = con.prepareStatement("SELECT * FROM MY_TABLE WHERE id IN (?)");

String ids[] = new String[idList.size()];
idList.toArray(ids);
statement.setArray(1, con.createArrayOf("text", ids));

java.sql.ResultSet rs = statement.executeQuery();
return rs;
}'

The user-defined-function is being called from a DAO method. I'm using Spring to manage the DB connections, here's a snippet of the calling method:

public List<MyObject> getMyObjects(final List<String> idList) {

    Map<String, Object> params = new HashMap<>();

    params.put("idList", idList);
    // Execute stored procedure
    Map<String, Object> output = super.execute(params);
    // Collect stored procedure results into list
    List<MyObject> myObjectList = (List<MyObject>) output.get("myResultSet");

    return myObjectList;
}

I've also tried passing an array, and using setObject on the prepared statement -- same error is thrown. I'm able to get the user-defined-function to return when passing in a single string, and calling setString on the prepared statement, but can't seem to pass multiple strings to the IN clause of the SQL statement.

Any ideas on why this is being thrown?

Answer 1

Solved:

Had declared the input parameter type as Types.VARCHAR, changed to Types.JAVA_OBJECT and resolved the hex character issue:

StoredProcedure.declareParameter(new SqlParameter("idList", Types.JAVA_OBJECT));