About StrataData

Staff & Associates



v3.0 Converting a 2.1 h2 database

If you have an existing v2.1 database you can only open it in v3.0 by converting the database file to the new v3.0 format.

Note: If your database platform is a server database or not a single user h2 file, then you will need additional steps before you start this process, contact StrataData for details.

To start this process you should have an installed version of 3.0, which is licenced, and includes the Database Utility.

If you have no previous 3.0 connections, then you will be prompted to open the database utility on starting StrataBugs. If you do have an existing connection, then from the StrataBugs control panel, open the database utility from the Tools menu item:

Alternatively, you may be able to run it from a separate desktop icon.

With the database utility open, add a new connection. The connection name must be unique. The database platform is H2 and you should press "Select database file..." to navigate to a suitable folder (perhaps \Data in your StrataBugs folder, or in a location that will get backed up) and use a suitable file name for the database file e.g. sbugs3-0. You don't need to add any more parameters on this dialog, except perhaps to click the "Prompt for backup" option.

Having set up the file, and closed the dialogs, back at the main window you should see the connection to an empty database. Select the option to create tables, and then Start:

The table creation script should complete:

Having created the database, you will not be able to open it without a minimum amount of data and metadata. To copy and convert data from a 2.1 database, you select the copy option (1), then select 2.1, and select the 2.1 connection. If you have been using 2.1 on the same machine, you will already have a connection set up. If not, you should create a new connection to the existing database file in the 2.1 folder, or wherever you have copied it to on your system.


Having created the 2.1 connection, run the copy option. You will see a table-by-table comparison, where all the "Target Rows" (2) should be zero. The tables that are "not found" (1) are new in v3.0. Some tables will not be selected if they have no data in the source table. You can also pre-select a reduced set of tables to create a partial database with dictionaries and schemes but excludes any well our outcrop data from the copy.

Press Copy (4), and it will copy each table in turn. Some tables will copy much faster than others, depending on the amount of data in the source table.


At this point you may receive error mesages if the v2.1 data violates the new data structure or rules. It may be obvious from the error message the source of the problem, and you might be able to open the 2.1 database using v2.1 and edit or delete the offending data. If in doubt, contact support@stratadata.co.uk for advice, copying the error message.

A common error with the WELLS table copy is the lat_long values being out of range. To find the entries, open the 2.1 database, and from Tools | Database query, run this statement:

select well_name, well_code, grid_x, grid_y, lat_dec, long_dec from wells where lat_dec > 90 or lat_dec < -90 or long_dec > 180 or long_dec < -180

Correct those entries shown by editing the well headers in v2.1 then retrn to the copy dialog.

At the end of the copy, you will get this:

After closing the copy dialog, run the option to compact the database, to remove temporary space within the database file created by the copy process:

You can also return to use this option periodially, depending on level of usage. A smaller database size will be more efficient and easier to back up.