VARRAY/TABLE

The Oracle VARRAY and TABLE data types map to the JDBC ARRAY data type. To use these data types as a parameter, an array must be created using the createArray() method of the ExtConnection interface.

Refer to JDBC extensions in the Progress DataDirect for JDBC Drivers Reference for more information about the createArray() method.

If you want more accurate metadata information for either of these data types, set the CatalogOptions connection property to 8, but be aware that this can negatively affect performance. See CatalogOptions for details.

Example A: Selecting Data From an Array

This example selects two columns, reads them into separate arrays, and retrieves the objects from the arrays.

Statement st = connection.createStatement();
ResultSet resultSet = st.executeQuery(
          "SELECT array_col, table_col FROM complex_type_table");
      
// Loop through the result set and retrieve the objects
while(resultSet.next()) {
        
    // Use getArray to read the value into an array
    java.sql.Array myVarray = resultSet.getArray(1);
    java.sql.Array myTable = resultSet.getArray(2);
        
    // Call getArray() method to retrieve the array of objects
    Object varrayValues[] = (Object[])myVarray.getArray();
    Object tableValues[] = (Object[])myTable.getArray();
}

Example B: Inserting Data into an Array

This example constructs two separate arrays and inserts the array objects into their respective columns.

Object objArray[] = new Object[4];
objArray[0] = new java.math.BigDecimal (1);
objArray[1] = new java.math.BigDecimal (2);
objArray[2] = new java.math.BigDecimal (3);
objArray[3] = new java.math.BigDecimal (4);
      
// Construct the array for the VARRAY
java.sql.Array colVarray =
((com.ddtek.jdbc.extensions.ExtConnection)connection).createArray(
            "NUM_VARRAY", objArray);
// Construct the array for the Table
java.sql.Array colTable =
((com.ddtek.jdbc.extensions.ExtConnection)connection).createArray(
            "NUM_TABLE", objArray);
      
PreparedStatement ps = connection.prepareStatement(
  "INSERT INTO complex_type_table (array_col, table_col) VALUES (?, ?)");
      
// Bind the array objects to be inserted
ps.setArray(1, colVarray);
ps.setArray(2, colTable);
ps.executeUpdate(); 
ps.close();