Cannot browse a H2 database file?

Question

I am creating a H2 database in my unit tests. The database uses the following properties:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
    destroy-method="close">
  <property name="driverClassName" value="org.h2.Driver"/>
  <property name="url" value="jdbc:h2:file:target/db/testdb"/>
  <property name="username" value="sa"/>
  <property name="password" value=""/>
</bean>

I am using version 1.3.166 of the com.h2database.h2 jar file.

When I run my tests, I see the database created in target/db directory, and a testdb.h2.db file exists. My tests run and load data from the database. I can open the target/db/testdb.h2.db file and see the SQL statements that I used to create the database.

However, when I try to load the target/db/testsb.h2.db file into a database browsing tool such as DBVisualizer, I cannot see any tables or data. For DBVisualizer I specify the H2(Embedded) mode.

I also tried the H2 console but a show tables command returns an empty result set.

I can't see what I am doing wrong: the database file exists, the tests run against it correctly, but I cannot open this file in a database browser.

Any suggestions?

Answer 1

Most likely, the problem is the database URL you use.

jdbc:h2:file:target/db/testdb

This means the database file is stored relative to the current working directory. So it depends where you started the application. If you started DBVisualizer in a different directory (which most likely you did), then it's creating a new database in a different directory.

To ensure you are using the same database, I suggest to use an absolute directory name, or relative to the current user home directory, as described in the H2 documentation:

jdbc:h2:~/relative/to/user/home/dir/testdb
jdbc:h2:/absolute/path/testdb

The prefix file: is optional.

Answer 2

If you get an error like

Unsupported database file version or invalid file header in file "Old database:
/path/to/databasename.data.db - please convert the database to a SQL script and 
re-create it." [90048-167]

the problem is (as the message says) that you are using a database created with an old version of h2. In order to browse the database with dbVisualizer you can create a new database driver:

Tools > Driver manager... > Create a new driver

Fill in the fields and select the right h2 jar file, and you should be good to go.