Using OpenOffice Base 2.3.1 with PostgreSQL     

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
   
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.

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

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