Home

Biostratigraphy

About StrataData

Staff & Associates

Contact

Blog

Legacy charts (sbchart) with H2

You can configure the legacy chart application to run with StrataBugs 2.1 using an H2 database. You will need to install the PostgreSQL ODBC driver which the application will use to connect to the H2 database.

Installing the driver

Download a PostgreSQL driver. We have found that for an unknown reason the latest driver 09.06.x does not work, producing an error when you start the chart program, so use an earlier driver like this one. The full list can be seen on this link. The drivers are listed newest last. You will download a file with a name like psqlodbc_09_05_0400.zip. Be sure to extract the x86 (32-bit) version. Extract the contents of the zip file and run the installer psqlodbc-setup.exe. Install the driver with the default options.

Configuring the driver

Start the 32-bit ODBC Administrator. You can open this from the ODBC button on the StrataBugs Add Connection dialog. (You will need to temporarily select the ODBC option in order to enable this button). Add a new data source which uses the UNICODE PostgreSQL driver (you may already have a default PostgreSQL data source installed). Make sure you set the path to the H2 database file in the driver configuration dialog:

 

The path to the H2 database will, by default, be in the StrataBugs\Data folder. You should take the folder and file name of the h2 database, remove the .h2.db suffix, and also append the clause "ifexists=true" to the end. For example, the demo database installed in the following folder on drive C:, which is the file:

C:\StrataBugs2-1\data\sbugs2-1_demo.h2.db

... the Database parameter would be

C:\StrataBugs2-1\data\sbugs2-1_demo;ifexists=true

Hint: you can copy the Data source/file name from the field in the Connection dialog, and paste it into the Database field of the driver setup, then append ";ifexists=true".

The name of the data source is then used as the ODBC name in the Legacy Charts Connection setting of the Add Connection dialog. The default will be PostgreSQL35W, but you can use any name as long as the name is used consistently in both places.

The User Name field must contain an ID which has been set up in the H2 database, and this ID must have ADMIN privileges. This is set up in the demo database as sbugs_user with a password of 'system'. If you need to create a new user in H2 the SQL command would be: create user SBUGS_USER password 'system' ADMIN. This user ID is not connected with the users created within StrataBugs. Note: the User Name in H2 is not case sensivitve, but the password is case sensitive.

The Port number field should be changed from the default value to 5435. You may need to configure firewall settings to allow traffic on this port if your connection is unsuccessful.

You must also check off the "Server side prepare" option on page 2 of the Datasource options to avoid "error during prepare" errors:

If you press the Test button at this stage, you will get an error message "No connection could be made because the target machine actively refused it". This is because the server that the driver requires is started by StrataBugs. If you run StrataBugs and start SBchart from the File menu, then this test should also connect successfully.

When you use the option from the File menu of the StrataBugs control panel to start SBChart, StrataBugs will start the server required to support the driver, and the chart application should open. If it fails to open, use the Test button in the driver configuration to obtain an error status. An error "Communication closed during authentication; Socket has been closed" can indicate that the User Name or password are incorrect. An error "Connection sucessful but schema invalid" can indicate that the file name you entered for the database name is incorrect.

Note that if you close the StrataBugs control panel while the chart program is still running, it will close the server and the chart program will not run without errors, so always close the chart program before closing StrataBugs.

Note also that if you have an H2 server process running on your machine, the chart program may fail to connect reliably to the database.