Is it possible to have Strings Array as one of the input type for calling Stored proc in IBM BPM ?

Question

Is it possible to have Strings Array as one of the input type for calling Stored proc.
I want to pass List of string as a input to a Store proc.

Answer 1

The SQL Execute Statement / SQL Call Stored Procedure services of the System Data toolkit take parameters as a list of SQLParameter, each parameter being a scalar (String, Integer, Decimal, Date...). A JDBC array type cannot be passed that way. Three working options:

1. Pass a delimited string and split it in the procedure - the most portable:

// service flow script: build the parameter list
var ids = tw.local.customerIds.join(",");             // "12,45,78"
tw.local.params = new tw.object.listOf.SQLParameter();
tw.local.params[0] = new tw.object.SQLParameter(); tw.local.params[0].value = ids; tw.local.params[0].type = "VARCHAR"; tw.local.params[0].mode = "IN";
tw.local.sql = "{ call GET_ORDERS_FOR_CUSTOMERS(?) }";
-- Db2 procedure: split with a recursive CTE / Oracle: REGEXP_SUBSTR in a CONNECT BY, or a table-valued split function

2. A temporary table: insert the list with SQL Execute Multiple Statements (one INSERT per element, same transaction / connection) and let the procedure join the temporary table.
3. A Java integration when the database really needs an array type (Oracle VARRAY, PostgreSQL text[]): a few lines with Connection.createArrayOf, using the WebSphere data source by JNDI name:

DataSource ds = (DataSource) new InitialContext().lookup("jdbc/MyAppDS");
try (Connection c = ds.getConnection(); CallableStatement cs = c.prepareCall("{ call GET_ORDERS(?) }")) {
  java.sql.Array arr = c.createArrayOf("VARCHAR", ids);      // String[] ids; Oracle: ((OracleConnection) c.unwrap(OracleConnection.class)).createOracleArray("ID_TAB", ids)
  cs.setArray(1, arr); cs.execute(); ...
}

Pass the JNDI name of the data source as an environment variable, and keep the SQL services' dataSourceName the same so that transaction behaviour stays consistent.

References

• BAW documentation - SQL integration services (SQLParameter)
• BAW documentation - Java integration