connecting to H2 database

Question

I have a file called xxx.h2.db My goal is to be able to execute queries in order to delete/edit records (preferably in a C# application).

DBeaver

I have tried opening the file with DBeaver using the H2 embedded option. This results in 2 schema's:

• INFORMATION_SCHEMA
• PUBLIC

Unfortunately the public schema has no tables. I wasn't expecting this result considering the filesize is around 150mb. When opening the database i've noticed that it creates two more files in the H2 database's directory:

• xxx.h2.db.mv.db
• xxx.h2.db.trace.db

C#

While trying to use an ODBC driver as mentioned in this article: https://wiki.postgresql.org/wiki/Using_Microsoft_.NET_with_the_PostgreSQL_Database_Server_via_ODBC

i came across this step, in which i'm unsure what information to enter. Image of ODBC datasource administrator

Once you've installed the ODBC driver you will need to add a new user data source. This is achieved by going to 'Control Panel', 'Admin. Tools', 'Data Sources (ODBC)'. Then selecting 'Add User DSN'. [NOTE: These names and locations may vary slightly with different Win OS's.]

Select the PostgreSQL driver, and fill in your server and database details. You must also specify a unique DSN name; in Windows 2000 this field is, confusingly, labelled 'Data Source' in the data entry dialog box and not 'Data Source Name' which would be more appropriate. It is this name that you will later use in your programs to specify which database connection you want to use. Of course you can have as many User DSN entries as you want for different databases, servers and users.

Questions

1. Why can't i see any public tables in DBeaver?
2. Am i on the right track to get it working trough ODBC? Which information should i enter?

Thanks

Answer 1

1. It looks like you included file name extension into connection URL (xxx.h2.db), but you need to specify path to database without extension (xxx).

   Please also note that .h2.db extension belongs to old storage engine (PageStore), it is still supported, but it has some limitations.

   Additionally you need to make sure that you use the same version of H2 as it was used to create the database file you have, usage of different version may lead to its corruption.

2. Connection to H2 through ODBC with ODBC driver for PostgreSQL is experimental only, it may not work at all. There were some improvements in this area since the last release, but it's not a reliable thing even with H2 compiled from its current sources. Also ODBC connections with this driver now use PostgreSQL compatibility mode of H2, so they can be not really compatible with your existing database. You can also try to use some third-party (possibly commercial) bridge to JDBC for .NET instead of that functionality.