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.
- Download and extract mysql-connector-java-5.0.4-bin.jar.
- Copy mysql-connector-java-5.0.4-bin.jar to C:\Program Files\Java\jre1.5.0_11\lib\ext\ folder.
- Start OpenOffice and select Tools / Options menu item, then select Java from the tree pane.
- In Java Options pane, select the appropriate JRE and select the OK button.
- In OpenOffice, select File / New / Database menu item.
- In Select Database step, select Connect to an existing database radio button, select MySQL from the drop down list, then press the Next button.
- In Set up MySQL connection step, select Connect using JDBC (Java Database Connectivity) radio button, then press the Next button.
- 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.
- Enter the Name of the database (e.g. test), Server URL (e.g. localhost) then press the Next button.
- 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.
- 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.
It looks like I can remove jar files from the class path if I first press the "add folder" button on the class path dialog box, and then press cancel on the "select path" dialog that appears for adding the folder, then the remove button is activated.
ReplyDeleteUnfortunately, I have not been able to get open office to load the jdbc library yet, after following your instructions.
ok - I was able to get this working. When open office says that you need to restart it for it to take effect, simply selecting exit on the menu bar is not necessarily enough. If you have the open office quick start program running on the windows toolbar, you need to actually stop that and restart for the class path changes to take effect. I actually did a reboot and now the connection works. Thanks for writing this up!
ReplyDeleteNo problemo. I turn off Quick Start because it's confuses me after I change some configuration and nothing seems to work.
ReplyDeleteFinally thanks this fixed my setup. It didnt save when I would try to add a classpath oh well this works thanks
ReplyDeleteThis is awesome! Every other bit of info I found was for linux.
ReplyDeleteOne problem. I'm trying to connect to an online database but I keep getting connection denied. Any tips?
Thank you so much for this!
icekat, here's some things to try:
ReplyDeleteTry using "telnet hostname port" to see if you can establish a connection to the remote database server. Maybe your server is using a different port or there is a firewall blocking your connection.
Is the connection string correct? Try to connect using a third-party Java database browser such as DbVisualizer or SQLDeveloper and see whether you get a more informative error message.