Handling BLOB and CLOB outputs

Modern databases are frequently capable of storing BLOBs (Binary large objects) and CLOBs (Character large objects) as columns in their tables. Since it is hard and messy to include the expected CLOB output in the XML test file, and pretty much impossible in case of a BLOB, SQLUnit requires that you either specify the name of a file containing the data or specify the MD5 Checksum for the data as a String in your expected results element.

SQLUnit converts large objects such as CLOB, BLOB, LONGVARBINARY and LONGVARCHAR to an MD5 digest when extracting from the database via a stored procedure or SQL call. This is done in order to make subsequent comparisons faster. Other types are extracted and stored as Strings, which may be converted to an MD5 digest form at comparison time if the test matches it against a file name (prefixed with file:) or an MD5 digest (prefixed with md5:).

If you are specifying the name of the file to compare against, prepend either the full path name of the file or the relative path offset from the directory where the tests will be run, with the string file:. If specifying the MD5 Checksum for the resulting LOB data, prepend the checksum string with md5:.

SQLUnit writes CLOB and BLOB objects retrieved from the database into temporary files on disk. In case of a test failure, the mapping between the LOB column [resultset,row,col] in the failed test and the temporary file created is shown at the bottom of the test report. It is up to the user to verify what the difference is using operating system tools such as diff (for CLOBs) and cmp (for BLOBs).