GemfireXD - PreparedStatement setArray for String(VARCHAR) array not working

Question

I have some JDBC code as follows where I am using gemfireXD as DB.

I am trying to parallelize the execution of a procedure on a list of inputs. The size of the list I am passing as the parameter to the procedure is not fixed and it is determined after the execution of the query in the code below. This list can be too big in size.

Code:

 private static void executeProc (Connection cxn) throws SQLException {

          Statement stmt = null;
          try {
                 stmt = cxn.createStatement();

                 stmt.execute("select distinct field1 from Table1");
                 ResultSet rs = stmt.getResultSet();
                 List<String> fieldList = new ArrayList<String> ();
                 while (rs.next()) {
                       fieldList.add(rs.getString("field1"));
                 }
                 Array fieldArray = cxn.createArrayOf("VARCHAR", fieldList.toArray(new String[fieldList.size()]));
                 CallableStatement callableStmt = cxn.prepareCall("{CALL procedure1() ON TABLE Table1 WHERE field1 IN (?)}");
                 callableStmt.setArray(1, fieldArray);
                 callableStmt.execute();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
   }

Error: while executing the code it is giving below runtime error.

Feature not Implemented : createArrayOf(String, Object[])

Question:

The gemfireXD documentation says Data Aware procedure can be executed passing a list using setArray method. But when I am working on actual API, the setAaary() method is expecting an Array Object instead of a List.

GemfireXD document gives an example of such procedure call in its documentation (1.4.0) which supports setArray() method. The direct example from the documentation is as follows: -

// GemFire XD data-aware procedure invocation.

CallableStatement callableStmt = connection.prepareCall("{CALL order_credit_check() ON TABLE Orders WHERE customerID IN (?)}"); callableStmt.setArray(1, <-list of customer IDs->);

How do I call preparedStatement.setArray to set the parameter in the query?

Could any one please suggest any other implementation approch to achieve the desired result?

Answer 1

GemFire XD does not support Array data type so createArrayOf method will not work.

For the kind of query in you question where you want to use in-list in the where clause, you may have to set each individual value as a parameter

CallableStatement callableStmt = cxn.prepareCall("{CALL procedure1() ON TABLE Table1 WHERE field1 IN (?, ?, ?)}");
callableStmt.setInt(1, 1);
callableStmt.setInt(2, 2);
callableStmt.setInt(3, 3);
....