Handling Object output from ORDBMS

Databases such as PostgreSQL allow storing non-primitive database types, both built-in such as the geometric data types, or user-defined. These datatypes can be returned from stored procedure calls. As long as a toString() method is defined for these objects, all that needs to be done is to specify the String representation of this object in your expected result. For example, a PostgreSQL POINT data type is backed by the org.postgresql.geometric.PGPoint object, which defines a toString() method. So a POINT(x,y) can be represented by the String (x,y). So to test that a POINT(10.0, 20.0) is returned as the seventh column of the first row of the first resultset from a procedure call, the XML would look something like this:

<result>
  <resultset id="1">
    <row id="1">
      <col id="1" type="INTEGER">123</col>
      ...
      <col id="7" type="OTHER">(10.0,20.0)</col>
    </row>
  </resultset>
</result>
        
        

The toString() method must be defined in order for the comparison to work, since SQLUnit compares two objects for equality based on their String representation returned from the toString() method.

Note that SQLUnit does not yet (and probably never will) support passing built-in or user-defined objects as arguments to stored procedures to be tested with SQLUnit. The reason is that it is difficult to convert from the String representation to the equivalent object representation in a generic manner. The approach taken in the PostgreSQL test (AddEmployee.sql) is to pass the parameters for object creation (the x and y coordinates for the Employee.location::POINT) as separate arguments to the stored procedure. The stored procedure is responsible for constructing a POINT object and storing it into the database. This approach allows scaling to more complicated types (such as CIRCLE((x,y),r)) in a trivial manner.