2011 note: As you will see, these notes concern the very earliest production release of OpenOffice.org, which as of 2010 has been replaced by LibreOffice as a result of a code fork. I am retaining these decade-old notes in case they are still necessary with LibreOffice for SQL connectivity.
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/faq/Apps/LibreOffice/mysql.html
>> or
>>
http://linuxmafia.com/faq/Apps/LibreOffice/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/faq/Apps/LibreOffice/mysql.html
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
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)
To: luv@luv.asn.au
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.
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)
To: luv@luv.asn.au
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 IDs (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.