Invoking stored procedures with ARRAY of ROW parameters in JDBC applications

DB2® for Linux, UNIX, and Windows stored procedures can have parameters of the ARRAY of ROW type. To retrieve or update data in those parameters in JDBC applications, you use arrays of Struct objects.

To retrieve or update data in ARRAY of ROW parameters, follow these steps:

1. Define a Struct object for each row of each input parameter that has the ARRAY of ROW data type.

   If you are using the SDK for Java™ Version 6 or later, use the createStruct method of the jav.sql.Connection interface. If you are using an earlier version of the SDK for Java, use the createStruct object of the com.ibm.db2.jcc.DB2Connection interface.

2. Define an Array object for each array of rows.

   If you are using the SDK for Java Version 6 or later, use the createArrayOf method of the jav.sql.Connection interface. If you are using an earlier version of the SDK for Java, use the createArrayOf object of the com.ibm.db2.jcc.DB2Connection interface.

3. Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
4. Invoke CallableStatement.setXXX methods to assign values to the IN or INOUT parameters in the CREATE PROCEDURE statement.

   Use the CallableStatement.setArray method for ARRAY of ROW parameters.

5. Invoke the CallableStatement.registerOutParameter method to register the data types of parameters that are defined as OUT in the CREATE PROCEDURE statement.

   The data type for ARRAY of ROW type output parameters is java.sql.Types.ARRAY.

6. Call the stored procedure.
7. Invoke the CallableStatement.getArray method to retrieve the array from each OUT ARRAY of ROW parameter into a java.sql.Array object.
8. Invoke the java.sql.Array.getArray method to retrieve the contents of the java.sql.Array object. Cast the retrieved objects as java.sql.Struct[] arrays.
9. Retrieve data from each element of the array of Struct objects for each OUT ARRAY of ROW parameter.

CREATE TYPE MYTYPE.PERSON_T AS ROW
 ID INTEGER
 FIRSTNAME VARCHAR(20)
 LASTNAME VARCHAR(20)
 SALARY INTEGER

CREATE TYPE MYTYPE.PEOPLE_T AS MYTYPE.PERSON_T ARRAY[10]

CREATE PROCEDURE MYSP.PEOPLE_SP
 (IN PIN MYTYPE.PEOPLE_T, OUT POUT MYTYPE.PEOPLE_T)
 LANGUAGE SQL
 BEGIN
 …
 END

Connection con;
CallableStatement cstmt;
…

peopleElements = new java.sql.Struct[2];
personAttributes = new Object[] {
  new Integer(1), "John", "Doe", new Integer(60000)
};
peopleElements[0] =
 con.createStruct("MYTYPE.PERSON_T", personAttributes);                  1 
personAttributes = new Object[] {
  new Integer(2), "Jane", "Doe", new Integer(65000)
};
peopleElements[1] =
  con.createStruct("MYTYPE.PERSON_T", personAttributes);
Array people = con.createArrayOf("MYTYPE.PERSON_T", peopleElements);     2 
cstmt = con.prepareCall("CALL MYSP.PEOPLE_SP(?,?)");                     3 
cstmt.setArray(1, people);                                               4 
cstmt.registerOutParameter(2, java.sql.Types.ARRAY);                     5 
cstmt.execute();                                                         6 
java.sql.Array people = cstmt.getArray(2);                               7 
java.sql.Struct[] persons =
  (java.sql.Struct[])people.getArray();                                  8 
for (int i = 0; i < persons.length; i++) {                               9 
  java.sql.Struct person = persons[i];
  Object[] personAttributes = person.getAttributes();
  Integer id = (Integer)personAttributes[0];
  String firstName = (String)personAttributes[1];
  String lastName = (String)personAttributes[2];
  Integer salary = (Integer)personAttributes[3];
}