DbUnit and nullable columns

We use DbUnit for our database integration tests and this works very well allowing us to populate the database with test data specific for the test at hand.

I have had a problem for the last few hours that has driven me to distraction. I was populating a table with a row and the tests using this data were all OK.

I then added an extra row that also wanted to populate an extra nullable column. The test failed because the test thought this column was null. I stopped the test in the debugger and yes the code was correct, the column was null. What could be wrong ?

My DbUnit XML looked like this:

<dataset>
  <MY_TABLE
      ID="1"
      MANDATORY_COL="This column must have data"
   />

   <MY_TABLE
      ID="2"
      MANDATORY_COL="This column must have data"
      NULLABLE_COL="This column is nullable"
   />
</dataset>

The problem appears to be that DbUnit builds its list of columns from the first occurrance of each table. Any optional columns listed in subsequent entries are simply ignored.

There are two solutions. Either reverse the order of the two items in the XML file, or set the column to null in the rows where the nullable column is not required.

AmbiguousTableNameException in dbUnit

There is plenty of documentation on AmbiguousTableNameException in dbunit but I was unable to find a clear example showing the problem and the solution.

We have an application that needs to work on Oracle & SQLServer. The integration tests make extensive use of dbunits dataset classes to populate test databases. The tests clear out various tables & populate the tables as required. Previous to me being involved there would be only one database that had to be populated for use and cleaned out to run the tests. I found this a pain so I created a test database for the integration test use only.

SQL Server continued to work fine however when running the Oracle tests I got lots of AmbiguousTableNameExceptions being thrown. It took me a good while to make the connection as to the cause despite it being written in black & white.

Our code was creating the DatabaseConnection as follows:

DataSource ds = (DataSource) applicationContext.getBean(getDataSourceName());
DatabaseConnection mDbConnection = new DatabaseConnection(getConnection(ds));

Logically this should be absolutely fine. First off it all worked before. The Datasource fully describes the connection. In the debugger I could see the username, password and connection url. Accessing the database from the command line using the same parameters all looked cool. Upgrading to dbunit 2.2 proved fruitless.

So why would the connection come up with an AmbiguousTableNameException when that user has no privileges to see any other schema? The long answer to that defeats me.
However as I said the solution is there on the dbunit website.

Despite the fact that the connection has everything defined you still need to tell the DatabaseConnection object the schema name. In the constructor pass in the Schema name and all will be fine.

DatabaseConnection mDatabaseConnection = new DatabaseConnection(connection, getSchemaName());

This all appears to be a bug in dbunit and potentially a security flaw as it implies a connection with limited access can see other schemas.