This entry explains how to convert OpenOffice HSQLDB database to a MySQL server which can then be accessed using a front end (OpenOffice base, web gui, etc). This article assumes that a basic mySQL server is running (root password configured, server listening on port 3306, httpd and mysqld running).

Export OpenOffice Base as SQL File Link to heading

  • Open the database in OpenOffice Base
  • Click on Tools -> SQL...
  • Execute export command to a location on disk (I created a directory on the top level of my C drive called temp for this)
SCRIPT 'C:\temp\**database**.sql'

Import SQL File to MySQL Server Link to heading

  • Copy the sql file to the machine that will be the sql server
  • Open the .sql file with your favourite text editor and make the following changes

Replace instances of CREATE CACHED TABLE with CREATE TABLE Replace instances of INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) with INTEGER AUTOINCREMENT Remove the following lines

SET DATABASE COLLATION "Latin1_General"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
...
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
...
ALTER TABLE "**Table Name**" ALTER COLUMN "**Column Name**" RESTART WITH **Integer**
  • Create a new database
[stuart@asuka ~]$ mysql -u **user** -p
mysql> create database **database**; 
mysql> exit;
  • Import the database
[stuart@asuka ~]$ mysqladmin -u **user** -p **database** < **database**.sql

Set Up Front End Link to heading

  • Start OpenOffice Base
  • In the Database Wizard that opens, select 'Connect to an existing database', select MySQL from the drop down and click next
  • Select 'Connect using JDBC' and click next
  • On the next window, input the database name and server ip. Leave port number and driver class on the default settings unless these details have been changed otherwise.
  • Input the username for the database, select requires password and click test connection (you'll be asked for the password of the user and will then get a message about successful or failed connection)
  • If the connection was successful then finish the setup wizard and the database front end should now open
  • From the front end, queries and forms can be created for the database

More info for connecting MySQL and OpenOffice Base here - https://wiki.openoffice.org/wiki/Connect_MySQL_and_Base