Author: Rob Reilly
Setting up the connection is pretty easy: just download the driver and configure a few things. You’ll be able to connect with both Writer and Calc. I’ll cover connecting with the word processor, using a PostgreSQL database that I had on my laptop. You can apply the same procedure for Calc.
Installing the PostgreSQL driver
Getting OpenOffice.org to talk to PostgreSQL databases was straightforward. I downloaded the PostgreSQL driver zip file from the OpenOffice.org site.
Then, I made sure to copy the .zip file (without unzipping) into the right place. I used OpenOffice.org1.1.x/user/uno_packages, but OpenOffice.org1.1.x/share/uno_packages might also be appropriate for multiple users.
I DIDN’T unzip the .zip file, just copied it as is.
The only problem I had during installation was that I had to search to find the pkgchk command. Mine turned up in the
/home/rreilly/OpenOffice.org1.1.2/program directory.
Since I was just setting it up for myself (on my laptop), I put the file in the OpenOffice.org1.1.2/user/uno_packages directory and ran the pkgchk program. Pkgchk comes bundled with OpenOffice.org and is their plug-in packaging system. I restarted OpenOffice.org and was all set.
Once the driver file was installed properly, I could select the PostgreSQL type datasource in the OpenOffice.org datasources window.
Connecting with OpenOffice.org’s datasource screens
The PostgreSQL driver works great once you get it installed. Here are the steps I used set up my PostgreSQL datasource.
- Started OpenOffice.com and brought up a new text document
- Clicked on the Tools menu and selected Data Sources
- Selected New Data Source (on the left hand side of the Data Source Administration window)
- Highlighted the “Data Source 1” entry
- Named the datasource
I was running the PostgreSQL server on my laptop with a hostname of curley (192.168.2.40) so I named the datasource curley-pgsql. If you connect to a corporate PostgreSQL server, you could name it something else, like corp-pgsql.
Since my prototype PostgreSQL database, named rreilly, was on my local machine, I just left the Data Source URL blank. It assumed the “localhost” hostname and database named after the user.
Next, I clicked on the Tables tab. The “public” table list appeared and I clicked on the + sign to expand the list of public tables. One of the tables in my database appeared, “states” and I put a check in the view box so it would show up when I accessed it from Writer. Leaving the box unchecked makes that particular table invisible when you view the datasource. You may want to leave some tables invisible to reduce clutter.
Before I left the datasource administrator window, I clicked on the skinny blank tab, next to the general tab. That tab tells OpenOffice.org to require a valid username and password to access the PostgreSQL database. I checked the “required password” box and added “rreilly” to the user name field.
Finally, I clicked the close button to return to my OpenOffice.org document.
I was ready to start pulling data into my Writer document.
Using database data in my document
Getting started using the new datasource was as simple as clicking on the View menu item at the top, and then selecting Data Sources. A new window section popped up at the top of document which contained my available data sources. I picked out the curley-pgsql datasource and traveled down the directory tree to my “states” table. When I clicked on “states”, the user name and password window appeared. I entered my password and then I could see the data appear in nice neat rows, in the middle of the window.
Using the data was as easy as dragging the data I wanted from the data source window down into my documents. After I picked out the row I wanted to drag, an “insert database columns” window appeared. I could insert the data as a table, as fields or as plain text. Most often you’ll insert as a table or text. You can even drag the entire table down if you want.
The “insert database columns” window was especially helpful with text. When I selected text mode, a large field appeared where I could arrange my data and put in additional text. I could also do things like format a date field as mm/dd/yy, or a number field as integers, decimals, etc. When I hit OK, the text (including the selected fields for that row) appeared in my document arranged the way I wanted.
If you have large tables of data, you’ll probably want to use the query functions. Here are some quick ones that I tried.
- Opened the Design Query and Add Tables windows
- Highlighted, then added the “states” table, which then appeared in the Design Query window
- Double-clicked the state_name and state_code,
when then appeared in the Design Query window - Clicked on the “sort” drop-down menu on the state_name column and selected “ascending”
- Clicked the “run query” button at the left edge of the database menu bar (it looks like a stack of disks with a down arrow next to them)
My states were neatly sorted alphabetically in ascending order. Next:
- Clicked File and selected Save As, named it ascend=states, then clicked OK
- Closed the Design Query window
The new query then appeared in the datasource selection window under Queries. Clicking on my query made the data again appear in sorted order.
Pretty slick, right?
There are other lots of options that you can use to build a query, but I just wanted to show you a basic example. If you know SQL, you can also design a query using only the SQL statements.
Using Other datasources in Writer
Lot’s of other options are available when using datasources.
If you keep your address data in the Mozilla address book, you can connect seamlessly to that table by selecting the address book database type. Address book type data connections are read only.
Suppose you have a bunch of spreadsheets in Calc and you want to use the data from them in your documents, to create a table, for example. Just use the spreadsheet database type. You can click the box to the right of the Data Source URL field to get a file directory, then select your spreadsheet. Spreadsheet data viewing and grabbing is also read only.
You can also grab data from a MySQL database. Setting up the driver is a little more involved because it uses an ODBC connection, as well. I haven’t installed the MySQL drivers, so you should Google for that installation information.
Helpful books
These two books contain almost everything you need to know about OpenOffice.org. The information density is high and the prices are modest.
OpenOffice.org 1.0 Resource Kit
By: Solveig Haugland and Floyd Jones
Published 2003
Sun Microsystems, Inc.
ISBN 0-13-140745-7
http://www.getopenoffice.org/main.html
OOoSwitch
By: Tamar E. Granor
Published 2003
Hentzenwerke Publishing
ISBN 1-930919-36-0
http://www.hentzenwerke.com/
Wrapping up
Connecting OpenOffice.org Writer to PostgreSQL gives you many opportunities to streamline your document preparation process. It’s especially useful for high volume, repetitive document production, with variable data.
Now it’s time for you to use your imagination and see how these tools can fit into your business processes. Sit down with your staff and see how they might streamline some tasks by re-using previously keyed data stored on your database servers. Go talk to the database administrators and find out what data are on your corporate servers. Ask them for a data dictionary and then study it. Work with them to set up access and put some of that valuable data to good use making your business successful.
Rob Reilly is a professional technology writer and consultant whose articles appear in various Linux media outlets. He offers professional writing and seminar services on Linux desktop applications, portable computing and public speaking techniques/technology.
