Using the default HSQLDB database management system (DMS) in LibreOffice Base is great for creating new databases, but sometimes you need to connect to an existing database on another DMS like MySQL, dBase, JDBC, Oracle, PostgreSQL, and others. I showed you how to connect to a simple spreadsheet database back in Issue 52. Today, I will show you how to connect to a database created in MySQL.
MySQL is a DMS used on many websites. Wordpress and other content management systems use MySQL for their databases. You might have many reasons to access a database created in MySQL. MySQL is open source, and you can install it from your software manager.
Install MySQL
Installing MySQL from your software manager requires the installation of two packages. The first package is “mysql-client” – the client program for MySQL. It allows you to interact with MySQL.
If you are installing MySQL on your current machine, you will need to install the MySQL server package as well: “mysql-server”. When you install the server package, it prompts you for a “root” password and asks for confirmation. This is your master password for MySQL. Jot it down somewhere. You will need it later. If you are connecting to a database on a different machine, you don't need this package.
The last package we need to install is “libreoffice-mysql-connector”. This package will allow us to connect to MySQL without using an OBDC or JDBC wrapper.
Create A Test MySQL Database
If this is the first time you are connecting to an external database using LibreOffice Base, I recommend you create a test database in MySQL where you can make mistakes and learn. You can install a program like PHP MyAdmin or EMMA, but I think it is easier just to use the MySQL command-line client.
To connect to MySQL on your local machine, use the command:
mysql -h localhost -u root -p
MySQL will prompt you for the “root” password. Enter it and you are greeted with a welcome message and the “mysql>” prompt. To connect to a MySQL server on another computer, replace “localhost” with the server name or IP address. Keep in mind that you will need to set up MySQL to accept remote connections. For details on setting up MySQL to accept remote connections, read this tutorial: http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
To create a database use the following command at the MySQL prompt:
CREATE DATABASE <dbname>;
where <dbname> is the name you want to give the database. To create a databases name “myTestDB” enter:
CREATE DATABASE myTestDB;
MySQL responds with a message that the query was executed. Enter
SHOW DATABASES;
to generate a list of all the databases. You should see your new database among those listed. An empty database is all we need to connect to MySQL from Base, but you can also connect to a fully functional database as well; but, like I said, it is better to practice on a test database first. Type “quit” and press Enter to exit your MySQL session.
Connect Base to MySQL Database
Now for the part we have been working for – actually connecting Base to the MySQL database. Start by creating a new database document in LibreOffice, File > New > Database. We will use this document to connect to the database we created before. Using the database wizard (recommended), it is a five step process.
Step 1: We want to connect to an existing database. Don't confuse this with connecting to an existing database file. You want the last selection. From the drop down list select MySQL. Click Next.
Step 2: Since we installed the “libreoffice-mysql-connector”, we have no use for the ODBC or JDBC connections. Select “Connect directly”. Click Next. Step 3: The database is the name of the database we just created in MySQL. Our example database was “myTestDB”. The server is “localhost” for the local computer, or the server name or IP address of another computer. Leave the Port as the default unless you changed it in your MySQL settings or have been told to do so by your IT department. Click Next. Step 4: The user name you need to connect to MySQL from your computer. Use “root” if you are connecting to MySQL on your local computer. Check “Password required”. Click “Test Connection”. You are prompted for your password. Enter your password and click OK. If you set everything correctly, you will get a message back saying Base successfully connected to the database. OK on the message and click Next. Step 5: Select Register or Don't Register according to your needs for the database. A registered database is available in other LibreOffice modules. Check “Open database for editing”. Click Finish. Base will prompt you for a file name. Enter a name and click Save.
You can now interact with the MySQL database in the same way you would an HSQLDB database. You can create tables, queries, reports, and forms just like you would with the default DMS. You will need to pay attention to the data types as they are slightly different in MySQL. For an overview of the HSQLDB data types see my article at http://wp.me/pvwht-8Q. For a list of MySQL data types see http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html.
While using the default DMS for Base is good for new databases, sometimes it becomes necessary to connect to existing databases. In this article, we showed how this is done by connecting to an external database we created in MySQL. We installed MySQL and created a blank database. Using the database wizard in Base, we connected to that database where we can create tables, forms, queries, and reports to interact with the database. Base can also connect to other database types like dBase, JDBC, Oracle, PostgreSQL, and others.