Migrate a database from OpenOffice Base to MySQL
-
- Posts: 1
- Joined: Wed Mar 21, 2018 5:40 pm
- Location: USA
Migrate a database from OpenOffice Base to MySQL
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
-
- Volunteer
- Posts: 300
- Joined: Thu Apr 23, 2009 10:19 pm
- Location: Sydney Australia
Re: Migrate a database from OpenOffice Base to MySQL
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.
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.
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
Re: Migrate a database from OpenOffice Base to MySQL
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 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:
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
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
Code: Select all
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ANSI
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
I use the following to achieve this.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.
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