Invoking stored procedures with nested ROW or ARRAY of ROW types in JDBC applications

In Db2® on Linux®, UNIX, and Windows systems stored procedure parameters, ROW types can be nested. You can retrieve or update data in those parameters in JDBC applications.

About this task

Invoking stored procedures with nested ROW or ARRAY of ROW parameters is supported only for IBM® Data Server Driver for JDBC and SQLJ type 4 connectivity to Db2 on Linux, UNIX, and Windows systems.

Procedure

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

Define a Struct object for each of the most deeply nested ROW types, or a Struct[] object for the most deeply nested ARRAY or ROW type.
If you are using the SDK for Java™ Version 6 or later, use the createStruct method of the java.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.
Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
Repeat step 1 for the next most deeply nested ROW type. Continue this process until you have defined and populated a Struct object for the outermost ROW type.
Invoke CallableStatement.setXXX methods to assign values to the IN or INOUT parameters in the CREATE PROCEDURE statement.
Use the CallableStatement.setObject method for ROW parameters.
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 ROW type output parameters is java.sql.Types.STRUCT.
Call the stored procedure.
Invoke the CallableStatement.getObject method to retrieve the value of each OUT ROW parameter. Cast the returned objects as java.sql.Struct values.
Retrieve data from the Struct object for each OUT ROW parameter, and each ROW type that is nested within a ROW parameter.

Example

Suppose that ARRAY and ROW objects are defined as follows:

MYTYPE.SKILLS is an ARRAY type.

CREATE TYPE MYTYPE.SKILLS AS VARCHAR(20) ARRAY[10]

ROW type MYTYPE.PERSON_T contains a field with ARRAY type MYTYPE.SKILLS.

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

ROW type MYTYPE.PEOPLE_T is an array of objects that have ROW type MYTYPE.PERSON_T.

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

ROW type MYTYPE.DEPARTMENT contains a field that has the ARRAY of ROW type MYTYPE.PEOPLE_T.

CREATE TYPE MYTYPE.DEPARTMENT AS ROW
 (ID INTEGER,
  DEPTNAME VARCHAR(20),
  DEPTPEOPLE MYTYPE.PEOPLE_T)

Stored procedure MYSP.DEPARTMENT_SP has two parameters, each of which has the MYTYPE.DEPARTMENT type.

CREATE PROCEDURE MYSP.DEPARTMENT_SP
 (IN PIN MYTYPE.DEPARTMENT, OUT POUT MYTYPE.DEPARTMENT)
   LANGUAGE SQL
   BEGIN
    SET POUT = PIN;
   END

The following code demonstrates how to use Struct objects to assign values to nested ROW objects in a stored procedure input parameter, and how to use Struct objects to retrieve data from nested ROW objects in a stored procedure output parameter. The numbers to the right of selected statements correspond to the previously-described steps.

java.sql.Connection con;
…
java.sql.Struct[] people = new java.sql.Struct[2];
java.sql.Array array ;
java.sql.Struct[] structArr;
                   // Construct an array of Struct objects for the
                   // MYTYPE.PEOPLE_T array or ROW objects
String[] skills1 = {"Java", "C++", "JavaScript"};
java.sql.Array skillsArray1 = 
  con.createArrayOf("VARCHAR", skills1);
                   // Create and populate the first array for the 
                   // MYTYPE.SKILLS ARRAY object
Object[] attributes1 = 
  new Object [] {new Integer (1), "Alpha", "Doe", skillsArray1};
                   // Create an object with the content of the first
                   // input row that corresponds to MYTYPE.PERSON_T
people[0] = 
  con.createStruct ("PERSON_T", attributes1);                             1 
                   // Populate the first element of the array of Struct
                   // for input to the MYTYPE.PEOPLE_T type
String[] skills2 = {"Java", "C++", "C"};
java.sql.Array skillsArray2 = 
  con.createArrayOf("VARCHAR", skills2);
                   // Create and populate the second array for the 
                   // MYTYPE.SKILLS ARRAY object
Object[] attributes2 = 
  new Object [] {new Integer (2), "Beta", "Doe", skillsArray2};
                   // Create an object with the content of the second
                   // input row that corresponds to MYTYPE.PERSON_T
people[1] = 
  con.createStruct ("PERSON_T", attributes2);
                   // Populate the second element of the array of Struct
                   // for input to the MYTYPE.PEOPLE_T type
java.sql.Array peopleArray = 
  con.createArrayOf("PEOPLE_T", people);
                   // Create and populate an Array object for 
                   // input to the DEPTPEOPLE object
Object[] deptAttributes = 
  new Object [] {new Integer (1), "Dev", peopleArray };
                   // Create an object with the content of an
                   // input row for parameter PIN
java.sql.CallableStatement cstmt = con.prepareCall                        2 
  ("CALL MYSP.DEPARTMENT_SP (?, ?)"); 
                   // Create a CallableStatement instance for 
                   // invoking the stored procedure
java.sql.Struct deptStruct =                                              3 
  con.createStruct("DEPARTMENT", deptAttributes);
                   // Create and populate a Struct object for 
                   // input to the PIN parameter
cstmt.setObject(1, deptStruct);                                           4 
                   // Assign the row object to input parameter PIN
cstmt.registerOutParameter(2, java.sql.Types.STRUCT);                     5 
                   // Register the output parameter as a STRUCT type
cstmt.executeUpdate();                                                    6 
                   // Call the stored procedure
java.sql.Struct outputStruct = (java.sql.Struct)cstmt.getObject(2);       7 
                   // Retrieve the content of the POUT row parameter
Object[] structAttributes = outputStruct.getAttributes();
                   // Retrieve an array of objects that contains the
                   // attributes of the outermost output structure
String departmentName = (String)structAttributes[1];
System.out.println ("The department ID is: " + structAttributes[0]);
System.out.println ("The department name is: " + structAttributes[1]);
System.out.println ("The people in the department are: " );
array = (java.sql.Array)structAttributes[2];
structArr = (java.sql.Struct[]) array.getArray();
for (int i = 0; i < structArr.length; i++) {                              8 
  structAttributes = structArr[i].getAttributes();
  System.out.println (
    "ID: " + (Integer)structAttributes[0] + ": " +
    "First name: " + (String)structAttributes[1] + ": " +
    "Last name: " + (String)structAttributes[2] + ": " +
    "Skills: ");
  array = (java.sql.Array) structAttributes[3];
  String[] skillsArray = (String[]) array.getArray();
  for (int j = 0; j < skillsArray.length; j++) {
    System.out.print ("  " + (skillsArray[j]));
  }
  System.out.print ("\n");
}