DB2 Version 9.7 for Linux, UNIX, and Windows

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® for Linux, UNIX, and Windows 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 DB2Connection.createArrayOf method (JDBC 3.0) or the Connection.createArrayOf method (JDBC 4.0 or later) 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
cstmt.setObject (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();   
}