CODEWITHSUNDEEP
h2
Lakatos Gyula
Lakatos Gyula

Reputation: 4160

Select in H2 but with database prefix

At work, I had an assignment to query data from multiple MSSQL databases that sit on the same host machine, with just one query. It works fine, looks like something like this:

SELECT
    user.name as userName,
    product.name as productName
FROM
    userDb.dbo.userTable user 
    INNER JOIN productDb.dbo.productTable product on user.id = product.buyer
WHERE user.id = ?

However, when I wanted to extend the end-to-end testing solution that uses a mock H2 database, I realized that H2 doesn't support databases like that. The query fails with "Schema productDb not found". While productDb should be the database, the schema is dbo and the table is productTable.

Is there a way to circumvent this in H2, or I'll need to do two separate calls to the separate databases, then merge the results together, just for testing?

Upvotes: 0

Views: 492

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

If names within both userDb.dbo and productDb.dbo are unique, you can create all tables in the one H2 database and append ;IGNORE_CATALOGS=TRUE to JDBC connection URL.

(You can also use two separate databases and create links for each table with CREATE LINKED TABLE command, but linked tables may be slow.)

If you have tables with the same names (from different databases), there will be no way to distinguish them in H2, join like … userDb.dbo.table1 JOIN productDb.dbo.table1 … will reference the same table dbo.table1 twice.

You can also use different schema names (dbo) in your databases, in that case you can create them both in the same database, productDb.productDbo.table1 and userDb.userDbo.table1 will have different meaning (productDbo.table1 and userDbo.table1).

Anyway, if you use only one DBMS in production you normally should use the same DBMS in test cases, preferably with the same settings. Otherwise you will run into different incompatibilities from time to time and in some cases code that works in your tests may fail or even silently return different results in the production database. There could be various valid use cases for some tests with another DBMS when logic isn't database-specific at all, but even in them some deviations may appear.

Upvotes: 1

Related Questions