From owner-luv@luv.asn.au Thu Oct 24 16:47:38 2002
From: Troy Dack troy@tkdack.com
To: Linux Users of Victoria luv@luv.asn.au
Date: Thu, 24 Oct 2002 22:42:30 +1000
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database choices)
X-Mailer: Ximian Evolution 1.0.8

On Thu, 2002-10-24 at 17:10, Neale Banks wrote:
> On Wed, 23 Oct 2002, Troy Dack wrote:
>
>> See this:
>> http://linuxmafia.com/~rick/linux-info/openoffice.org-mysql.txt
>> or
>> http://linuxmafia.com/~rick/linux-info/openoffice.org-mysql.pdf
>>
>> I tried it, it works and should give you just about everything that you
>> are after. It even comes pretty close to the MS Access pointy-clicky
>> database type stuff.
>
> Anyone got a pointer to a similar doc for openoffice.org+postgresql? For
> bonus points, a specific reference to doing this on Debian Woody would be
> most gratefuly received.
>
> I'm half-way there (thanks to above doc), but it won't (yet) connect :-(
>
> Thanks,
> Neale.

This might provide a few pointers

http://www.unixodbc.org/odbcinst.html

Plus a quick look through dselect turns up odbc-postgresql that looks
like it supplies the required "drivers" for unixODBC.

Hope it's some help, but I have never used PostgreSQL, sorry.

--
Troy Dack
http://linux.tkdack.com

From: Rick Moen rick@linuxmafia.com
To: Linux Users of Victoria luv@luv.asn.au
Date: Thu, 24 Oct 2002 11:31:01 -0700
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database choices)
User-Agent: Mutt/1.4i

Quoting Paul Sorenson (pauls@classware.com.au):

> Using postgresql as a datasource for openoffice is possible. I have
> only done it for a trivial exercise however. I used the JDBC driver
> for postgres.

Should work. I think Neale was hoping for a step-by-step guide, similar
to the one for MySQL.

--
Cheers, "That article and its poster have been cancelled."
Rick Moen -- David B. O'Donnel, sysadmin for America Online
rick@linuxmafia.com

From: Paul Sorenson pauls@classware.com.au
To: Linux Users of Victoria luv@luv.asn.au
Date: Fri, 25 Oct 2002 09:42:20 +1000
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database choices)

"Rick Moen" rick@linuxmafia.com wrote:

> Should work. I think Neale was hoping for a step-by-step guide, similar
> to the one for MySQL.

If I recall, step-by-step for me was:
- open up a help window on data sources
- open up the tools | data sources dialog
- frig around until you get it to stick.

I think at one stage, the only "undocumented" trick was that I had to shut
down all open office instances and start up again to load the JDBC driver.

If it helps my data sources windows look like:
database type: JDBC
Data Source URL jdbc:postgresql://<host>/<database name>
JDBC Driver class: org.postgresql.Driver

Somewhere in the options I had to make sure the postgres JDBC driver (jar
file) could be found by OpenOffice. There is some classpath setting
somewhere but I don't have time right now to dig it out.

From: Gavin Baker gavinb@optushome.com.au
To: luv@luv.asn.au
Date: Sat, 26 Oct 2002 17:51:47 +1000
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database choices)
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623
Debian/1.0.0-0.woody.1

Greetings,

I just read this thread, and decided to try this myself since I have
PostgreSQL and OpenOffice.org. I managed to get it working fine, so I
thought I'd post some notes... it's not exactly a step-by-step tutorial,
but it should be enough to guide you through.

I am running Debian woody, and already had Ooo and psql installed as
normal. In addition, I installed the following packages:

- odbc-postgresql
- unixodbc

Then I needed to configure the ODBC setup. So:

- Copy /usr/lib/postgresql/share/odbcinst.ini.template to /etc/odbcinst.ini
- Copy /usr/lib/postgresql/share/odbc.ini.template to /etc/odbc.ini

I then edited ~/.odbc.ini (based on the one in /etc) to add my own
databases. Essentially /etc/odbcinst.ini contains info on the drivers,
while /etc/odbc.ini has system-wide databases, and ~/.odbc.ini has
user-specific databases. Here is my sample (which is based on the
supplied template):

[Contacts]
Description = Personal Contacts List
Driver = PostgreSQL
Trace = No
TraceFile = /tmp/odbc.log
Database = contacts
Servername = localhost
UserName = gavinb
Password = mysecret
Port = 5432
Protocol = 6.4
ReadOnly = Yes
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

The one final step was adding some ODBC-specific stuff to my database.
As root, I did a 'su - postgres' and then ran:

% psql -d contacts < /usr/lib/postgresql/share/odbc.sql

(Actually I didn't do this with another db, and it worked fine so it may
not be essential.)

My database was called 'contacts'. As myself, I verified the database
with 'psql contacts', then tried connecting with odbc, using:

% isql Contacts

Note the parameter is the db name in the ini file in brackets, not the
real db name.

Once I verified that ODBC could talk to my database, I fired up
OpenOffice.org and went into the data browser (F4). I selected Tools |
Data Sources, then pressed New, chose ODBC and used the '...' button to
browse the available databases. All the defined dbs came up in the
list, and I chose one. It was then added to the list and when I came
back to the browser I could see the tables and it all worked fine.

So... most useful was /usr/share/doc/odbc-postgresql/unixodbc.HOWTO
which I followed to get ODBC working. Then I used
http://linuxmafia.com/~rick/linux-info/openoffice.org-mysql.txt as a
guide for the OOo side of things.

It's quite easy really... if anything is unclear in the above, let me
know and I'll try to follow up.

ciao,

::gavin

From owner-luv@luv.asn.au Tue Oct 29 01:46:36 2002
Return-path: owner-luv@luv.asn.au
Envelope-to: rick@linuxmafia.com
Delivery-date: Tue, 29 Oct 2002 01:46:36 -0800
Received: from free.its.monash.edu.au ([130.194.11.76] helo=free.luv.asn.au)
by linuxmafia.com with esmtp (Exim 3.36 #1 (Debian))
id 186SxH-0005yz-00
for rick@linuxmafia.com; Tue, 29 Oct 2002 01:46:35 -0800
Received: from localhost (mail@localhost)
by free.luv.asn.au (8.11.6/8.11.2) with SMTP id g9T9fQZ28429;
Tue, 29 Oct 2002 20:41:26 +1100
Received: by free.luv.asn.au (bulk_mailer v1.9); Tue, 29 Oct 2002 20:41:14 +1100
Received: (from majordomo@localhost)
by free.luv.asn.au (8.11.6/8.11.2) id g9T9fDW28418
for luv-l; Tue, 29 Oct 2002 20:41:13 +1100
Received: from ALPHA9.CC.MONASH.EDU.AU (alpha9.cc.monash.edu.au [130.194.1.9])
by free.luv.asn.au (8.11.6/8.11.2) with ESMTP id g9T9fAK28411
for luv@luv.asn.au; Tue, 29 Oct 2002 20:41:10 +1100
Received: from kapow.its.monash.edu.au ([130.194.1.71])
by vaxh.cc.monash.edu.au (PMDF V5.2-31 #39306)
with ESMTP id 01KO90ZBBT7Y94ETH8@vaxh.cc.monash.edu.au for luv@luv.asn.au;
Tue, 29 Oct 2002 20:41:10 +1100
Received: from kapow.its.monash.edu.au (localhost [127.0.0.1])
by localhost (Postfix) with ESMTP id 4AD0E2000A for luv@luv.asn.au; Tue,
29 Oct 2002 20:41:10 +1100 (EST)
Received: from marina.lowendale.com.au (gw.lowendale.com.au [203.26.242.120])
by kapow.its.monash.edu.au (Postfix) with ESMTP id 858E220007 for
luv@luv.asn.au; Tue, 29 Oct 2002 20:41:08 +1100 (EST)
Received: from localhost (neale@localhost) by marina.lowendale.com.au
(8.9.3/8.9.3/Debian/GNU) with ESMTP id UAA14141 for luv@luv.asn.au; Tue,
29 Oct 2002 20:34:11 +1100
Date: Tue, 29 Oct 2002 20:34:09 +1100 (EST)
From: Neale Banks neale@lowendale.com.au
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database
choices)
In-reply-to: 3DBA4993.2070502@optushome.com.au
To: luv@luv.asn.au
Message-id: Pine.LNX.4.05.10210292029220.14027-100000@marina.lowendale.com.au
MIME-version: 1.0
Content-type: TEXT/PLAIN; charset=US-ASCII
Content-transfer-encoding: 7BIT
Sender: owner-luv@luv.asn.au
X-Mailing-List: luv@luv.asn.au
X-Spam-Status: No, hits=-3.3 required=5.0
tests=EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
SPAM_PHRASE_00_01,USER_AGENT_PINE,X_MAILING_LIST
version=2.42
X-Spam-Level:
Status: RO
Content-Length: 878
Lines: 26

On Sat, 26 Oct 2002, Gavin Baker wrote:

> I just read this thread, and decided to try this myself since I have
> PostgreSQL and OpenOffice.org. I managed to get it working fine, so I
> thought I'd post some notes... it's not exactly a step-by-step
> tutorial, but it should be enough to guide you through.
>
[snip useful stuff]

That's pretty much what I ended up doing.

The only remaining puzzle is that any tables lacking a Primary Key come up
on OOo as readonly (regardless of the odbc.ini "ReadOnly" setting).

Is this something I should expect? IF so, is it an ODBC thingy or an OOo
thingy?

Also, the OOo forms designer seems a little obtuse - anyone got any
suggestions on how to come to terms with it?

Thanks,
Neale.

-
luv@luv.asn.au is for LINUX-RELATED POSTS ONLY. For details and information
on how to unsubscribe, see http://www.luv.asn.au/mailinglists.html.

From owner-luv@luv.asn.au Tue Oct 29 03:12:57 2002
Return-path: owner-luv@luv.asn.au
Envelope-to: rick@linuxmafia.com
Delivery-date: Tue, 29 Oct 2002 03:12:57 -0800
Received: from free.its.monash.edu.au ([130.194.11.76] helo=free.luv.asn.au)
by linuxmafia.com with esmtp (Exim 3.36 #1 (Debian))
id 186UIr-0003Qm-00
for rick@linuxmafia.com; Tue, 29 Oct 2002 03:12:57 -0800
Received: from localhost (mail@localhost)
by free.luv.asn.au (8.11.6/8.11.2) with SMTP id g9TB7oa28781;
Tue, 29 Oct 2002 22:07:50 +1100
Received: by free.luv.asn.au (bulk_mailer v1.9); Tue, 29 Oct 2002 22:07:44 +1100
Received: (from majordomo@localhost)
by free.luv.asn.au (8.11.6/8.11.2) id g9TB7hL28770
for luv-l; Tue, 29 Oct 2002 22:07:43 +1100
Received: from ALPHA8.CC.MONASH.EDU.AU (alpha8.cc.monash.edu.au [130.194.1.8])
by free.luv.asn.au (8.11.6/8.11.2) with ESMTP id g9TB7fK28763
for luv@luv.asn.au; Tue, 29 Oct 2002 22:07:41 +1100
Received: from kapow.its.monash.edu.au ([130.194.1.71])
by vaxh.cc.monash.edu.au (PMDF V5.2-31 #39306)
with ESMTP id 01KO93Z07NQC9066ZV@vaxh.cc.monash.edu.au for luv@luv.asn.au;
Tue, 29 Oct 2002 22:07:12 +1100
Received: from kapow.its.monash.edu.au (localhost [127.0.0.1])
by localhost (Postfix) with ESMTP id BA3E92000F for luv@luv.asn.au; Tue,
29 Oct 2002 22:07:12 +1100 (EST)
Received: from mail.strategicdata.com.au
(faire.strategicdata.com.au [202.44.177.161]) by kapow.its.monash.edu.au
(Postfix) with ESMTP id 89D772000E for luv@luv.asn.au; Tue,
29 Oct 2002 22:07:12 +1100 (EST)
Received: from rack002.mel.strategicdata.com.au (localhost [127.0.0.1])
by delivermail.mel.strategicdata.com.au (Postfix) with ESMTP id 1EEACEC0A2;
Tue, 29 Oct 2002 22:07:06 +1100 (EST)
Received: from StrategicData.com.au
(dealramh.mel.strategicdata.com.au [192.168.1.53])
by mail.strategicdata.com.au (Postfix) with ESMTP id 6407BEC0A2; Tue,
29 Oct 2002 22:07:05 +1100 (EST)
Date: Tue, 29 Oct 2002 22:07:05 +1100
From: Adam Clarke Adam.Clarke@StrategicData.com.au
Subject: Re: openoffice.org+postgresql (Was:Re: Advice please on database
choices)
In-reply-to: Pine.LNX.4.05.10210292029220.14027-100000@marina.lowendale.com.au
To: Neale Banks neale@lowendale.com.au
Cc: luv@luv.asn.au
Message-id: 3DBE6BD9.5090409@StrategicData.com.au
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=us-ascii
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2b)
Gecko/20021016
X-Accept-Language: en-us, en
References: Pine.LNX.4.05.10210292029220.14027-100000@marina.lowendale.com.au
Sender: owner-luv@luv.asn.au
X-Mailing-List: luv@luv.asn.au
X-Spam-Status: No, hits=-1.8 required=5.0
tests=IN_REP_TO,REFERENCES,SPAM_PHRASE_03_05,USER_AGENT,
USER_AGENT_MOZILLA_UA,X_ACCEPT_LANG,X_MAILING_LIST
version=2.42
X-Spam-Level:
Status: RO
Content-Length: 997
Lines: 24

Pretty much an ODBC thing although I have found that when using some
drivers from Access I am asked what column to treat as unique. The
problem here (for the ODBC driver) is that it needs a unique key with
which to write back your changes to a particular record, otherwise your
update or delete might impact other rows unintentionally. Some databases
have unique record id's (Oracle for one) independant of your own meta
data (primary key) and on those the ODBC driver should be able to handle
that problem without resorting to making things read only or asking for
your (possibly incorrect) input.

>The only remaining puzzle is that any tables lacking a Primary Key come up
>on OOo as readonly (regardless of the odbc.ini "ReadOnly" setting).
>
>Is this something I should expect? IF so, is it an ODBC thingy or an OOo
>thingy?
>
>
>
>

-
luv@luv.asn.au is for LINUX-RELATED POSTS ONLY. For details and information
on how to unsubscribe, see http://www.luv.asn.au/mailinglists.html.