Tuesday, 26 August 2008

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.

1 comment:

Luc Russell said...

Thanks, that solved my DBUnit woes.