01 April 2007

OpenOffice 2.2 Database Connection

OpenOffice.org has released version 2.2 of their office suite. Seems to be just a bug-fix release. Having installed the latest version, I thought I'd connect the Base product to my test MySQL database. It should have been painless but I kept getting a JDBC driver could not be loaded error. After some head-scratching, it turns out that the MySQL JDBC driver had to be copied into the Java Runtime (JRE) ext folder. Here's how I set up a connection between OpenOffice and MySQL.

  1. Download and extract mysql-connector-java-5.0.4-bin.jar.
  2. Copy mysql-connector-java-5.0.4-bin.jar to C:\Program Files\Java\jre1.5.0_11\lib\ext\ folder.
  3. Start OpenOffice and select Tools / Options menu item, then select Java from the tree pane.
  4. In Java Options pane, select the appropriate JRE and select the OK button.
  5. In OpenOffice, select File / New / Database menu item.
  6. In Select Database step, select Connect to an existing database radio button, select MySQL from the drop down list, then press the Next button.
  7. In Set up MySQL connection step, select Connect using JDBC (Java Database Connectivity) radio button, then press the Next button.
  8. In Set up JDBC connection step, press Test class button. If your connector is copied in the JRE's ext, then the JDBC Driver Test dialog should report The JDBC driver was loaded successfully. If the test fails, it will report The JDBC driver could not be loaded. Close the test dialog and continue configuring your database connection.
  9. Enter the Name of the database (e.g. test), Server URL (e.g. localhost) then press the Next button.
  10. In Set up user authentication step, enter User name (e.g. root) then press Test Connection button. If the connection was unsuccessful, the Connection Test dialog should report Access denied for user 'X' to database 'Y'. Close the test dialog and press the Next button.
  11. In Save and proceed step, use the default options then press the Finish button. OpenOffice will prompt you to save the database connection as an .odb file.

Notes: OpenOffice doesn't find the MySQL driver class after setting the CLASSPATH in the Options dialog. Another bug is that I can't remove a wrong JAR entry from the Options dialog.