Setting up LibreOffice to access MySQL database with JDBC driver
By: +David Herron; Date: 2016-08-24 13:21
LibreOffice Base is supposed to be an excellent thingymajob for doing database whatever. I don't know, I've never used it. I'm looking forward to using it in the LibreOffice Spreadsheet tool instead.
This was very frustrating, but it turns out to be easily fixed.
Install Java JDK for your operating system
Java does come pre-installed on Mac OS X but it's just a JRE. Turns out that LibreOffice requires a JDK (Java Development Kit) to be installed, rather than a JRE (Java Runtime Environment). I don't grok why that would be the case, but I found multiple sources saying the same thing. I looked at some issues in the issue queue, and it seems there's some fugly history between Apple's Java and Oracle's and ... uh ... that's not important because we just install Oracle's Java and get on with things.
The first step is to go to http://www.oracle.com/technetwork/java/javase/downloads/index.html for the official Oracle Java download page. What you want is the JDK, so click on the JDK Download button, then on the next page select the download archive matching your operating system. Make sure to agree to the license.
This gives you a JDK installer, for your operating system, that you run to install the JDK on your system.
Get the MySQL JDBC driver
You don't install the JDBC driver so much as unpack it on your system, somewhere.
Then download the archive they provide, and unpack it somewhere on your computer. It doesn't matter where.
Install LibreOffice and MySQL
You already have this installed somehow, I'm assuming. Right?
I install LibreOffice using the installer from http://libreoffice.org
I install MySQL using MacPorts.
LibreOffice Java Preferences
This took awhile to find but it turned out to be the critical piece.
For the Mac OS X LibreOffice, you open Preferences and navigate as shown below. For other OS's, it appears you use the Tools menu to get to the Preferences doohickeythingamajob.
Once you have the JDK installed, some choices appear here. Tick the "Use a Java runtime environment" then tick the radio button in the box below. You'll need to restart LibreOffice at this point for the change to take effect.
Once I'd done this the error message while setting up a MySQL JDBC database changed from "No Java installation found" to "the driver class com.mysql.jdbc.Driver could not be found". This will be your clue that you're on the right path.
Adding MySQL JDBC driver to LibreOffice
Again, you don't "install" the JDBC driver so much as unpack it somewhere on your system. The trick is to get the driver onto the Classpath of LibreOffice. It doesn't work to simply set your login
CLASSPATH variable. Of course not, this is Java and nothing is easy.
You'll notice there's a Classpath button in the window. Click on that.
What you do is click the Add Archive button, and add the
mysql-connector-java .jar file you downloaded above. You'll again need to restart LibreOffice.
Setting up MySQL JDBC database in LibreOffice Base
Now we can do the thing we set out to do, setup a MySQL database in LibreOffice Base.
Select MySQL as the type of database.
We'll be connecting with JDBC.
If all goes well, you'll get this message with the Test Class button.
Enter the "Database name" and "Server" in the boxes above.
Next step is verifying you can access the database. Enter the login name and password credentials, and click Test Connection, and if all went well you'll get the above message.
This last step finalizes the database in LibreOffice.
After this you'll be in LibreOffice Base and can do database things there. I don't know enough to recommend anything you can do. I did try a query, and it came up quickly in a nice table.