Invocation of stored procedures with ARRAY parameters in SQLJ applications

SQLJ applications that run under the IBM® Data Server Driver for JDBC and SQLJ and connect to Db2® on Linux®, UNIX, and Windows systems data sources can call stored procedures that have ARRAY parameters.

You can use java.sql.Array objects as IN, OUT, or INOUT parameters in a stored procedure.

For IN or INOUT parameters, use the DB2Connection.createArrayOf method (JDBC 3.0 or earlier) or the Connection.createArrayOf method (JDBC 4.0 or later) to create a java.sql.Array object.

There are two ways to retrieve data from an ARRAY output stored procedure parameter:
  • Use the java.sql.Array.getArray method to retrieve the contents of output parameter into a Java™ array.
  • Use a java.sql.Array.getResultSet method to retrieve the output parameter data into a ResultSet object. Then 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

    You need to retrieve the array elements from the ResultSet using the getObject method.

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;
String type = "CHAR";
String [] contents = {"1234", "5678", "9101"};
…
com.ibm.db2.jcc.DB2Connection db2con = (com.ibm.db2.jcc.DB2Connection) con;
                                  // Cast the Connection as a DB2Connection
                                  // so you can use the 
                                  // DB2Connection.createArrayOf method
java.sql.Array inPhoneData = db2con.createArrayOf(type, contents);
java.sql.Array outPhoneData;
try { 
  #sql [db2con] {CALL GET_EMP_DATA(:IN inPhoneData, :OUT outPhoneData ) };
}
catch( SQLException e )
{
  throw e;
}
ResultSet rs = outPhoneData.getResultSet();
while (rs.next()) {
  String phoneNum = (String)rs.getObject(2); // Get phone number
  System.out.println("Phone number = " + phoneNum);
}