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?

I'm not the downvoter, but what I would advise is the following: you write unit tests to make sure the code you wrote will run fine when deployed in production. Your production database is Sybase. You're using Sybase specific features. If you want your unit tests to be useful, then test against Sybase, not against Derby or HSQL. — JB Nizet, Mar 17 '14 at 07:06
Thanks @JBNizet, i understand what you are trying to say here, but I am not actually testing "Sybase", I am actually trying to test out the validity of a query, which incidentally uses Sybase. I am planning to start up 2 instances of H2 or something similar to test this out! But will be interested to know what people out there use! — Achow, Mar 17 '14 at 08:30
@MichaelGardner, fair enough! Sybase ASE 15 is what I am using — Achow, Mar 27 '14 at 05:04

score 0 · Answer 1 · answered Mar 17 '14 at 21:31

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

Cross database in memory unit testing

1 Answers1