Skip to main content

Content Starts Here

How to connect to the in-file H2 database?

Question
How to connect to the in-file H2 database?
Answer

Please follow the below instructions in order to connect to the in-file H2 database.
 

Verifying the driver version

Since ReadyAPI uses the H2 database files to store tests results, the ReadyAPI installation already has the JDBC driver file for the H2 database. Note that, before ReadyAPI version 2.4, we used H2 database version 1.3. Since release 2.4.0, we use H2 database version 1.4.

 

Configuring the connection string
 

Add the following driver properties to the "File -> Preferences -> JDBC Driver Properties" preferences:
Driver: H2/org.h2.Driver
Connection String Template: jdbc:h2:file:<HOST>/<DB>;user=<USER>;password=<PASSWORD>
When configuring the connection, you will need to specify the path to your DB file as the Host value.

If your DB files are located in the default location, i.e. in the user directory, you can use the following connection string template with a tilde after 'file':
jdbc:h2:file:~/<HOST>/<DB>;user=<USER>;password=<PASSWORD>
and specify the relative path to the user directory as the Host value when configuring the connection.

The details about the Database files location can be found here: http://www.h2database.com/html/faq.html#database_files

Also, please note that you may need to add the mv_store parameter, so the connection string will look like this:
jdbc:h2:file:<HOST>/<DB>;user=<USER>;password=<PASSWORD>;mv_store=true
 
User-added image

This will depend on the version of the H2 database which you use. For example, if you use version 1.3 and you need to connect to the .mv.db file, you will need to set the mv_store option to true. Without this option, ReadyAPI will create a database file with the same name but an .h2.db extension. This will show a successful connection, but it is actually accessing a new database. You will not be able to query or see any data located in the .mv.db file.

Please see the below table which shows the correspondence between the Database version and the Database file which you need to access.
 
Database file which you need to accessH2 database version
1.31.4 and newer
<databaseName>.h2.db-mv_store=false
<databaseName>.mv.dbmv_store=true-

The details can be found in the following articles:
-    https://stackoverflow.com/questions/27096363/how-to-access-mv-db-file-of-h2-database;
-    http://www.h2database.com/html/mvstore.html (see the "Storage Engine for H2" section).
 

Configuring the connection
 

Create a connection string in the Database Manager or directly in the JDBC request Test Step using the following instructions: https://support.smartbear.com/readyapi/docs/testing/data-driven/drivers/manager.html.

If you specified the following connection string template in the JDBC driver properties (without a tilde):
jdbc:h2:file:<HOST>/<DB>;user=<USER>;password=<PASSWORD>
then, you will need to specify the full path to the database file as the Host value, the database name, and change the username and password values to your credentials. Please see the example (with an empty password):

User-added image
User-added image

If you specified the following connection string template in the JDBC driver properties (with a tilde): 
jdbc:h2:file:~/<HOST>/<DB>;user=<USER>;password=<PASSWORD>
you will need to specify the relative path to the user directory as the Host value. Please see the sample screenshots:

User-added image

User-added image
 

Verifying the connection
 

Test the connection and make sure that it can be established successfully. 
 
User-added image

After this, you can build a query to obtain data from your database and use this data in ReadyAPI tests, for example, in the JDBC test step.
Previous MonthNext Month
SunMonTueWedThuFriSat