Cross database in memory unit testing

Question

Objective:Unit Testing DAO layer using any in memory database(HSQL/Derby?) My end database is Sybase, so the query uses cross database joins. Eg. of the query :

select table1.col1, table2.col2 
from db1..table1, db2..table2 
where table1.col1=table2.col2

Obviously, table1 is a table in Database db1 and table2 is a table in Database db2. Is there any way to test this out?

Answer 1

if you use db specific feature (cross db joins) then you have 2 options to prevent failure on other db:

• teach other db to understand the query. for example you can register new functions in h2. this probably won't work in your case. but this way you don't test anything, you just prevent failure and/or allow your application to work on other db
• don't test it on other db. face it, even if your queries run on h2, you still have to run them on sysbase. otherwise you simply don't know if it works

so this way or another you need to run your tests on sysbase. if you don't plan to support other database then what's the point in fighting for making this specific test works on h2? it will have completely irrelevant to your production code.

and answering your question: Is there any way to test this out?

• on sysbase? yes, just run your query and clear your database after. you can use vagrant or dedicated db
• on h2/hsqlbd? probably not