Invoking stored procedures with ROW parameters in JDBC applications

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

Procedure

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

  1. Define a Struct object for each parameter that has the ROW data 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.

  2. Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
  3. 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.

  4. 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.

  5. Call the stored procedure.
  6. Invoke the CallableStatement.getObject method to retrieve the value of each OUT ROW parameter. Cast the returned objects as java.sql.Struct values.
  7. Retrieve data from the Struct object for each OUT ROW parameter.

Example

A ROW type and a stored procedure have the following definitions:
CREATE TYPE MYTYPE.PERSON_T AS ROW
 ID INTEGER
 FIRSTNAME VARCHAR(20)
 LASTNAME VARCHAR(20)
 SALARY INTEGER

CREATE PROCEDURE MYSP.PERSON_SP
 (IN PIN MYTYPE.PERSON_T, OUT POUT MYTYPE.PERSON_T)
 LANGUAGE SQL
 BEGIN
 …
 END
The following code calls stored procedure MYSP.PERSON_SP and uses Struct objects to assign data to ROW parameter PIN and to retrieve data from ROW parameter POUT. The numbers to the right of selected statements correspond to the previously-described steps.
Connection con;
CallableStatement cstmt;
…
personAttributes = new Object[] {
  new Integer(1), "John", "Doe", new Integer(60000)
};
person = con.createStruct("MYTYPE.PERSON_T", personAttributes);          1 
cstmt = con.prepareCall("CALL MYSP.PERSON_SP(?,?)");                     2 
cstmt.setObject(1, person);                                              3 
cstmt.registerOutParameter(2, java.sql.Types.STRUCT);                    4 
cstmt.execute();                                                         5 
person = (java.sql.Struct)cstmt.getObject(2);                            6 
Object[] personAttributes = person.getAttributes();                      7 
Integer id = (Integer)personAttributes[0];
String firstName = (String)personAttributes[1];
String lastName = (String)personAttributes[2];
Integer salary = (Integer)personAttributes[3];
cstmt.close();