Connecting OpenOffice.org to MySQL has been well-documented for some time. For those who use PostgreSQL, however, finding good documentation on using OpenOffice.org with it has been like finding a needle in a haystack. Fortunately, it's much easier now.

I don't want to try to write yet another howto, when there is a perfectly good resource available at the OpenOffice.org wiki. I followed that tutorial step-by-step with no problems. But a good overview of using these tools together should help those who might be trying to transition to a new environment. You should also be aware of some of the issues you'll face when using OpenOffice.org with PostgreSQL using the SDBC driver.

For those who are still trying to feel their way along in the dark, OpenOffice.org is a powerful office suite with the ability to connect to a Relational Database Management System, such as MySQL or PostgreSQL, from which to pull data directly into your documents. This means you can use a serious database, with all it's advantages, without having to write a front-end program from scratch to make it 'pretty' to use, especially if you are not a programmer. And, if you are a programmer, you can still do that, too, but give your users a way to pull the data directly into their documents.

Note: If you are not familiar with Database terminology (don't know what a schema is, etc.), you really need to read the documentation for the database system you are deploying. I am assuming here that you are a fairly advanced user, or at least adventurous enough to learn a little about the software you want to use. Preferably, you even know how to install and manage PostgreSQL. If not, you can do like I did and read the manual.

In this case, we are talking about using OpenOffice.org with PostgreSQL. If you do not already have PostgreSQL installed with your GNU/Linux distribution, then you can easily do so using your package manager. Version 8.1 is better supported, and thus preferred. For users of Debian-based distributions, it's a simple matter of using Apt, Adept or Synaptic to find and install PostgreSQL. For RPM-based distributions, it's a matter of using Yast, YUM, or similar. Once installed, you you should look in /usr/share/doc for the documentation to get PostgreSQL up and running.

Once you have PostgreSQL up and running, and you have created the appropriate user(s) and database, you then need to download the SDBC driver for OpenOffice.org. This link also offers some important information on using the SDBC driver. For example, the current driver version, 0.7.5, is considered 'beta' quality, but works very well. Additionally, you should read the Known Bugs at the bottom of the page.

Now follow the instructions in the first link I gave you to create the connection between OpenOffice.org and PostgreSQL. Be aware that where the instructions refer to "Extensions Manager", you may want to look for the "Package Manager" in the Tools menu.

You can now change the table schema, as well as the name, so long as you are using PostgreSQL 8.1 or later. In order to modify the data in a table using OpenOffice.org's Beamer (the database navigator), your tables must have a primary key, and it must be selected as part of the query. Also, because PostgreSQL does not offer the serial (auto increment) data type, you must use the int or big int data type, and set the auto increment flag to 'True'.

You can only update the result set on a single table. Thus, if your query relies on multiple tables for the result set, you will not be able to modify that data. That is a huge limitation, but Rome wasn't built in a day, and I'm sure this will be overcome in due time. Oh, and the Table Wizard does not yet work with PostgreSQL. So you get to create your tables the old-fashioned way - manually. And, if you need to change a column type or size, you will need to delete the column and re-create it with the new type and/or size. Any data you have entered will be lost.

OpenOffice.org's table creation tool does not necessarily choose the correct data types via SDBC. I had to make sure I chose the varchar type, instead of the default character type. PostgreSQL gave an error when I initially attempted to create a table that way. Choosing the varchar type worked. If you attempt to use a type not supported by the database server, you'll get an error.

The gotchyas simply mean you will want to work with some test data before taking your database live (adding real data). Doing so allows you to correct errors before you have the opportunity to lose real data. At any rate, you should now find it very easy to connect OpenOffice.org to PostgreSQL 8.1, and create and use databases to your heart's content.

For fun, I created a simple database to enable me to start keeping all my sermon notes in the database, and then dragged the cells I wanted from the Beamer into a Writer document. I can both easily track my sermons, as well as print out the notes to take with me into the pulpit. I need to improve the back-end design, but now I have a way to use it without having to be a programmer. At the same time, I can still play with the more advanced functionality of PostgreSQL on the back-end when and where I need that.

So you should now be ready to download the PostgreSQL database driver for OpenOffice.org, plug it in using OOo's Package Manager, and create and edit databases to your heart's content. You know some of the issues you'll face when using OpenOffice.org with PostgreSQL. And you also know you can drag and drop cels (and columns, too) into your documents to help eliminate some (often redundant) typing.