We are trying to simplify some of our unit testing. One of the problems we are running into is that Maven, which we just recently switched too, wants to run these unit tests each and everytime, which isn't a bad thing, but can get messy when the data gets fouled up, which happens a lot when you have several developers all making changes to the same code base accessing the same shared DB2 development databases.

One of the solutions I thought of was to use Hypersonic as an in-memory DB just for the tests. In this way, we could populate the in-memory tables with clean, fresh data, do our tests, and not have to worry that the underlying data is corrupt if one test fails, since it will be rebuilt everytime the test runs. And since its in-memory, it runs way faster than trying to connect to a remote DB over JDBC. This works absolutely great with our MySQL database, but with DB2 we have ran into a "snafu".

The problem is that DB2, unlike many other DB servers out there, uses an "Owner" tacked on to the from of the table name. So, its not "SELECT * FROM MY_TABLE", its "SELECT * FROM OWNR.MY_TABLE". Hypersonic doesn't support this "owner" idea, from what I can. It also doesn't support tables with a '.' in them, so I can't even fake it out. I even tried naming the database itself to my owner name, but that failed as well. And since the table name is hard-coded into the Value Object classes (using Annotations, mind you), the table in DB2 must match the table in Hypersonic.

My question(s)...has anyone ran into this problem? Is there a work-around to it? Are there any other solutions to provide an in-memory DB such that the unit testing can run, but not be tied to some physical DB instance? How are others handling testing from multiple sources in a co-existing test DB?

I am in the exact situation as you. We are currently trying to do the same sort of unit testing with an in-memory db. We are using HSQLDB to bulid the tables before the test, run the test, and drop the tables afterwards so each developer can run their own test whenever they want without affecting the others.

Our qurries are written for Oracle which uses the same "owner" idea and therefore I cannot run those qurries unmodified. For example, I have to change the "SELECT * FROM USER1.PERSON" to "SELECT * FROM PERSON" and it works (however this is not the point). So I'm stuck. We have another db that is being use and it does not use the "owner" idea so when I create the db for those I don't see any problems. I'll let you know.

I will tell you what else I've tried to do but I wasn't able to solve our problem. I ran the runManager.bat for HSQLDB to setup a standalone file db and created a new user "USER1" and dropped the default "SA" user. I was thinking if I removed the default user and created another user with admin rights, my problems would be solved. Unfortunately not. I still get the SQLException with "invalid schema name" and again we're stuck.

If you were able to get solve or workaround this problem I would love to hear how did it. I'll keep you posted if I come up with any solutions.

Just an update. I had a schema issue which was my problem. Once I changed my schema from the default "PUBLIC" to "USER1" everything worked!

Now, I have a problem of selecting a column within my Java code. Say I have two tables, PERSON(FIRST, LAST, ID) and PEOPLE(ID, TITLE). With the following code, rs1 is the resultSet from a previous statment for the PEOPLE table.

String sql = "SELECT FIRST, LAST FROM PERSON WHERE ID = ?";
PreparedStatement psGetThresholds = null;

ps = connection.prepareStatement(sql);
ps.setString(1, rs1.getString("PEOPLE.ID")); <----- error here

I get an SQLException: Column not found: PEOPLE.ID

This is my next task. If anyone has any suggestions or hints, please let me know.

Thanks