Using MySQL with OpenOffice.

Installing MySQL Connector/J

Many people use phpMyAdmin to configure MySQL but I also find it useful to be able to use MySQL from withing OpenOffice. Once it's configured you can retrieve data into any OpenOffice application using the data sources menu (just press F4). First get the MySQL Connector/J 5.0 from the MySQL site, it's GPL licensed. Save it to your home folder then extract the file to the desired location on your computer, it can be anywhere but I usually put it into my /opt/ folder like this:

$ sudo tar -xzvf mysql-connector-java-5.0.4.tar.gz -C /opt/
  1. Next open OpenOffice by typing ooffice, click on Tools > Options then expand the OpenOffice.org tree and select java
  2. Click the button 'Class Path' and then 'Add Archive'
  3. Browse to the .jar file located in the install folder (/opt/mysql-connector-java-5.0.4/ in my case), select the file then just click OK

You will need to restart OpenOffice to see the changes.

Using Sun JRE with OpenOffice

I would also recommend using Sun JRE if you wish to use this connector, I was using the Free Software Foundation java that comes installed with Fedora Core but OpenOffice would crash frequently when getting data from MySQL, after changing to Sun JRE it's never crashed.

If you have installed the Sun JRE according to the installation instructions in order to use the java plugin for Firefox then it will be installed in your /usr/java/ folder. To use this go to the java settings in OpenOffice as shown above but click 'Add' and navigate to the /usr/java/ jre folder (/usr/java/jre1.5.0_10/ in my case), select the folder and click OK to add this installation. Then just select the radio button next to the 'Sun Microsystems' entry as seen here.

OpenOffice java

There was a problem in Fedora Core 4 that meant you could not select a different JRE, when you selected the folder it would just say the folder doesn't contain a valid JRE (you can see the bug report in Red Hat Bugzilla). If you read comment 4 on the bug report it explains how to get around this issue by downloading the javavendors.xml file and putting it into your /usr/lib/openoffice.org2.0/share/config/ directory.

Connecting to an existing MySQL database with OpenOffice

To create a new OpenOffice database based on a MySQL database first open OpenOffice Base either from the menu or typing oobase in a terminal. Select 'Connect to an existing database' at the bottom and select MySQL from the dropdown box.

OpenOffice and MySQL

Click next and from this screen make sure 'Connect using JDBC' is selected.

OpenOffice and MySQL

After clicking next again add the database name, this is the name of the MySQL database you will connect to, URL will probably be localhost if the MySQL server is on your own computer and leave the port as 3306.

OpenOffice and MySQL

Click next and enter your MySQL username to log in, it's probably a good idea to test the connection here to check everything is OK. If all is fine just finish and save the database. From now on you can use this database to administer MySQL. Note that you will be prompted for your password every time you close and re-open the database but the password will be saved during a session if you make sure to click the option on the password dialog.