Migrate a database from OpenOffice Base to MySQL

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
Twistellar
Posts: 1
Joined: Wed Mar 21, 2018 5:40 pm
Location: USA

Migrate a database from OpenOffice Base to MySQL

Post by Twistellar »

I'm new in OpenOffice. Can anyone suggest the easiest way to migrate a database from OpenOffice Base to MySQL?
Use OpenOffice 4.1.0 for Win 10
mgroenescheij
Volunteer
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Migrate a database from OpenOffice Base to MySQL

Post 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.
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Migrate a database from OpenOffice Base to MySQL

Post 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 https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html. 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

sql-mode=ANSI_QUOTES
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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ANSI
I am also working out how to migrate to MySQL as well and I have found this post to be helpful.
viewtopic.php?f=13&t=54182

Running the SCRIPT command will produce a file with all definitions and INSERT statements of your current HSQLDB database.
Read all about that at http://hsqldb.org/doc/guide/management- ... operations
SCRIPT

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

#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
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
Post Reply