Quick HOWTO - connecting StarOffice Win or Lin to PostGreSQL using ODBC

From: Olivier Berger <olivier(dot)berger(at)idealx(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Quick HOWTO - connecting StarOffice Win or Lin to PostGreSQL using ODBC
Date: 2001-06-26 13:14:14
Message-ID: 3B388AA6.7BC12467@idealx.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-odbc

We have had some problems to enable connection between StarOffice (on
GNU/Linux and on Windows systems) to a PostGreSQL database (running on
GNU/Linux) over ODBC connection.

Here is a quick howto I wrote as a reminder of the problems and
solutions we found.

Hope you find it useful.


$Id: ConfigurationOdbc,v 1.2 2001/06/26 12:51:53 oberger Exp $

We have encountered problems to enable access to a GNU/Linux server
running PostGreSQL, from Linux and Windows platforms, both using
StarOffice? to access the database (using de StarBase? forms and

Our goal was to be able to interoperate with the database (read and
write) from both platforms in a unified way (same forms, look and feel).

The database connectivity from StarOffice? is made with ODBC.

StarOffice? configuration to access ODBC data sources is quite the same
once the ODBC sources have been defined.

We use the following ODBC drivers :

unixodbc 2.0.4 for GNU/Linux, from http://www.unixodbc.org/
(package Debian 2.0.4-0.potato3)
psqlodbc 7.1.0005 for Windows, from http://odbc.postgresql.org/
(zip file)

Problems encountered

The unixodbc driver, as packaged with the debian distribution does not
include a "default" /usr/lib/libodbcpsql.so file. Thus you have to
the right libodbcpsql.so.* file in the .odbc.ini or odbc.ini /
odbcinst.ini files or create the appropriate link. See below.

The tables must have a primary key defined with a consttraint or index
in PostGreSQL in order to allow read-write access to the table from

The tables indices for primary keys must be named according to the
following scheme in order to be recognized by the PsqlODBC ODBC driver
for windows : "{table}_pkey" (see the FAQ

Configuring the PostGreSQL DataBase? server

The file pg_hba.conf should specify that PostGreSQL network access is
allowed from the client machines and users.

The postmaster.init file should include the option allowing network
connection (and be launched with -i option)


Configuring the GNU/Linux ODBC driver to allow access to the remote

We configured the .odbc.ini file with :

[ODBC Data Source]
funnel = funnel

Description = FUNNEL satanas
Driver = /usr/lib/libodbcpsql.so
Trace = No
Database = template1
Servername = servername.domain.com
UserName? = postgres
Password = postgres
ReadOnly? = No

Beware that the library /usr/lib/libodbcpsql.so exist. If not, create a
link to the appropriate libodbcpsql.so.* file.

Configuring the Windows ODBC driver to allow access to the remote server

You should verify that the Read-Only checkboxes aren't checked in the
configuration option dialogs of the driver.

Creating tables structure

If you want to allow read-write access to the tables from StarOffice?
via the windows ODBC PostGreSQL driver, tables must have a primary
key named "{table}_pkey" (see the FAQ

By default, it will be OK if you create the table like this :

CREATE SEQUENCE "table_seq";

CREATE TABLE mytable (


If you use a CONSTRAINT definition, you should pay attention to the name
of the constraint :

CREATE SEQUENCE "table_seq";

CREATE TABLE mytable (
"id" INTEGER DEFAULT NEXTVAL('table_seq'),


CONSTRAINT "mytable_pkey" PRIMARY KEY("id")

Développeur senior 15-17, av. de Ségur F-75007 PARIS http://IDEALX.com/

Browse pgsql-odbc by date

  From Date Subject
Next Message Hiroshi Inoue 2001-06-27 05:36:42 Re: Write access problems with StarOffice for Windows
Previous Message BELLON Michel 2001-06-26 10:41:56 odbc with MS-Query