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.

What platform and version of Db2?

Charles
– Charles

2022-04-20 17:31:53 +00:00
Commented Apr 20, 2022 at 17:31 — Charles
– Charles, Commented Apr 20, 2022 at 17:31
DB2 v11.5.7.0, I'm editing the question to show this.

madtyn
– madtyn

2022-04-21 07:08:57 +00:00
Commented Apr 21, 2022 at 7:08 — madtyn
– madtyn, Commented Apr 21, 2022 at 7:08

Mark Barinstein · Accepted Answer · 2022-04-21 07:46:40Z

1

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.

answered Apr 21, 2022 at 7:46

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

madtyn Over a year ago

I'm trying this ASAP, but I don't understand where can I read about how to use these string expressions with $variables. Also, I don't know where can I read about what PATH '.' is doing

Mark Barinstein Over a year ago

XMLTABLE function overview, tokenize function

Charles · Accepted Answer · 2022-04-20 19:21:12Z

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.

I'm trying but systools.split doesn't seem to work: SQL Error [42884]: No authorized routine named "SPLIT" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related