08 Nov 2008
openoffice.org

Connecting to mySql from OpenOffice.org on OSX

I tried to have a play with the OpenOffice 3.0 db app to see how it would work on OSX with MAMP, as an alternative to phpMyAdmin. As expected with anything Java, that turned out to be a pain the backside, involving jars, missing drivers, mistery classpaths, and the usual badly documented Java hookery-pookery. Eventually I got there, and here’s how.

Actually it quite simple, it’s just that the information is hard to come by.

Step-by-step procedure to set up the MySQL JDBC drivers for OpenOffice.

  1. OpenOffice doesn’t come with mySql drivers, because of licensing issues, therefore you have to go and get them yourself from the mySql website. Currently it is called the MySQL Connector/J. Find and download the latest version, and unzip it. This will create a folder called something like mysql-connector-java-X.X.X – the version I downloaded was mysql-connector-java-5.1.7.
  2. I suggest moving that folder somewhere out of the way instead of leaving it in the Downloads folder (or wherever you save your downloads). I put into /MacIntosh HD/Library/Java/Extensions
  3. Start OpenOffice, and go into Preferences / OpenOffice.org / Java. Click on ‘Class Path…’, ‘Add Archive…’ and select mysql-connector-java-X.X.X-bin.jar from the folder you just moved. In my case that was /MacIntosh HD/Library/Java/Extensions/mysql-connector-java-5.1.7/mysql-connector-java-5.1.7-bin.jar.
  4. Quit and restart OpenOffice.
  5. To work with databases, selects File / New… / Database. This will show a dialog box.
  6. MySQL ships with a test database called, surprisingly enough ‘test’. Let’s try and connect to it. Choose ‘Connect to an existing database’, and select ‘MySQL’ from the pulldown menu below it. Click ‘next’
  7. Tick ‘Connect using JDBC’. Click ‘Next’
  8. Enter ‘test’ as the database name, ‘localhost’ as Server URL. I have set up MAMP to work on the default ports, so I just left OpenOffice’s suggestion, 3306, as the Port Number.
  9. Otherwise, start the MAMP application, click on ‘Preferences…’, then ‘Ports’, make a note of the MySQL port there, and enter that in the OpenOffice box. Click ‘Next’.
  10. Unless you know what you are doing, enter ‘root’ as the username, and tick ‘Password required’. Click ‘Test Connection’. You will be asked for the ‘root’ password – on MAMP, the default is ‘root’. Hopefully, it should all go well. Click ‘Next’.
  11. Ticking ‘Registering Database’ doesn’t register it with an online provider, but internally within OpenOffice. Apparently this is similar to the infamous Windows Registry. I’d be inclined to register it.
  12. Click ‘Next’ and save the connection settings
  13. Now you are ready to work with the db tool which is somewhat similar MS Access. OpenOffice offers some db tutorials to get you started.

How useful is the OpenOffice database tool?

It depends what you use it for. It is very good for data entry, but rubbish for designing tables. I still use it for creating the bulk of the table, without setting a primary key, and then I go into phpMyAdmin to clean it up.

The form wizard, on the other hand, is brilliant. The fact that OpenOffice is platform independent means I can create a quick data entry form on my Mac, set it up the data entry person PC, and within minutes they’ll be starting to populate the database while the rest of the team builds the app around it.