Re: Help with setting up postgres and odbc

Hello Mike,

It's been a while since I last played with ODBC/Postgres but if I
remember rightly, I think you've got your driver name & DSN name mixed
up. The driver "section" name in your .ini file is PostgreSQL & so is
your DSN "section" name. Maybe this is confusing ODBC? I think your
DSN name wants to be something different, maybe named after your db
(Datacc)? Just a guess.

Something like this:

[Datacc]
Description = Datacc
Driver = PostgreSQL
.
.
.

Here's my notes from when I last played with it:

[snip] ------------------------

Setting up unixODBC
===================

Get unixODBC
------------
Get unixODBC (either RPMs or src) available from
"http://www.unixodbc.org" & install it. By default it installs under
"/usr/local" as follows:

* the drivers (.so libs) install under "/usr/local/lib"
* the programs (odbcinst etc.) install under "/usr/local/bin"
* the config. files (.ini) install under "/usr/local/etc"

Installing drivers & DSNs
-------------------------
If the gui tools (ODBCConfig etc.) haven't been installed, then the
following commands are needed to install drivers & DSNs manually (see
below for examples
of the template.ini files):

* install a driver - odbcinst -i -d -f template.ini
* install a system DSN - odbcinst -i -s -f template.ini -l
* install a user DSN - odbcinst -i -s -f template.ini -h

For each driver there are 2 libs (excluding the symlinks), the driver &
the driver's setup routines. The setup lib is named the same as the
driver except it has an "S" as the last character before the file
suffix, e.g.:

* the driver lib - libodbcpsql.so.1.0.0
* the setup lib - libodbcpsqlS.so.1.0.0

Uninstalling drivers & DSNs
-------------------------
If the gui tools (ODBCConfig etc.) haven't been installed, then the
following commands are needed to uninstall drivers & DSNs manually:

* uninstall a driver - odbcinst -u -d -n driver_name *
uninstall a DSN (system OR user) - odbcinst -u -s -n dsn_name

where "driver_name" & "dsn_name" are the names (the section heading text
between the [ ] brackets) found in odbcinst.ini (driver_name) & odbc.ini
(dsn_name).

Example template.ini files
--------------------------
The template.ini files I created to install the PostgreSQL ODBC driver &
install the resources db as a PostgreSQL ODBC DSN are as follows:

Template file to install the driver
("/usr/local/lib/odbcinst-psql-1.ini")
-------------------------------------------------------------------------------
[PostgreSQL]
Description = PostgreSQL ODBC driver for Linux
Driver = /usr/local/lib/libodbcpsql.so.1
Setup = /usr/local/lib/libodbcpsqlS.so.1
ExtraSysTablePrefixes =
-------------------------------------------------------------------------------

Template file to install the DSN ("/usr/local/lib/odbc-psql-1.ini")
-------------------------------------------------------------------------------
[resources]
Description = CP resources db
Driver = PostgreSQL
Trace = No
TraceFile = sql.log
Database = resources
Servername = venus.computerpark.co.uk
UserName = postgres
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
ExtraSysTablePrefixes =
-------------------------------------------------------------------------------

[snip] ------------------------

> I seem to be hitting a bit of a wall here.
>
> This is what I have got
>
> /etc/odbc.ini
> [PostgreSQL]
> Description = Datacc
> Driver = PostgreSQL
> Trace = Yes
> TraceFile = sql.log
> Database = data_cc
> Servername = localhost
> Host = localhost
> UserName =
> Password =
> Port = 5432
> Servertype = postgres
> Protocol = 6.4
> ReadOnly = No
> RowVersioning = No
> ShowSystemTables = No
> ShowOidColumn = No
> FakeOidIndex = No
> ConnSettings =
>
> [Default]
> Driver = /usr/lib/psqlodbc.so
>
> [ODBC]
> InstallDir = /usr/lib/libodbc.so
>
> /etc/odbcinst.ini
>
> # Example driver definitinions
> #
> #
>
> # Included in the unixODBC package
> [PostgreSQL]
> Description = ODBC for PostgreSQL
> Driver = /usr/lib/psqlodbc.so
> Setup = /usr/lib/libodbcpsqlS.so.1
> FileUsage = 1
>
>
> # From the MyODBC package
> #[MySQL]
> #Description = ODBC for MySQL
> #Driver = /usr/lib/libmyodbc.so
> #FileUsage = 1
>
> using odbctest I get this when I try to connect using full connect
> SQLConnect() failed
> Diag(hdbc 0x82bd1b0):IM002:201:[unixODBC]Missing server name, port, or
> database name in call to CC_connect.
>
> Any help appreciated
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Paul M. Breen, Software Engineer - Computer Park Ltd.

Tel: (01536) 417155
Email: pbreen(at)computerpark(dot)co(dot)uk

--
Paul M. Breen, Software Engineer - Computer Park Ltd.

Tel: (01536) 417155
Email: pbreen(at)computerpark(dot)co(dot)uk

---------------------------------------------------------

This private and confidential e-mail has been sent to you
by Computer Park Ltd.

If you are not the intended recipient of this e-mail and
have received it in error, please notify us via the email
address or telephone number below, and then delete it from
your mailbox.

Email: mailbox(at)computerpark(dot)co(dot)uk

Tel: +44 (0) 1536 417155
Fax: +44 (0) 1536 417566

Head Office:
Computer Park Ltd, Broughton Grange, Headlands, Kettering
Northamptonshire NN15 6XA

Registered in England: 3022961.

Registered Office:
6 North Street, Oundle, Peterborough PE8 4AL

=========================================================