Page 1 of 1

Migrate a database from OpenOffice Base to MySQL

PostPosted: Thu Mar 22, 2018 12:40 pm
by Twistellar
I'm new in OpenOffice. Can anyone suggest the easiest way to migrate a database from OpenOffice Base to MySQL?

Re: Migrate a database from OpenOffice Base to MySQL

PostPosted: Sun Mar 25, 2018 7:36 am
by mgroenescheij
For table migration just create a new odb file and connect it to the MySQL database, then copy and past the tables from the old odb file to the new odb file.
In Base you make use of Queries you can copy the Query the same as tables, but keep in mind that a remote database could be less efficient with queries when you have large a database.
It's more efficient to use a view created in the MySQL database, this way you reduce the number of records to be transmitted.
Note that the sql text in Base is quoted with " while MySQL use ` you need to edit the sql text before you create the view.

Re: Migrate a database from OpenOffice Base to MySQL

PostPosted: Tue Nov 20, 2018 4:10 pm
by mehorter
As far as quoting in OO vs MySQL, there is an option in MySQL that will allow quoting with " as is done in OO.
see I am not sure of the full implications of using this option, I just know it exists.Basically, one can add this to /etc/my.cnf
Code: Select all   Expand viewCollapse view
In my install there were, by default, other sql-mode options so I appended the existing line the additional option. I used just ANSI for the other options that provides. Now my /etc/my.cnf reads:
Code: Select all   Expand viewCollapse view

I am also working out how to migrate to MySQL as well and I have found this post to be helpful.

Running the SCRIPT command will produce a file with all definitions and INSERT statements of your current HSQLDB database.
Read all about that at

script statement

<script statement> ::= SCRIPT [<file name>]

Returns a script containing SQL statements that define the database, its users, and its schema objects. If <file name> is not specified, the statements are returned in a ResultSet, with each row containing an SQL statement. No data statements are included in this form. The optional file name is a single-quoted string. If <file name> is specified, then the script is written to the named file. In this case, all the data in all tables of the database is included in the script as INSERT statements.

Only a user with the DBA role can execute this statement.

I use the following to achieve this.
Code: Select all   Expand viewCollapse view
#Create a backup script with insert data of hsqldb. Script will not clobber existing script file, you must rm the old one before creating new one.

rm '/path/to/resulting/script'
java -jar /path/to/sqltool.jar --sql="SCRIPT '/path/to/resulting/script';" database_name