postgresql-sdbc-driver

Content

Introduction

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

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. In case there is an older version, remove it e.g. by invoking . Then install the new version with the following command: . 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 . 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

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

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

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

URL format

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

• Integration into OpenOffice GUI via DataSources
• Viewing, inserting and updating tables via the beamer window (press F4)
• Creation of a new table, view or index
• Structural modifications to existing tables
• Viewing and creation of table’s relationships (relational keys)
• Supported data types include now strings, numbers, binaries, date and time.

The following features work with some limitations one needs to keep in mind:

• Renaming tables
  You can only change the schema of table or view, when your postgresql server’s version is 8.1 or later. Note that the change is not transactional, (the alter table statement does not allow this). First the driver changes the schema and if this succeeds, it tries to change the table’s name. With former versions, you can only change the table’s name.
• Data modifcation
  Modification of data in tables via the UI (e.g. via the beamer window) is only possible, when your table has a primary key and the primary key is part of the used select statement (otherwise the OOo framework has no possibility to find the row again to write the modification).
• The serial datatype (and default values)
  Support for serial (= auto increment ) datatype is difficult to implement, because it is not really a type in postgresql.

  You can create tables with serial columns. Therefor you have to choose a int or big int as data type of the column and set the autoincrement flag to true. Note, that you can only do this during creation of the table (before you press the save button the first time), as postgresql does not support serials in ALTER TABLE statements.

  Additionally it is difficult to retrieve the generated value after an insertion in such a table. The driver follows two strategies here. When the table supports posgresql oids, it uses oid to find the just inserted row again. In case it does not, it queries for the structure of the primary key of the table and uses the curr_val() function to retrieve the last increment of the serial in this session.

  This is a little slow (because these reflection queries are executed for every insertion). It might be sped up with cashing the reflection data when too many people complain.

• Updateable resultsets
  Updateable resultsets are currently implemented for selects on simple tables only. Resultsets containing data from multiple tables raise errors in case a modification is attempted.
• Creating and editing table structures
  Creating and editing table structures works, but keep the following problems in mind
  1. The table wizard does not yet work with the postgresql driver (see i74185).
  2. You cannot change the type (including the lengths for fixed width types) of a column. Instead delete the column, press save, add a column with the same name and choose your new type and press save again. This data stored in this column is lost.

  When you change the type by accident, OOo behaves somewhat strange. When you press the save button, you don’t get an error message but the save button does not become disabled as it does normally. You have to close the window without saving, otherwise you are locked up in this window.
  Note also, that all column comments are stored into the user’s office configuration and not within the postgresql database. Consequently, the comments already stored in the postgresql database are not shown.

  This is currently a limitation in the OOo framework itself, so the framework will need to change to allow this.

  Logging
  For diagnostic purposes, a rudimentary logging has been implemented in the driver. The loglevel can be set by editing a .ini or rc file in the extracted package. The file is located in the following path :

  /openoffice.org2/uno_packages/cache/uno_packages/postgresql-sdbc-0.7.6.zip.1086437099/postgresql-sdbc.unorc , where the number is different on your system. The file can be edited with a texteditor. The logevel can be set to NONE (no logging), ERROR (only errornouss situations are logged), INFO (some more verbose output) and SQL (every SQL statement sent to the server is logged including access time in milliseconds ). The data gets logged into the sdbc-pqsql.log in the program directory. It would be nice, if someone with some database knowledge woud review the queries needed to reflect the postgresql database for performance problems, but just give me hints on the internal structure of the queries. I can’t do anything against the fact, that some queries are executed multiple times (this must be done in the OOo framework).

• User Administration
  Priviliges are not shown and cannot be modified in the user administration dialog.
• Knwon issues
  

  #i30059#	Updates may fail on tables with non-primary-indexes
  #i16426#	Table design dialog offer not-existent schema name

The following features are not implemented yet, but may follow in future.

• data types like clobs, blobs and arrays are not yet supported. The whole datatype handling for non-standard datatypes is crippled currently, here needs to be developed a concept first.
• Callable statements (XConnection.prepareCall())

Using the driver via the API

Building from source

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.

(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]).

Libreoffice Base: драйвер SDBC

Доброго времени суток. Не могу использовать Libreoffice Base — возникает ошибка

Традиционно:
emerge —info libreoffice-bin
eix -I libreoffice
equery f libreoffice

Если libreoffice установлен бинарный — то же самое, только для libreoffice-bin

Выхлоп lddtree /usr/lib64/libreoffice/program/libsdbc2.so что кажет?

JRE есть? Не уверен нужно ли еще ставить dev-db/hsqldb, сам я Base не использовал

JRE есть. dev-db/hsqldb поставил,

Ну тогда остаётся только в код смотреть 🙁

Проблема решается сборкой с флагом USE=«java»

тогда до кучи ещё в настройках после установки используемую версию java выбрать.