Java: Problems while setting an array as parameter name in DB2 SQL query

Question

DB2 Driver version: 4.19.66

DB2 installed version: DB2 v11.5.7.0

Java version: 1.8_121

I'm trying to set a parameter on a DB2 SQL query:

SELECT distinct object_id AS oid 
FROM myschema.package p 
INNER JOIN myschema.package_item pi ON p.machine_id = pi.machine_id 
                                    AND p.package_id = pi.package_id 
INNER JOIN main_schema.item i ON i.item_id = pi.id_articulo
WHERE p.package_status_id = 5 AND p.machine_id IN (:machineIdsParam)

I must say that p.machine_id is an INTEGER field in the DB2 database.

I prepare the connection like this:

Properties properties = new Properties();         // Create Properties object
properties.put("user", config.getDbUser());       // Set user ID for connection
properties.put("password", bdPassword);           // Set password for connection
properties.put("enableNamedParameterMarkers", 1);
connection = (DB2Connection) DriverManager.getConnection(config.getDbUrl(), properties);

And then I try to set the parameter on this query:

DB2PreparedStatement ps = connection.getPreparedStatement(sqlString);
List<Integer> machineIds = Array.asList(new Integer[] { 1, 5, 7, 9});
Array machineIdArray = connection.createArrayOf("INTEGER", machineIds.toArray(new Integer[idInstalaciones.length]));
ps.setJccArrayAtName("machineIdsParam", machineIdArray);

But I'm getting this error:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10417][4.19.66] Invalid data conversion: Parameter instance com.ibm.db2.jcc.am.o@36fc695d is invalid for the requested conversion. 

I couldn't find an example on how to use this setJccArrayAtName(), only documentation where I'm unable to find the cause of this error. I'm only guessing that it has something to do with the data type but I don't know how to make this work.

Answer 1

I don't believe any family of Db2 supports arrays with the IN predicate..

What's normally needed is separate parameters

SELECT distinct object_id AS oid 
FROM myschema.package p 
INNER JOIN myschema.package_item pi ON p.machine_id = pi.machine_id 
                                    AND p.package_id = pi.package_id 
INNER JOIN main_schema.item i ON i.item_id = pi.id_articulo
WHERE p.package_status_id = 5 AND p.machine_id IN (:machineId1 
                                                   ,:machineId2
                                                   ,:machineId3
                                                   ,:machineId4
                                                  )

That of course requires you to know how many values you'll need ahead of time, or at least some maximum number. (You can always load the last value in any remaining parameters.)

The other option is to pass a delimited string that get's split out into individual values. Something like so (assuming a comma separated list)

SELECT distinct object_id AS oid 
FROM myschema.package p 
INNER JOIN myschema.package_item pi ON p.machine_id = pi.machine_id 
                                    AND p.package_id = pi.package_id 
INNER JOIN main_schema.item i ON i.item_id = pi.id_articulo
WHERE p.package_status_id = 5 AND p.machine_id IN (select int(element)
                                                   from table (systools.split(:machineIds, ' '))
                                                  )

systools.split() may be specific to Db2 for IBM i, other platforms may have it in a different schema.

If nothing else, you can write your own User Defined Function (UDF) to do the split.

Answer 2

For Db2 for LUW:

... IN 
(
  SELECT TOK
  FROM XMLTABLE
  (
    'for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
    PASSING CAST (:machineIdsParam AS VARCHAR (100)) AS "s"
    COLUMNS
      TOK INT PATH '.'
  )
)

You pass a string like '1,2,3' for the :machineIdsParam parameter. That is: int values separated by comma.