Re: Help with setting up postgres and odbc - Mailing list pgsql-interfaces

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"*
theconfig. 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*
installa 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@computerpark.co.uk


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

Tel:   (01536) 417155
Email: pbreen@computerpark.co.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@computerpark.co.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 

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