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?

score 6 · Accepted Answer · answered Apr 20 '13 at 15:11

6

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.

answered Apr 20 '13 at 15:11

Thomas Mueller

48,905
14
116
132

6

Actually the problem was how I specified the file in DBVisualizer. It asked for a database file, and opens a file chooser dialog, and I naturally double-clicked on the `target/db/testdb.h2.db` file. But the correct method is to remove the `.h2.db` extension. Bad UI design IMHO. – John Q Citizen Apr 21 '13 at 21:06
1

I think in the next mayor version of H2 (2.0 maybe) there will be no hardcoded file name suffix. – Thomas Mueller Apr 22 '13 at 05:12
1

@JohnQCitizen You win all my internet for a week! This was the issue. I was using database migrations and couldn't figure out why the tables weren't created. Turned out that removing .h2.db did the trick. Use ui fail! And thanks for the solution! – Maik Apr 15 '14 at 10:45

score 1 · Answer 2 · answered Sep 12 '13 at 14:45

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.

Cannot browse a H2 database file?

2 Answers2