dbunit, Postgres and NoSuchTableException

I am having trouble running some dbunit tests against Postgres.

I’ve managed to extract some data from the database into an XML file with no trouble at all, using the following code:

IDatabaseConnection connection = new DatabaseConnection( conn );
QueryDataSet partialDataSet = new QueryDataSet(connection);
partialDataSet.addTable("mytable", " SELECT * FROM mytable WHERE id=1068 ");
FlatXmlWriter datasetWriter = new FlatXmlWriter(new FileOutputStream("mydata.xml"));
datasetWriter.write( partialDataSet );

But then I can’t reload it using the following code:

DatabaseOperation.INSERT.execute(connection, new FlatXmlDataSet(
					this.getClass().getClassLoader().getResourceAsStream(
							"mydata.xml")));

The error I get is a variation on org.dbunit.dataset.NoSuchTableException: mytable.

I’ve tried specifiing the schema when I create the connection, I’ve tried editing the xml to include the schema. I thought it was a problem with case, as sometimes the error message shows the table name in uppercase depending on what I have fiddled with, but it seems to make no difference. The table is definitely there as I extracted data from it fine. Argh what a nuisance.

  

10 thoughts on “dbunit, Postgres and NoSuchTableException

  1. Answered my own question as soon as I posted it. Downloading a slightly older version of db unit (2.2.3 instead of 2.4.1) solved the problem instantly!

  2. Thanks man! This helped me too. My suspicion was that because DbUnit quotes the table names this doesn’t work for postgres but I didn’t have the time to explore and solve that. I mean even if postgres is case insensitive which means

    select * from table

    is the same as

    select * from TABLE

    it is not the same as

    select * from “TABLE”

    that is my candidate for root of the problem. but replacing the version was far more comfortable ;-)

  3. Actually, the last version of dbunit that works for me (I had the exact same issue as described) is 2.3.0. All versions above that fails.
    – Hugues

  4. I also found this problem using dbunit-maven-plugin which latest version relies on dbunit 2.4.1.
    Using dbunit-maven-plugin 1.0-beta1 , which uses dbunit 2.2, it works fine.
    Does anyone know if this problem has been reported on DBUnit support site ?

  5. I am having almost same problem here. I will try your suggestion now. Thanks for the tip, this was driving me crazy!

  6. Downgrading my DbUnit dependency was not an option as I wanted to use FlatXmlDataSetBuilder. I stepped through DbUnit with the debugger and a possible solution seems to be to use PUBLIC as schema (if you don’t have a schema or one that is not working out)

    new HsqldbConnection(connection, “PUBLIC”);

    Thanks for the input!

  7. Just to confirm (again) – I’m in the exact same boat as raoulsson above. I didn’t want to downgrade the version because I like the FlatXmlDataSetBuilder. I’ve successfully gotten dbunit 2.4.8 working with postgres 8.4 and flat xml datasets for tables created in the postgres “public” schema by using this code:

    ⪯

    JdbcDatabaseTester databaseTester =
    new JdbcDatabaseTester(dbDriverClass, dbConnectionUrl, dbUsername, dbPassword) {
    @Override public IDatabaseConnection getConnection() throws Exception {
    // for ORCL:
    // IDatabaseConnection dbconn = new DatabaseConnection(getDataSource(puName).getConnection(), dbUsername);
    // for PostGRES
    IDatabaseConnection dbconn = null;
    if (getSchema() != null) {
    dbconn = new DatabaseConnection(getDataSource(puName).getConnection(), getSchema());
    }
    else {
    dbconn = new DatabaseConnection(getDataSource(puName).getConnection(), dbUsername);
    }
    testCase.configureConnection(puName, dbconn);
    return dbconn;
    }

Leave a Reply

Your email address will not be published. Required fields are marked *