Invocation of stored procedures with ARRAY parameters in JDBC applications

JDBC applications that run under the IBM® Data Server Driver for JDBC and SQLJ can call stored procedures that have ARRAY parameters.

ARRAY parameters are supported in stored procedures on Db2® on Linux®, UNIX, and Windows systems Version 9.5 and later.

ARRAY parameters are supported in native SQL procedures on Db2 for z/OS® Version 11 and later. Programs that call Db2 for z/OS stored procedures with array parameters must use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity.

You can use java.sql.Array objects as arguments for calling stored procedures with array parameters.

For IN or INOUT parameters, use the Connection.createArrayOf method to create a java.sql.Array object. Use the CallableStatement.setArray method or the CallableStatement.setObject method to assign a java.sql.Array object to an ARRAY stored procedure parameter.

You can register an OUT ARRAY parameter for a stored procedure call by specifying java.sql.Types.ARRAY as the parameter type in a CallableStatement.registerOutParameter call.

There are two ways to retrieve data from an ARRAY output parameter:
  • Use the CallableStatement.getArray method to retrieve the data into a java.sql.Array object, and use the java.sql.Array.getArray method to retrieve the contents of the java.sql.Array object into a Java™ array.
  • Use the CallableStatement.getArray method to retrieve the data into a java.sql.Array object. Use the java.sql.Array.getResultSet() method to retrieve the data into a ResultSet object. Use ResultSet methods to retrieve elements of the array. Each row of the ResultSet contains two columns:
    • An index into the array, which starts at 1
    • The array element
Example: Suppose that input and output parameters IN_PHONE and OUT_PHONE in stored procedure GET_EMP_DATA are arrays that are defined like this:
CREATE TYPE PHONENUMBERS AS VARCHAR(10) ARRAY[5]

Call GET_EMP_DATA with the two parameters.

Connection con;
CallableStatement cstmt;
ResultSet rs;
java.sql.Array inPhoneData;
…
cstmt = con.prepareCall("CALL GET_EMP_DATA(?,?)");
                                            // Create a CallableStatement object
String[] charArray = new String[] {"a", "b", "c"};        
inPhoneData = conn.createArrayOf("CHAR", charArray);   
cstmt.setArray(1, inPhoneData);             // Set input parameter
cstmt.registerOutParameter (2, java.sql.Types.ARRAY);         
                                            // Register out parameters
cstmt.executeUpdate();                      // Call the stored procedure
Array outPhoneData = cstmt.getArray(2);                                   
                                            // Get the output parameter array
System.out.println("Parameter values from GET_EMP_DATA call: ");
String [] outPhoneNums = (String [])outPhoneData.getArray();
                                            // Retrieve output data from the
                                            // JDBC Array object into a Java
                                            // String array
for(int i=0; i<outPhoneNums.length; i++) {
  System.out.print(outPhoneNums[i]); 
  System.out.println();  
}