English:

The Free and Open Productivity Suite
New: Apache OpenOffice 4.1.1 released!
Project Sections: Home | Specifications | QA | Development | Database Drivers
Database drivers: PostgreSQL | Linux MDB | SQLite

postgresql-sdbc-driver

Content

Introduction

The postgresql SDBC Driver allows to use the postgresql database from OpenOffice.org without any wrapper layer such as odbc or jdbc.

The current version 0.7.6a can be considerded as good beta quality ( with some known issues and missing features).

The driver is aimed at the OpenOffice.org versions 3.x/2.x/1.1.x, it does not work with OOo1.0.x trees.

The final aim is to have an easier to use, faster, more feature rich database driver than the jdbc-odbc solution. The current version should already allow this in most places (though I actually have never compared them feature by feature).

Requirements

Install a postgresql server if you haven't one already. The current driver version was tested using postgresql-7.3.2. It does not work with postgresql 7.2.x server version. It should work with all other currently available including 8.x versions.

Install OpenOffice.org.

Download

Download the binary version of the driver (depending on your OOo version). Follow below installation instructions.
Version OOo version Platform Location md5sum
0.7.6b (released 2010-08-12) OOo 3.3 and above Windows, Linux x86 (multi platform package)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.6b.zip ae8915cfd031b2c4c0cd970f9409a736
0.7.6a (released 2010-02-06) OOo 2.x -> 3.2 Windows, Linux x86 (multi platform package)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.6a.zip 1723de995efd1ad69ee59b5e15e805a6
0.7.5 OOo 1.1.x Windows, Linux x86 (multi platform package)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.5-for-oo11.zip 03484e135e2b3517a633936b211e7c7a

Older (outdated) builds can be found below. If your platform does not appear in the above list, you can build the driver from source.

Installation with OOo 3.x/2.x

OOo 2.x There are two different ways to install the driver:
1 Start OpenOffice, choose Tools/Package Manager ...
Click on the add button and select the downladed file (DO NOT EXTRACT IT !) in the upcoming File dialog. The driver gets installed now, afterwards, the file is listed in the dialog with state enabled. In case you have installed an older version of the driver, remove it now. Close the dialog and shutdown openoffice (even the quickstarter on windows must be shut down !!!).
2 Shutdown OpenOffice.org (even the quickstarter on windows must be closed, see the system tray at the bottom right of your desktop).

Start a shell and switch to /path/to/openoffice/program.

Check, whether you have installed an older version of the driver.

          unopkg list
      
In case there is an older version, remove it e.g. by invoking
          unopkg remove postgresql-sdbc-0.6.2.zip
      
. Then install the new version with the following command:
      unopkg add /path/to/your/download/postgresql-sdbc-0.7.6.zip
      
. Using the --shared option will install the driver for all users.

Then start the OpenOffice.org Base program ./sbase. In the upcoming dialog choose the option connect to an existing database. In the combobox should appear postgresql (if the it is not there, the installation of the driver has failed).

In the upcoming dialog, you can enter space separated name/value pairs describing the database connection. Note that there must not be a leading nor a trailing space surrounding the attributes. At the simplest level, this string can be empty ( which means connecting to the user's database instance running on the same machine ). A typical url e.g. is

         dbname=pqtest host=mydatabaseserver
         
. A full list of possible options can be found on the postgresql site . User/password can be given in the following dialog (so that the password does not appear in plain text).

You can press the Test Connection button to verify, that your settings work. Then press the Finish button, and you can work with your postgresql database.

Tip: Open the Tools/Table Filter dialog afterwards and deselect the schemas and tables, you don't want to work with (typically you won't want to work with the pg_catalog (postgresql intern) schema.

Installation with OOo 1.1.x

OOo 1.1

In case you have used an older version of this driver, you have to deinstall the old one before. This can simply be done by deleting the postgresql-sdbc-$(VERSION).zip (e.g. 0.5.0 or 0.6.0) file from the OpenOffice1.1.0/user/uno_packages folder.

Copy the postgresql-sdbc-0.7.5-for-oo11.zip file in OpenOffice1.1.0/user/uno_packages folder (Note: USE the zip file as a whole, DO NOT extract it). Open up a shell and switch to the OpenOffice1.1.0/program directory and start the

pkgchk

tool (assuming that you have a . in your PATH). It should deinstall the old and install the new version during in one run in case you have followed the steps as described above. On success, no output is given.

Alternatively, you can install the driver for all users of a OpenOffice.org network installation. This must be done by the user, who installed the network installation (typically root or Administrator). All users should terminate there office before, running offices won't see an effect until next restart and at worst may crash during or after the installation. Place the file into the OpenOffice1.1.0/share/uno_packages folder (it must be the OpenOffice1.1.0 directory, which contains all the shared libraries and executables). Change the ownership of the file to root. Then start

pkgchk --shared

(again assuming that you have a . in PATH).

You can uninstall the package by deleting the file in the uno_packages directory and starting pkgchk (or as root pkgchk --shared) again.

Adding a datasource

In case you have used an older version of this driver, you can continue to use your earlier configured datasources.

You can add a new datasource via the common Tools/Data Sources dialog. Click on new datasource and select postgresql from the Database type combobox. You should currently put all connection information into url line by using the following format (except for user/password, which optionally can be entered differently, see below ):

URL format

The XDriver.connect() expects a url as parameter. This section specifies, how the url must look like to access the postgresql driver.

Format: sdbc:postgresql:[name1=value1] [name2=value2] [...]

The URL must start with the sdbc:postgresql:prefix. The string following this prefix is the connection string as it is expected by the postgresql client API . Note that there must not be a leading nor a trailing space surrounding the attributes, but all attributes must be separated by a space. At the simplest level, this string can be empty ( which means connecting to the user's database instance running on the same machine ).

If you want to connect with a password and you don't want to have it appear in the url, you can instead activate the second (in OOo1.1.x unnamed) tab page between General and Tables, fill in the user in the appropriate input field and check the password required box. You are now prompted for the password when connecting to the data source.

After you have entered the url, you should switch to the tables tab in the same dialog. The driver connects to the database and shows the found schemas and tables. Select the schemas you want to work with (in general, you won't want to work with the pg_catalog schema).

Supported and missing features

The following features work with some limitations one needs to keep in mind: The following features are not implemented yet, but may follow in future.

Using the driver via the API

You may use the driver via the API directly by instantiating org.openoffice.comp.connectivity.pq.Driver service. You can use the driver also from within a python process or with a standalone java/C++ program.

Building from source

Build in a OO1.1 environment (this is needed to keep up compatibility with OOo1.1). I have actually never checked, whether the driver builds in OOo2.0 env also.

Download the postgresql module from here, it contains the makefiles to build postgresql client API. You must place the postgresql-7.3.2 tarball into the download directory. Build and deliver the postgresql module. Note, that the driver just uses the client part of the API. As postgresql interprocess protocol is backward compatible, it shouldn't be problem to later connect also to newer versions of the database.

Check out the connectivity project (e.g.

cvs co -r OpenOffice_1_1_rc3 connectivity

and then retrieve the postgresql driver code with this special tag OO_PQSDBC_x_y_z (where x,y,z are the major, minor, micro of the version you wish to build).

cvs update -d connectivity/source/drivers/postgresql connectivity/workben/postgresql
cvs update -r OO_PQSDBC_0_6_1 connectivity/source/drivers/postgresql connectivity/workben/postgresql

. Build connectivity/source/drivers/postgresql. You will then find a postgresql-sdbc-0.x.y.zip uno-package in the bin or lib output directory.

Test

In order to test your build, you need a running instance of postgresql database server and PyUNO. The test can be found in connectivity/workben/postgresql. The test syntax for the dmake command is dmake runtest "dburl=sdbc:postgresql:dbname=pqtest" . You should create a fresh database pqtest (or any other name) as the test also writes data and drops tables.

(Note, when you have never used postgresql server before and just want to test your build, this short series of calls should give you an postgresql test-server [note, that this is not the suggested way to install a postgresql server, follow the postgresql instructions therefor]).

gunzip < postgresql-7.3.2.tar.gz | tar -xvf -
cd postgresql-7.3.2
./configure --prefix=/path/to/your/postgres/installation
make ; make install
setenv PATH "$PATH":/path/to/your/postgres/installation/bin
mkdir /path/to/your/postgresql/installation/data
initdb -D /path/to/your/postgresl/installation/data
postmaster -D /path/to/your/postgresl/installation/data

now start a a separate shell:
setenv PATH "$PATH":/path/to/your/postgres/installation/bin
createuser
createdb pqtest


Changes

Reporting bugs

Please read through the known bugs section before reporting bugs. Create an issue and assign the issue to myself (jbu@openoffice.org).Questions should be raised in dev@dba.openoffice.org mailinglist. Please don't fire usage questions directly at myself.

Outdated versions

Version Platform Location md5sum
0.7.6 for >= OO2.x and <= OOo3.1 (released 2008-08-23) Windows, Linux x86 (multi platform package)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.6.zip f30bd071ce0e35da6eadedbec4f6eef5
0.7.5 for OO2.x (released 2007-08-27) Windows, Linux x86 (multi platform package)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.5.zip 6db1a4b3b811957e4c1191ae2ba736ac
0.7.4 (released 2007-02-16) Windows, Linux x86 (multi platform package)
Note: For Linux x86, use this only with OpenOffice.org 1.1 (see below)
http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.4.zip 4e15fa7dcb017b66dfb060498ab92f4d
0.7.4a(recommended for OOo2.2 and above) (released 2007-05-27) ONLY Linux x86 with OpenOffice.org 2.2 and above (see issue i77188) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.4a.zip 02e30ed857e836230159ab052ce21a70
0.7.3 (released 2007-01-08 ) Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.3.zip 8586328f1e57f17e458a42da949ea174
0.7.2 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.2.zip f3fa2a7b859eb5d1ccc2859a739cf620
0.7.1 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.1.zip 213a34341a3c9e12476f58c76e29204b
0.7.0 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.7.0.zip 3d5d7996474f75cee9de20d741ffa5d8
0.6.2 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.6.2.zip 9522c14a1bc45edc3b5b3b50c99c2f9a
0.6.1 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.6.1.zip 058689603b18a4f9a050e47fb35921ec
0.6.0 Windows, Linux x86 (multi platform package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.6.0.zip e7866011e976641b206b43c269fa1fcf
0.5.0 Windows, Linux x86, Solaris sparc (multi platform uno package) http://dba.openoffice.org/drivers/postgresql/postgresql-sdbc-0.5.0.zip 24edb8fac50676b486dc9534a73f9e3e
0.5.0 Linux PPC ftp://ftp.sunsite.utk.edu/pub/linux/yellowdog/software/openoffice/postgresql-sdbc-0.5.0.zip n.a.

Authors

The driver has been implemented and is maintained by Joerg Budischewski (JoergBudi@gmx.de ) in my spare time.

Apache Software Foundation

Apache Software Foundation

Copyright & License | Privacy | Website Feedback | Contact Us | Donate | Thanks

Apache, the Apache feather logo, and OpenOffice are trademarks of The Apache Software Foundation. OpenOffice.org and the seagull logo are registered trademarks of The Apache Software Foundation. Other names appearing on the site may be trademarks of their respective owners.