Connect to MySQL in OpenOffice.org

by Vincent Danen | Mar 26, 2007 7:00:00 AM

Tags: OpenOffice, Storage, Databases, Java, OPEN SOURCE, Vincent Danen, OpenOffice.org, database, MySQL, JDBC Driver, Linux Tips Newsletter

2 comment(s)

Takeaway: Vincent Danen tells you how easy it is to connect to databases using OpenOffice.org.

OpenOffice.org is one of the premier office suites available on Linux. It has a lot going for it: word processing, spreadsheets, presentations, and more. It also can connect you to any kind of database that you like, including MySQL, PostgreSQL, and others.

Depending on how you installed OpenOffice.org and what your vendor provides, you may have the capability to connect to a MySQL database already. If not, you can easily download the JDBC driver to use Java (which requires installing a JRE) to connect OpenOffice.org to your database. In fact, using the JDBC drivers is perhaps the easiest way to connect to a MySQL database from OpenOffice.org.

On Mandriva, OpenOffice.org pulls in the required ODBC drivers, so on that platform, you can connect immediately to a database once it is configured externally to OpenOffice.org. If you want to use the JDBC drivers, you can obtain them from MySQL's Web site. To install, download the mysql-connector-java-5.0.5.tar.gz file, unarchive it, and move mysql-connector-java-5.0.5.bin.jar to a convenient location, such as /usr/local or /home/user. Then, with OpenOffice.org running, select Tools | Options | Java. Make sure that OpenOffice.org is aware of your JRE and you have the desired one selected (if there is more than one); then, click the Class Path button. Once the new window opens, click Add Archive and choose the mysql-connector-java-5.0.5.bin.jar file. (Note: The version number obviously depends on which version you downloaded.)

With either the JDBC driver or the ODBC drivers installed and ready, you can initiate a connection to a MySQL database. In OpenOffice.org, select File | New | Database. In the Database Wizard, select Connect To An Existing Database and choose MySQL from the drop-down menu; then click Next. Select which method you will be using to connect (ODBC or JDBC) and then click Next again. Using the JDBC driver is arguably easier, as it provides a simple way to configure the connection. A window will open that allows you to define the name of the database, the server URL, and a port number. Using ODBC, you must have first created the ODBC data source by configuring /etc/odbc.ini or a similar file.

Continuing with the JDBC connection method, once you click Next again, you will need to provide a username to connect with and specify whether or not a password is required for that user to connect to the database. You can then register the database to make it available to OpenOffice.org and open it. Select Tables in the new window, provide the password to connect to the database, and then you'll be able to view and change entries in your chosen database. Data entry is similar to Microsoft Access; however, with this you have a full-featured database that isn't restricted to a single application.

To open later connections to the database, simply open the saved document with all of its settings.

Delivered each Tuesday, TechRepublic's free Linux NetNote provides tips, articles, and other resources to help you hone your Linux skills. Automatically sign up today!

2 comment(s)