Converting a Database to v3.0

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.

This guide explains how to convert a v2.1.1 h2 database using the v3.0 Database Utility tool. To start this process you should have an installed version of 3.0, which is licenced, and includes the Database Utility.

If your v2.1.1 database platform is a server database or not a single user h2 file, or is an older version than v2.1.1, then you will need additional steps before you start this process. Contact StrataData for details.

1. Open the Database Utility

If you have no previous v3.0 connections, then you will be prompted to open the Database Utility when you start 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.

2. Connect to a v3.0 database

With the database utility open, add a new connection. This will create a new, empty database.
The connection name must be unique. The database platform is H2.
Press Select database file… and navigate to a suitable folder (perhaps \Data in your StrataBugs folder, or in a location that will get backed up). Use a suitable file name for the database file e.g. sbugs3-0 (you do not need to add a file extension).

You don’t need to add any more parameters on this dialog, except perhaps to click the "Prompt for backup" option.

3. Create tables

At the main window, the target connection should show a connection to an empty database.

Select the option to "create tables" (1), and then press the start button (2).

Create tables

The table creation script should complete:

Script ran to completion

The v3.0 database is now ready to receive your data.

4. Select source connection

Select the option to "Copy data from another database".
Select the source connection type as v2.1 or v2.1.1.
Select the v2.1 connection. If you have been using v2.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 v2.1 folder, or wherever you have copied it to on your system.

3 0 conv4

5. Copy data

Press the start button to begin the table copy.

You will see a table-by-table comparison, where all the "target Rows" (2) should be zero. If there are data to copy from the source table, the number of rows will be shown (1); the tables that are "not found" are new in v3.0. Some tables will not be selected if they have no data in the source table.

You can pre-select a reduced set of tables to create a partial database with dictionaries and schemes but exclude any well our outcrop data from the copy (3). You can also opt to select individual tables for copying (using the checkbox in the select column), but since the tables are copied in order to avoid violating any foreign key constraints, it is generally not recommended to switch tables on or off here.

Press Copy (4), and the data from each table will be copied 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 messages 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 v2.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 latitude or longitude values being out of range. To find affected wells, 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, and then return to the copy dialog.

At the end of the copy, you will see a confirmation message:

Copy complete

6. Compact the database

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:

3 0 conv7

This is only available for h2 databases.

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