Re: installation of odbc

On Sun, 2003-11-16 at 15:09, Wolfgang Meiners wrote:
> So i need help in the correct installation of postgresql with odbc support.

The following information is related to Debian packages. I hope you can
adapt it sufficiently to cope with SUSE's differences. The template
files referred to are attached.

HOW TO set up odbc-postgresql with unixodbc to allow access to other
Unix programs
====================================================================

1. Install odbc-postgresql and the packages unixodbc and odbcinst1

2. Add a PostgreSQL driver to /etc/odbcinst.ini:

# odbcinst -i -d -f /usr/lib/postgresql/share/odbcinst.ini.template

3. Add the template1 database (with read only access) to the default odbc.ini

# cat /usr/lib/postgresql/share/odbc.ini.template >>/etc/odbc.ini

Edit /etc/odbc.ini as required, to add extra databases, change the read only
status and so on.

4. Add ODBC required functions to the databases that you wish to access
from ODBC:

# su -s /bin/bash - postgres
$ for dbname in database1 database2 ...
do
psql -d $dbname < /usr/lib/psqlodbc/odbc.sql
done

If you include template1 in the list, any database created in future will
automatically have these functions included when it is created.

At this point, you should be able to use the ODBC access features of any
application to connect to any of the listed databases. For example, in
StarOffice, you can click New->Database, select the ODBC database type
and browse the list of databases from /etc/odbc.ini and ~/.odbc.ini.

Each user has or can have a file ~/.odbc.ini, whose structure is the same
as /etc/odbc.ini. This file is read together with the /etc/odbc.ini to give
a complete list of databases accessible to the user. (Of course, any
access restrictions imposed by pg_hba.conf are still applicable.)

You can create templates for different databases and allow users to add
them to their own ~/.odbc.ini. Use the odbc.ini template file provided in
/usr/lib/postgresql/share/odbc.ini.template as a model. Ignore the file
/usr/lib/postgresql/lib/odbcinst.ini, which is provided by the postgresql
package. It may be good for use with iodbc, but I have no experience
of using that.

A good and reliable way for a user to add a template to his ~/.odbc.ini
without risk of damaging the file is for him to use this command:

$ odbcinst -i -s -f /path/to/your/template/file

Note: I have had a report that OpenOffice objects to tabs and/or whitespace
in ~/.odbc.ini and /etc/odbc.ini. I have not found this problem with
StarOffice 5, however.

Accessing schemas
=================

If you have schemas that are not in the default search path ($user, public)
you have to set the search path in the connection settings of the odbc.ini
file. Here's an example, where the search path must be set to prod, public:

[Production]
Description = Production control
Driver = PostgreSQL
Trace = No
TraceFile = /tmp/odbc.log
Database = companydb
Servername = localhost
UserName = userme
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = Yes
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings = SET SEARCH_PATH TO prod, public

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Therefore being justified by faith, we have peace
with God through our Lord Jesus Christ." Romans 5:1