postgresql-sdbc-driver

Content

• Introduction
• Requirements
• Download
• Installation and usage in OO3.x/OOo2.x
• Installation and usage in OO1.1.x
• Changes
• Features and know bugs
• Using the driver via the API
• Building from source
• Reporting bugs
• Outdated versions
• Author

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

Download

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

          unopkg list
      

          unopkg remove postgresql-sdbc-0.6.2.zip
      

      unopkg add /path/to/your/download/postgresql-sdbc-0.7.6.zip
      

         dbname=pqtest host=mydatabaseserver
         

Installation with OOo 1.1.x

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.

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

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

• 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())

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.

Test

(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

• Version 0.7.6b
  
  • i113494 the driver caused an error on OOo3.3 startup. This was fixed (an outdated configfile was removed from the package).
• Version 0.7.6a
  
  • i108928 the driver didn't work with OOo3.2. This was fixed (an additional configuration file within the package was necessary).
• Version 0.7.6
  
  • i89685 numeric columns can now be edited again. The problem showed up since some OOo2.x version, it does not occur in OOo1.x. As this is the only change, there is no 0.7.6 for OOo1.x.
• Version 0.7.5
  
  • i52352 you get now results when executing native (postgresql) queries
  • minor build issues ( i80085, i77337, i77336 )
  • i80904 a quoted single quote within a statement is now correctly recognized.
  • There are now two different packages, one targeted at 1.1 version of OpenOffice, the other one targeted at 2.x version of OpenOffice. The linux version differ, the windows version is the same in both packages (both built in the OO1.1 build enviroment)
• Version 0.7.4a
  
  • identical source tree to 0.7.4, just ensures, that the driver also works, when OOo2.x is started with soffice.bin. (see i77188). Works only on Linux x86 with OOo2.2 and above.
• Version 0.7.4
  
  • Earlier versions of the driver couldn't interoperate with OOo's table wizard. There had to be done changes within the driver (integrated in 0.7.4 release) and OOo's table wizard (see i74185). The fixes for the table wizard have not yet been integrated into OOo (probably not before OOo 2.3).
  • The varchar type is now reflected as varchar (You realize the change only in the table's design view, there is now the type 'Text' instead of 'Text fix').
  • Regression: The linux version of 0.7.3 had logging enabled by default, therefor the driver logs information into the program directory (sdbc-pqsql.log), this has been disabled again as it should.
• Version 0.7.3
  
  • Now both schema and table name can be changed via "table's context menu / Rename", when 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.
  • In former version, the retrieval of auto values failed, when the table/primary key column names had to be quoted. This bug has been fixed.
  • The driver now builds also in OOo 2.x build environment.
• Version 0.7.2
  
  • postgresql >= 8.1.x fully supported
    In former versions, the driver could not edit/create a table with postgresql 8.1.0 and above. This has been fixed (this was, because postgresql 8.1 does some stricter SQL checking).
  • Type content recognition
    The postgresql function, that returns the type of a certain column of a result set was not used correctly, this has been fixed. No content type recognition is needed anymore (was introduced in 0.7.1) (thx a lot for the hint from the postgresql community).
• Version 0.7.1
  
  • Domain types fully supported ( 63918).
    In former versions, the driver could not handle columns with with domain types ( domain type columns were simply empty within the UI), now they are fully supported.
  • Type content recognition 61887).
    When the postgresql database API does not inform about the type of a certain column in a resultset, the driver now guesses the type of the column from content of the first 100 rows within the resultset. Integers, numerics, date, time and timestamps are guessed. The is useful e.g. when you drag data into a spreadsheet.
  • View renaming/deletion ( 61777 )
    Views can now be renamed or deleted using OOo UI.
• Version 0.7.0
  
  • raised driver from alpha to beta state ( no serious bugs have been reported for the earlier versions)
  • fixed several issues with the former driver version and OO2.0.x ( problems with table creation, table renaming and crashes in user administration have been fixed)
  • data can now be inserted into tables without oids and the addition gets correctly reflected in the UI. This also holds for tables with auto increment values in the primary key. In former versions, this was only the case for tables with oids. However the current solution requires some additional reflection queries, which makes it a little slow and resource consuming.
  • fixed a crash when executing native sql (see i52352). The bug itself is not fixed though, native sql statements still return empty resultsets only.
  • adding/deleting users and password change is now supported. Privilege administration is still not supported.
• Version 0.6.2
  
  • Tables created by OOo should now appear immediatly in the list of available tables e.g. in the beamer window. Structural changes to tables (e.g. the addition of a column) are now shown after reselecting the table in the beamer window. In earlier versions, one had to a reconnect on the data source to see the changes.
  • The driver now supports arrays, however, as the OOo framework itself currently does not display arrays correctly, they are still reflected as plain strings as in earlier versions. You can only make use of this new feature by using the driver via the API.
  • An annoying bug has been fixed, that led to 'Input Error' pop up windows in forms. Under certain circumstances, the 'not null' column property could be transported transported incorrectly to OOo.
• Version 0.6.1
  
  • Data type handling
    Some postgresql data types are not yet correctly suppported by the driver ( arrays, blobs and 'esoteric types' such as circle, point, etc.). In prior version, values of this type were not displayed by the driver at all. Now, they are shown in their default string representation, which also allows modification of the values.

    All these types get currently mapped to the OOo Memo type, this is recognizable in the Edit-table window, where they are grouped together in the type selection box.

  • Named parameters in prepared statements (or subform support)
    In prior versions, only a '?' as a placeholder in prepared statements was supported, which did not work out properly with subforms. Now additionally named parameters (e.g. ':x' or ':myvar') are supported, which allows full subform support.

Reporting bugs

Outdated versions

Authors