Technical Info on .NET and Java, Software Tool and Book Recommendations, and Thoughts on Software Development in General from Scott McMaster.
Tuesday, November 25, 2008
Looking for Tips on Formatting Code in Blogger
dbUnit and HSQLDB Databases with Schema Names
Statement ddl = conn.createStatement();
ddl.executeUpdate("CREATE SCHEMA SCOTTSCHEMA AUTHORIZATION DBA"); |
Now, when you get ready to use dbUnit and create your IDatabaseConnection, you need to pass the schema name:
IDatabaseConnection dbUnitConn = new DatabaseConnection(conn, "SCOTTSCHEMA"); |
Now I should be able to load up XML files using dbUnit that look like this for a hypothetical "CONTACTS" table:
<?xml version="1.0" encoding="UTF-8"?> <dataset> <SCOTTSCHEMA.CONTACTS FIRST_NAME="Scott" LAST_NAME="McMaster"/> <SCOTTSCHEMA.CONTACTS FIRST_NAME="Tracy" LAST_NAME="McMaster"/> </dataset>
It turns out that passing the schema name in the second parameter is necessary, but not sufficient: If you try to run a database operation against dbUnitConn at this point, you'll get the following exception:
org.dbunit.dataset.NoSuchTableException: SCOTTSCHEMA.CONTACTS at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:222) at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109) at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79) at ...
When I first hit this, it was easier to figure out what was going on from the dbUnit code rather than the documentation. I discovered rather quickly that you also need to set FEATURE_QUALIFIED_TABLE_NAMES in the IDatabaseConnection's config:
dbUnitConn.getConfig().setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true); |
Now dbUnit will build its internal map of database tables prefixed with the schema name and will be able to find the table metadata when you execute an operation. It seems like it would make sense to default this feature to "true" when you create a database connection with a schema name...