5

When you connect to an embedded local H2 database like so:

jdbc:h2:./target/data/CATALOGA;

H2 uses the database name (CATALOGA here) as the CATALOG_NAME. This can be seen by querying the INFORMATION_SCHEMA.CATALOGS table.

Is it possible to have more than one catalog?

You cannot insert into the INFORMATION_SCHEMA and H2 does not allow for CREATE CATALOG statements.

Background information is that we have queries that join across catalogs. So something that looks like:

select * from CATALOGA.dbo.example e
    inner join CATALOGB.dbo.example2 e2 on e.fk = e2.fk

The queries have catalog/schema in them directly and I need to be able to execute them on H2.

FGreg
  • 14,110
  • 10
  • 68
  • 110

1 Answers1

3

For H2, a catalog is a database. In H2, you can create multiple schemas within a database, but not multiple catalogs.

Of course you can create multiple databases, but I guess that's not what you want, because databases are independent. You can link a table in another databases using the "create linked table" feature, but the linked table is still in the same schema.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • I noticed the linked table feature but the docs say only simple selects are possible. I think then it will not work in my scenario since I need to join on them correct? – FGreg Aug 03 '14 at 18:51
  • I wonder, where in the docs did you read that only simple selects are possible? That sounds like a documentation bug. It's true that joins may not be as efficient as when you execute them in the real database, but joins are possible (and updates, deletes, inserts). – Thomas Mueller Aug 04 '14 at 05:46
  • I think I may have mis-interpreted the statement. The [Linked Tables](http://www.h2database.com/html/advanced.html#linked_tables) section says `Only simple statements are executed against the target database, that means no joins.` I took that to mean you can't join to a linked table. – FGreg Aug 04 '14 at 12:39
  • I see. I will update the documentation and add "queries that contain joins are converted to simple queries" – Thomas Mueller Aug 04 '14 at 13:10
  • I wonder if it's at all possible to have schema names with a period? I'm in the same situation. A vendor is using OpenEdge with supplamental databases that are qualified with pub.mfg. trying to setup a development db with h2. – Brett Ryan Mar 23 '16 at 22:55
  • @BrettRyan no, I don't think that's possible. – Thomas Mueller Mar 24 '16 at 14:46
  • @BrettRyan The SQL standard says quite explicitly that a period is the delimiter between a catalog and schema and table/etc. (For example, section 5.4 “Names and identifiers” in SQL 1992.) Crazy to put in a schema name. – Basil Bourque Apr 14 '16 at 00:06
  • So, in formal language… the answer is that of the `CLUSTER` > `CATALOG` > `SCHEMA` > `TABLE` hierarchy defined by the SQL standard, H2 defines the last two (schema & table). The currently open database is the one and only database available in the context, so effectively no `CLUSTER` or `CATALOG` (current db is implicitly *the* catalog). – Basil Bourque Apr 14 '16 at 00:10
  • 1
    Yeah, was just being hopeful. I think that for some of us we use h2 for a test development db for a production and qa db that supports multiple catalogs. It would be very handy to somehow replicate this even if it wasn't a true catalog. – Brett Ryan Apr 14 '16 at 09:15