7.6 Using Object Types and Collections with Java

Table of Contents Previous Next


7 Advanced JDBC Connector Functionality : 7.6 Using Object Types and Collections with Java

The SQL CREATE TYPE command is used to create a user-defined object type, which is stored in the Advanced Server database. The CREATE TYPE command is also used to create a collection, commonly referred to as an array, which is also stored in the Advanced Server database.
The basic object type is created with the CREATE TYPE AS OBJECT command along with optional usage of the CREATE TYPE BODY command.
A nested table type collection is created using the CREATE TYPE AS TABLE OF command. A varray type collection is created with the CREATE TYPE VARRAY command.
Create the object types in the Advanced Server database. Object type addr_object_type defines the attributes of an address:
Object type emp_obj_typ defines the attributes of an employee. Note that one of these attributes is object type ADDR_OBJECT_TYPE as previously described. The object type body contains a method that displays the employee information:
A CallableStatement object is prepared based on the display_emp() method of the emp_obj_typ object type:
createStruct() initializes and creates instances of object types addr_object_type and emp_obj_typ named address and emp, respectively:
The call to registerOutParameter() registers the parameter type (Types.STRUCT) of emp_obj_typ:
The setObject() method binds the object instance emp to the IN OUT placeholder.
A call to stmt.execute() executes the call to the display_emp() method:
getObject() retrieves the emp_obj_typ object type. The attributes of the emp and address object instances are then retrieved and displayed:
Create collection types NUMBER_ARRAY and CHAR_ARRAY in the Advanced Server database:
Listing 1.17-a is an SPL function that uses collection types NUMBER_ARRAY and CHAR_ARRAY as IN parameters and CHAR_ARRAY as the OUT parameter.
The function concatenates the employee ID from the NUMBER_ARRAY IN parameter with the employee name in the corresponding row from the CHAR_ARRAY IN parameter. The resulting concatenated entries are returned in the CHAR_ARRAY OUT parameter.
A CallableStatement object is prepared to invoke the concatEmpIdName() function:
createArrayOf() initializes and creates collections named empIdArray and empNameArray:
The call to registerOutParameter() registers the parameter type (Types.ARRAY) of the OUT parameter:
The setObject() method binds the collections empIdArray and empNameArray to the IN placeholders:
A call to stmt.execute() invokes the concatEmpIdName() function:
getArray() retrieves the collection returned by the function. The first two rows consisting of the concatenated employee IDs and names are displayed:

7 Advanced JDBC Connector Functionality : 7.6 Using Object Types and Collections with Java

Table of Contents Previous Next