UNRESOLVED: odbc driver manager and postgresql odbc driver in Ubuntu

Hello all,
Sorry for cross-posting but my problem involves both unixodbc and
psqlodbc.

Thanks for all the replies to earlier postings. However, I'm afraid to sa=
y
that nothing has been resolved after two days of attempts. While this may
be a routine task for the gurus, it is all pretty confusing for this
newbie.

Could someone please paint a clear picture of what I need here? The basic
idea as far as I'm concerned:

1 - get postgresql up and running

2 - postgresql needs an odbc driver so that it can be accessible through =
ODBC

3 - get unixodbc up and running to enable ODBC on the system by using the
above driver to connect to postgresql


So I'm still at #2/#3. Here is what I don't understand:

1 - Is the postgresql driver procided by unixodbc appropriate or obsolete=
?
I don't really understand earlier replies on this (see below). The Ubuntu
packages "unixodbc-bin" and "unixodbc-dev" don't seem to include a
postgreSQL driver, but the latest source code does. So I removed the
packages and I'm now trying to build unixodbc from source. I get a build
error from "lex.l" and can't access a promising message on the mailing
list archive
(mail.easysoft.com/pipermail/unixodbc-announce/2006-January/ 000004.html).
I'm stuck.

2 - Is the Ubuntu package "odbc-postgresql" essentially the same as
building psqlodbc from source? If so, is this better than the unixodbc
postgresql driver? Also, how do I use this driver in unixodbc since it
doesn't have a "setup file"? Should I use iODBC instead of unixodbc?

3 - What is the "setup file" supposed to look like anyway? I'd be happy t=
o
create it if I knew what it is supposed to be. Why is it so hard to find?
I have the postgresql people telling me that it isn't from them but I
couldn't find it from the unixodbc-bin and unixodbc-dev packages either.


I had similar trouble setting this up last time and ended up leaving the
configuration to a sysadmin. It would be really good to be able to solve
this by myself this time around. I had odbc access to postgresql last wee=
k
before a forced reinstall. How I miss it now!

Apologies for my ignorance but rest assured that learning is taking place=
..
Cheers,

Dan


****************************************************
** earlier replies from pgsql-odbc@postgresql.org **

I can't say 2 different. unixodbc takes psqlodbc few years ago. So it has
obsolete psqlodbc driver. But they write theirs psqlodbcS.so which is onl=
y
for unixodbc settings purposes. That was I suggest in my previous message=
..
--
Ludek Finstrle


"Joshua D. Drake" writes:
> Tom Lane wrote:
>> There is a seriously obsolete postgres driver embedded in the unixodbc
source code. Don't use that one :-(

> The Dapper unixODBC driver is: 08.01.0102

Oh? unixODBC is at release 2.2.11 last I looked.

It could be that Ubuntu already pre-emptively nuked the old postgres
driver included with unixODBC --- which is something I have in mind to do
in the Red Hat release too ;-)
--
tom lane



******************************************************
** earlier reply from unixodbc-support@easysoft.com **

If you have the PG setup, it will be called libodbcpsqlS.so, you can=20
always build the package from source to get the lib.
--=20
Nick Gorham
Easysoft Limited





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: UNRESOLVED: odbc driver manager and postgresql odbc driver

> 2 - postgresql needs an odbc driver so that it can be accessible through ODBC
>
> 3 - get unixodbc up and running to enable ODBC on the system by using the
> above driver to connect to postgresql
>
>
> So I'm still at #2/#3. Here is what I don't understand:
>
> 1 - Is the postgresql driver procided by unixodbc appropriate or obsolete?

You should use the postgresql driver that is provided by the
distribution you are running. If you are running windows download the exe.

So for Ubuntu:

apt-get install odbc-postgresql

> I don't really understand earlier replies on this (see below). The Ubuntu
> packages "unixodbc-bin" and "unixodbc-dev" don't seem to include a
> postgreSQL driver, but the latest source code does. So I removed the
> packages and I'm now trying to build unixodbc from source. I get a build
> error from "lex.l" and can't access a promising message on the mailing
> list archive
> (mail.easysoft.com/pipermail/unixodbc-announce/2006-January/ 000004.html).
> I'm stuck.

odbc-postgresql is the driver
unixODBC is the driver manager.

So:

apt-get install unixodbc


> 2 - Is the Ubuntu package "odbc-postgresql" essentially the same as
> building psqlodbc from source?

Essentially yes. THe psqlodbc is probably more current but don't make
your life difficult.

If so, is this better than the unixodbc
> postgresql driver? Also, how do I use this driver in unixodbc since it
> doesn't have a "setup file"? Should I use iODBC instead of unixodbc?
>

No. Use unixODBC.

> 3 - What is the "setup file" supposed to look like anyway? I'd be happy to
> create it if I knew what it is supposed to be. Why is it so hard to find?
> I have the postgresql people telling me that it isn't from them but I
> couldn't find it from the unixodbc-bin and unixodbc-dev packages either.

Here are example working files:

/etc/odbc.ini:

[odbcng]
Description = Mammoth PostgreSQL
Driver = nobuffer
Trace = No
TraceFile =
Database = postgres
Servername = localhost
UserName = postgres
Password =
Port = 5432
Protocol = 7.0
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

/etc/odbcinst.ini:

[pgdg]
Description = PostgreSQL ODBC driver
Driver = /usr/lib/odbc/psqlodbc.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 2

[nobuffer]
Description = PostgreSQL ODBC driver
Driver = /usr/local/lib/libmodbc.so
Debug = 0
CommLog = 1
UsageCount = 2
USE_BUFFERING = False



> I had similar trouble setting this up last time and ended up leaving the
> configuration to a sysadmin. It would be really good to be able to solve
> this by myself this time around. I had odbc access to postgresql last week
> before a forced reinstall. How I miss it now!
>
> Apologies for my ignorance but rest assured that learning is taking place.
> Cheers,

I am curious why you are using ODBC to connect to PostgreSQL if you are
on Dapper? Are you using Open Office or somethign like that?

Sincerely,

Joshua D. Drake


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: UNRESOLVED: odbc driver manager and postgresql odbc

Hello,

> I am curious why you are using ODBC to connect to PostgreSQL if you are
> on Dapper? Are you using Open Office or somethign like that?

I use ODBC to access a postgis database from SAS (they provide database
interfaces to Oracle and ODBC, not to postgresql) and from R (using the
RODBC package which satisfactorily replaces the now defunct RDBI package)=
..
It also provides connectivity to MS Access, which some colleagues use.
Cheers,

D




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

RESOLVED: odbc driver manager and postgresql odbc driver

Success. Thanks Josh for the no-nonsense reply.

Basically, I didn't know the location of the appropriate libraries, and I
was confused about all the options available.

I now have the packages "unixodbc-bin" and "odbc-postgresql" installed.
The files /etc/odbc.in and /etc/odbcinst.ini are as follows:

[postgreodbc]
Description =3D PostgreSQL ODBC driver
Driver =3D /usr/lib/odbc/psqlodbc.so
Setup =3D /usr/lib/odbc/libodbcpsqlS.so
Debug =3D 0
CommLog =3D 1
UsageCount =3D 2


[gfsDB]
Description =3D PostgreSQL ODBC
Driver =3D postgreodbc
Trace =3D No
TraceFile =3D
Database =3D mydatabase
Servername =3D localhost
UserName =3D postgres
Password =3D
Port =3D 5432
Protocol =3D 7.0
ReadOnly =3D No
RowVersioning =3D No
ShowSystemTables =3D No
ShowOidColumn =3D No
FakeOidIndex =3D No
ConnSettings =3D



A final question then, part of the original confusion. Where is the file
"/usr/lib/odbc/libodbcpsqlS.so" from? It doesn't seem to be from the
odbc-postgresql package:

ricardd@octopus:~$ dpkg -L odbc-postgresql
/.
/usr
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/lib
/usr/lib/odbc
/usr/lib/odbc/psqlodbc.so
/usr/lib/odbc/psqlodbc.la
/usr/share
/usr/share/doc
/usr/share/doc/odbc-postgresql
/usr/share/doc/odbc-postgresql/docs
/usr/share/doc/odbc-postgresql/docs/howto-vb.html
/usr/share/doc/odbc-postgresql/docs/faq.html
/usr/share/doc/odbc-postgresql/docs/howto-accesslo.html
/usr/share/doc/odbc-postgresql/docs/howto-bo.html
/usr/share/doc/odbc-postgresql/docs/howto-ch.html
/usr/share/doc/odbc-postgresql/docs/index.html
/usr/share/doc/odbc-postgresql/docs/win32-compilation.html
/usr/share/doc/odbc-postgresql/docs/config.html
/usr/share/doc/odbc-postgresql/docs/howto-csharp.html
/usr/share/doc/odbc-postgresql/docs/howto-accessvba.html
/usr/share/doc/odbc-postgresql/docs/unix-compilation.html
/usr/share/doc/odbc-postgresql/docs/howto-vblo.html
/usr/share/doc/odbc-postgresql/README.Debian
/usr/share/doc/odbc-postgresql/copyright
/usr/share/doc/odbc-postgresql/examples
/usr/share/doc/odbc-postgresql/examples/odbc.ini.template
/usr/share/doc/odbc-postgresql/changelog.Debian.gz
/usr/share/psqlodbc
/usr/share/psqlodbc/odbcinst.ini.template
/usr/lib/postgresql/lib/psqlodbc.so

Thanks again.

Dan



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: RESOLVED: odbc driver manager and postgresql odbc driver

> A final question then, part of the original confusion. Where is the file
> "/usr/lib/odbc/libodbcpsqlS.so" from? It doesn't seem to be from the
> odbc-postgresql package:
>

apt-file is your friend.


root@scratch:/usr/lib/odbc# apt-file search libodbcpsqlS.so
odbcinst1debian1: usr/lib/odbc/libodbcpsqlS.so
root@scratch:/usr/lib/odbc#


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly