Using OpenOffice Base 2.3.1 with PostgreSQL

For those who are not familiar with OpenOffice Base. OpenOffice Base is the equivalent of Microsoft Access in the OpenOffice Open source suite. While it is not as feature rich as Microsoft Access, it has been getting increasingly better and has some unique features that even Microsoft Access lacks. Unfortuantely you can't just convert an access mdb to its format like you can with other Open office suite products - Word to Writer Writer to Word etc. However you can open MS Access databases in OOBase, but you can't take advantage of the forms and reports in an MS Access Database.

One thing I always liked about Microsoft Access was the ease with which you could link to various different kinds of datasources and generate rapid queries and so forth. Microsoft Access has a particular feature called Access Projects which ties it very closely with Microsoft SQL Server. What an MS Access Project does is connect you with a specific SQL Server database and allow you to browse all the objects, create forms and reports etc against the objects etc. Unfortunately MS Access Project only works with SQL Server. For other datasources you need to use linked tables and can't make design changes and browse a database as you can with Access Projects.

We had looked at Openoffice Base a while ago and thought they are making progress, but still not quite good enough to put to daily use. When we revisited Open Office Base recently, we were surprised to find a couple of neat nuggets.

  1. They now had a native SDBC driver for postgresql instead of having to rely on the jdbc or odbc driver. You can still use the jdbc and odbc bridges, and unfortunately for Mac OSX users, you are stuck using the jdbc driver.
  2. They have this Access Project like feature except it was better than Access in that it worked with other server side dbs. Any that had a driver - e.g. PostgreSQL, MySQL etc.
  3. It had a relational designer viewer similar to what Access had and when we opened up a PostgreSQL db it laid out all the relationships we had carefully defined before with foreign key constraints etc.

In the next couple of sections we'll lay out how to quickly setup OpenOffice, install the native PostgreSQL driver and JDBC PostgreSQL driver and connect to a PostgreSQL database in OpenOffice Base. Please forgive us for using Windows paths in this. We figured it would be easier for people to follow since most users coming to this site are windows users and a lot of Linux users already use OO and paths are too different from Linux/Mac OSX installs.

Installing Open Office

  1. Download open office from here and install. It takes about 5 minutes to install after download.

Installing the PostgreSQL Native SDBC driver

Please keep in mind that the PostgreSQL Native SDBC driver only works for Linux and Windows (not Mac), and is of beta quality. Meaning probably best not to fiddle around with a production database or at least have your db backed up.

  1. Download postgresql-sdbc-0.7.5.zip from http://dba.openoffice.org/drivers/postgresql/index.html
  2. Click on "C:\Program Files\OpenOffice.org 2.3\program\soffice.exe". Alternatively just open up any Open Office Writer.
  3. Tools -> Extensions Manager -> Expand Office Org Extensions -> Click Add and point at the postgresql-sdbc-0.7.5.zip file (in earlier versions of Open Office e.g 2.1 and lower - this was under Tools->Package Manager)
  4. Exit soffice and close any quick start soffice task items

Connecting to PostgreSQL from OOBase using SDBC driver

  1. Start -> All Programs -> OpenOffice.org 2.3 -> OpenOffice.org Base
  2. Connect to an existing database
  3. Select postgresql which is probably way at the bottom
  4. Click next
  5. For connection settings - put in a connection to a postgresql db which should look something like:
    host=localhost port=5432 dbname=somedb
  6. Next - fill in username and password when prompted
  7. Take default for remaining screens

Installing the PostgreSQL JDBC Driver

Note in general the PostgreSQL JDBC driver is said to be slower than the sdbc one since it goes thru a JDBC layer. We have not tested this theory. The JDBC driver however is more production quality and has the additional benefit of working in Mac OSX as well which is not currently supported by the SDBC driver.

To install do the following

  1. Download the JDBC 4 PostgreSQL 8.2 driver from http://jdbc.postgresql.org/download.html
  2. Create a folder in C:\Program Files\OpenOffice.org 2.3\program\ called jdbcdrivers. It can be called anything really.
  3. Copy the downloaded jar into that folder.
  4. Click on "C:\Program Files\OpenOffice.org 2.3\program\soffice.exe" - Again you can just open "OpenOffice.org Writer"
  5. Tools -> Options -> Java -> Class Path -> Add Archive -> point at the jdbcdrivers/postgresql-8.2-507.jdbc4.jar file you just created. Note: we tried using the Add Path and pointing at the folder, but that did not work.
  6. Exit soffice and close any quick start soffice task items

Connecting to PostgreSQL from OOBase using JDBC driver

  1. Start -> All Programs -> OpenOffice.org 2.3 -> OpenOffice.org Base
  2. Connect to an existing database
  3. Select JDBC which is the default.
  4. Click next
  5. In JDBC driver class - type org.postgresql.Driver - Case is important. and then click the Test Class. You should get a message that says loaded successfully.
  6. For connection settings - put in a connection to a postgresql db which should look something like:
    postgresql://localhost:5432/somedb
    Your screen should look something like this
    OO Base JDBC Database setup screen
  7. Next - fill in username and password when prompted
  8. Take default for remaining screens

Differences between using the SDBC driver and JDBC driver

From our observation we noticed the following differences between the drivers

  1. With the SDBC driver, you see the information_schema and pg_catalog schema. This does not seem to show using the JDBC driver.
  2. You can create tables with both drivers, however, the SDBC driver seems incapable of creating serial columns in its current state while the JDBC one can.
  3. Once a table is created, you can not edit it with the JDBC driver, but you can with the SDBC. Although the SDBC coughs when it sees a serial and insists on redefining it.Although it shows an AutoValue Yes/No option. This did not seem to work.

So general conclusion. Stick with PgAdmin when creating tables and adding columns. Both drivers seem deficient in that area. Other caveat, OOBase seems to follow the proper casing paradigm of MS Access. This is annoying for PostgreSQL use, since it will by default create proper cased tables and field names which then will always need to be quoted. We didn't see a mechanism to switch this off.

Viewing Relationships and Creating new ones

One thing that is nice about OOBase is that you can see your table relationships all laid out and add new ones. This seems to work equally well with both drivers. To do so do the following

  1. In OOBase go to Tools -> Relationships
  2. For the tables in PostgreSQL where you have already created foreign key constraints, you should see these nicely laid out
  3. You can add new tables to the layout and draw lines between tables, right click properties to set/view cascade actions - similar to the way MS Access works.

The layout is stored in the .odb file, but the actual foreign key constraints defined gets stored in the PostgreSQL database. Unfortunately we couldn't find a Print Relationships feature like what Microsoft Access has.


Below is snapshot of what the relationships screen looks like
OOBase Database Relationships Diagrammer

Query Designer

The Query designer is a nice feature, but has some rough spots. If you are used to MS Access query designer, it has a similar feel. Links are automatically drawn when you drag in related tables, you can drag and drop links between two tables, right click to change join type. All very comfy and Accessy.
Below is a snapshot of the query designer.

It seems to be able to create queries fine. We didn't really stress test though. Queries are saved in the .odb file not the PostgreSQL database.

NOTE: If you are using the Query Designer with PostgreSQL SDBC/JDBC, make sure to uncheck Use Outer Join Syntax (OJ) otherwise your LEFT and RIGHT JOIN queries will fail with a nasty error.

To get to where the OJ setting is,

  1. switch to Queries tab
  2. right mouse-click
  3. Select Database->Advanced Settings->Special Settings

OOBase Query Tab
Advanced Settings Tab looks like this
OOBase DB Advanced Settings

Views

OOBase lets you graphically create views similar to query designer, and saves them in the database, but there doesn't seem to be a mechanism to see the SQL of them or change them once created. From then on they are treated as tables. Sometimes creating a view just doesn't work when you go to save.

Data Editing

Data can be edited from forms, queries, and tables, but not Views (at least not using the PostgreSQL SDBC/JDBC drivers). Data can be filtered and so forth. Again very similar to what you do with MS Access except in Access, you can edit data in linked Views if you denote a primary key. There doesn't seem to be a mechanism to do that in OOBase.

Some other useful features

Hiding Tables you don't care to see can be done easily with Tools->Table Filter.

You can run ad-hoc sql commands against the database with Tools->SQL. This is more designed for running action queries like Vacuum Analyze.

Query builder has an option for you to run direct SQL command directly. This mode is equivalent to MS Access - Pass-thru Query mode. This will allow you to use advanced features of PostgreSQL SQL dialect. Unfortunately if you choose this option, you can't really use the query designer very easily - although you can start with Query designer and then do the following:

  1. View->Switch Design View On/Off
  2. Edit->Run SQL command directly


In upcoming version 2.5 of open office - I think its on the road map to allow Design view changes even in Run SQL command directly mode