Hooking OOo to MySQL

Did you know that you can connect the OpenOffice.org office suite to a MySQL database? By combining the power of these two open source applications you can do things like generating personalized sales letters from the customer data stored on your servers. Let’s walk through the process using OOo’s database wizard.

• Start OOo and click File, New, and Database.
• On the Database Wizard screen, click the “Connect to an existing database” button and use the drop down “Database type” menu to select MySQL. Click Next.
• Click on the “Connect using JDBC” radio button (it’s the easiest driver to use), followed by the Next button.
• Add a database name and the server URL. For a MySQL database that is running on your own machine, use localhost. I used the default MySQL JDBC driver class of com.mysql.jdbc.Driver and it worked fine. If you want to access a database on another machine, your network or database administrator can give you the necessary details. Click Next.
• Type in the user name for the database and check the “Password required” box. Obviously, you’ll need a user name and password to get to the MySQL database. Click Next.
• Now you can finalize the settings for the database wizard and register your database within OOo (post your database name to the internal OOo connection listing). If you have write access to the MySQL database tables, you can also check the “Open database for editing” box. If you have write access and can’t edit any fields, make sure that the table you want to access has a primary key (not null and unique) defined. Click Finish.
• Save the setup to an OOo Base file.
• After you save the file, the Base application will automatically open and you can start to work with the database records. The next time you log in, it will ask you for your user name and password.

OpenOffice.org is now connected to the MySQL database using the new Base application. You can look at the records in your database by clicking on the Tables icon (under the Database pane) and then the table name (under the Tables pane). Your data will appear as a table that you can navigate with the mouse or arrow keys.

Putting MySQL data to use in Writer or Calc

Once you’ve built a connection to your database, Writer and Calc will know about the connection whenever you start them up. Pulling data from your database and using it in a Writer document is easy.

• Select New, then Text Document. The following procedures work almost exactly the same way for Calc.
• Select Data Sources under the View tab.
• Click on the database that you set up in Base. Click the little “+” box to the left of the Tables icon, to show which tables are available. Click on the table name to make the records appear.
• To copy a field, click and hold the record field and drag it into your document. Release the mouse button to paste.
• You can also click the box at the upper left corner of the table (just to the left of names) to highlight all the fields, then click the “data to text” button (text with little right arrow to the right of the funnel-shaped icons). The resulting window will let you drag fields down as a table, fields, or text.
• Click the Text radio button, then select and arrange the fields however you like.
• Click OK and the contents of each record will be copied into the document. This is a great way to make a list of addresses. You can use this technique with other types of data to make basic text-oriented reports.

If you want to copy only a row of data, highlight the the box to the left of the first field and click the “data to text” button. All the data in the row will be copied into your Writer or Calc document.

If you’d like your data to show up as a table in your document instead of as formatted text lists, use the Table radio button under the “data to text” button.

If this introduction whets your appetite for integrating database information into your documents, you’ll be happy to hear that OpenOffice.org provides more wizards to create queries, forms, and reports.

Connecting a MySQL database to OpenOffice.org is easy, especially by using the JDBC (Java) driver. However, with version 1.9.79.2.3, I was unable to design queries or forms from within Writer or Calc. My workaround was to set up those items using Base. I could then switch to and from my Writer document using the Window tab on the main OOo toolbar. That glitch aside, the connectivity worked without a hitch. You can use it effectively even if you aren’t a super-techie.

Rob Reilly is a consultant, writer, and commentator who advises clients on business and technology projects. His articles regularly appear in various Linux and business media.