Hello,
I have utterly failed getting the postgreSQL ODBC driver working
with unixODBC on Red Hat Enterprise Linux 4 for Intel (actually CentOS4
but it's supposed to be the same thing) using the standard RPM packages.
However, the driver provided with the unixODBC package works fine.
I have tried lots of different tweaks in the config files and have concluded
I must be missing something basic.
I would simply shrug and use the unixODBC-provided driver, except
that I'd like to use the most current ODBC driver and my understanding
is the unixODBC-provided driver is somewhat out of date. Plus it's
pretty damn annoying that I can't get it to work at all with the
PostgreSQL-provided driver.
Here's what I'm using:

postgresql-server-7.4.8-1.RHEL4.1
postgresql-odbc-7.3-8.RHEL4.1
postgresql-libs-7.4.8-1.RHEL4.1
postgresql-7.4.8-1.RHEL4.1

unixODBC-devel-2.2.9-1
unixODBC-2.2.9-1

I have slightly modified /etc/odbcinst.ini by adding a new entry PostgreSQL2
for the postgreSQL driver:

-- /etc/odbcinst.ini ------------------------------------------
# Included in the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

# Added by me
[PostgreSQL2]
Description = ODBC for PostgreSQL (native)
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/psqlodbc.so
---------------------------------------------------------------

Here's my ~/.odbc.ini file:

-- ~/.odbc.ini ------------------------------------------------
[ODBC Data Sources]
comicnetODBC = ODBC access comicnet database

[comicnetODBC]
Description = comicnet database
Driver = PostgreSQL2
Database = comicnet
Servername = localhost
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Debug = 1
CommLog = 1
Trace = No
TraceFile = sql.log

[ODBC]
InstallDir = /usr/lib
---------------------------------------------------------------

I'm pretty sure my ~/.odbc.ini file has some cruft in it, but it's
tough to find documentation for unixODBC and PostgreSQL ODBC
saying exactly what parameters are needed and what proper values are
for each and exactly what they do.

Anyway, if I use Driver=PostgreSQL it works (the unixODBC-provided
driver), but if I use Driver=PostgreSQL2 it fails (the
PostgreSQL-provided driver).

I judge success with the unixODBC isql program; in success with the
unixODBC-prodived driver it connects and I can type SQL queries;
in failure with the postgreSQL-provided driver it gives the message:

[gamartin@localhost ~]$ isql -v comicnetODBC gamartin
[08001][unixODBC]Could not connect to the server;
Could not connect to remote socket.
[ISQL]ERROR: Could not SQLConnect

If anybody has read this far, what I really want is to find anybody
with a working configuration on RHEL4 from the RPM packages who can
post working config files (if some super-genius can spot what's
wrong using the information provided that's even better).

NOTE: I do have trace files, which are included below, although I
can't figure out anything useful from them...

Greg Martin
gamartin@shout.net

---------------------------------------------------------------
[gamartin@localhost ~]$ cat /tmp/mylog_gamartin2831.log
CC_connect: entering...
CC_connect(): DSN = 'comicnetODBC', server = 'localhost', port = '5432', database = 'comicnet', username = 'gamartin', password='xxxxx'
connecting to the server socket...
connection to the server socket failed.
CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=101, errmsg='Could not connect to the server'
[SQLError]**** PGAPI_Error: henv=0, hdbc=146218920 hstmt=0
**** PGAPI_ConnectError: hdbc=146218920 <513>
enter CC_get_error
enter CC_create_errormsg
msg = 'Could not connect to the server'
exit CC_create_errormsg
exit CC_get_error
CC_get_error: status = 101, msg = #Could not connect to the server;
Could not connect to remote socket.#
szSqlState = '08001',len=68, szError='Could not connect to the server;
Could not connect to remote socket.'
**** PGAPI_Error exit code=0
[SQLError]**** PGAPI_Error: henv=0, hdbc=146218920 hstmt=0
**** PGAPI_ConnectError: hdbc=146218920 <513>
enter CC_get_error
exit CC_get_error
CC_Get_error returned nothing.
**** PGAPI_Error exit code=100
[SQLFreeConnect]PGAPI_FreeConnect: entering...
**** in PGAPI_FreeConnect: hdbc=146218920
enter CC_Destructor, self=146218920
in CC_Cleanup, self=146218920
after CC_abort
SOCK_Destructor
after SOCK destructor
exit CC_Cleanup
after CC_Cleanup
after free statement holders
exit CC_Destructor
PGAPI_FreeConnect: returning...
[SQLFreeEnv]**** in PGAPI_FreeEnv: env = 146202848 **
in EN_Destructor, self=146202848
exit EN_Destructor: rv = 1
ok

---------------------------------------------------------------
[gamartin@localhost ~]$ cat /tmp/psqlodbc_gamartin2831.log
conn = 146218920, PGAPI_Connect(DSN='comicnetODBC', UID='gamartin', PWD='xxxxx')
Global Options: Version='07.03.0200', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'
CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=101, errmsg='Could not connect to the server'
------------------------------------------------------------
henv=146202848, conn=146218920, status=0, num_stmts=16
sock=146202384, stmts=146206088, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=4, errormsg='Could not connect to remote socket.'
buffer_in=146208816, buffer_out=146229880
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0


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

> Hello,
> I have utterly failed getting the postgreSQL ODBC driver working
> with unixODBC on Red Hat Enterprise Linux 4 for Intel (actually CentOS4
> but it's supposed to be the same thing) using the standard RPM packages.
> However, the driver provided with the unixODBC package works fine.
> I have tried lots of different tweaks in the config files and have
> concluded
> I must be missing something basic.
> I would simply shrug and use the unixODBC-provided driver, except
> that I'd like to use the most current ODBC driver and my understanding
> is the unixODBC-provided driver is somewhat out of date. Plus it's
> pretty damn annoying that I can't get it to work at all with the
> PostgreSQL-provided driver.
> Here's what I'm using:
>
> postgresql-server-7.4.8-1.RHEL4.1
> postgresql-odbc-7.3-8.RHEL4.1
> postgresql-libs-7.4.8-1.RHEL4.1
> postgresql-7.4.8-1.RHEL4.1
>
> unixODBC-devel-2.2.9-1
> unixODBC-2.2.9-1
>

hi,
have the same problem the other way around?!
here is what i have:
postgresql-7.3.4-3.rhl9.i386.rpm
postgresql-devel-7.3.4-3.rhl9.i386.rpm
postgresql-libs-7.3.4-3.rhl9.i386.rpm
postgresql-odbc-7.3-4.i386.rpm
postgresql-server-7.3.4-3.rhl9.i386.rpm

unixODBC-2.2.11-1.i386.rpm
unixODBC-devel-2.2.11-1.i386.rpm

BTW, how does your /var/lib/pgsql/data/pg_hba.conf look like?

;-) rainer



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

Rainer,
I used the standard /var/lib/pgsql/data/pg_hba.conf provided
with the RHEL4/CentOS4 RPM's... the only non-commented line is:

local all all ident sameuser

I must confess I don't fully understand the entries here, but I didn't touch
them and the comments say this gives everyone local access. Also since
the unixODBC driver works it seems like pg_hba.conf can't be the problem...

Another possibility I don't understand at all is that this is some byproduct
of Security Enhanced Linux (SEL) preventing the 2nd driver from running...
anybody know enough to comment on this?

Greg Martin
gamartin@shout.net

On Mon, 17 Oct 2005, Rainer Hochreiter wrote:

>
> > Hello,
> > I have utterly failed getting the postgreSQL ODBC driver working
> > with unixODBC on Red Hat Enterprise Linux 4 for Intel (actually CentOS4
> > but it's supposed to be the same thing) using the standard RPM packages.
> > However, the driver provided with the unixODBC package works fine.
> > I have tried lots of different tweaks in the config files and have
> > concluded
> > I must be missing something basic.
> > I would simply shrug and use the unixODBC-provided driver, except
> > that I'd like to use the most current ODBC driver and my understanding
> > is the unixODBC-provided driver is somewhat out of date. Plus it's
> > pretty damn annoying that I can't get it to work at all with the
> > PostgreSQL-provided driver.
> > Here's what I'm using:
> >
> > postgresql-server-7.4.8-1.RHEL4.1
> > postgresql-odbc-7.3-8.RHEL4.1
> > postgresql-libs-7.4.8-1.RHEL4.1
> > postgresql-7.4.8-1.RHEL4.1
> >
> > unixODBC-devel-2.2.9-1
> > unixODBC-2.2.9-1
> >
>
> hi,
> have the same problem the other way around?!
> here is what i have:
> postgresql-7.3.4-3.rhl9.i386.rpm
> postgresql-devel-7.3.4-3.rhl9.i386.rpm
> postgresql-libs-7.3.4-3.rhl9.i386.rpm
> postgresql-odbc-7.3-4.i386.rpm
> postgresql-server-7.3.4-3.rhl9.i386.rpm
>
> unixODBC-2.2.11-1.i386.rpm
> unixODBC-devel-2.2.11-1.i386.rpm
>
> BTW, how does your /var/lib/pgsql/data/pg_hba.conf look like?
>
> ;-) rainer

---------------------------(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

Good news -- I have achieved closure on this issue:

On Mon, 17 Oct 2005, Greg Martin wrote:
> I have utterly failed getting the postgreSQL ODBC driver working
> with unixODBC on Red Hat Enterprise Linux 4 for Intel (actually CentOS4
> but it's supposed to be the same thing) using the standard RPM packages.
> However, the driver provided with the unixODBC package works fine.
> I have tried lots of different tweaks in the config files and have concluded
> I must be missing something basic.
> I would simply shrug and use the unixODBC-provided driver, except
> that I'd like to use the most current ODBC driver and my understanding
> is the unixODBC-provided driver is somewhat out of date. Plus it's
> pretty damn annoying that I can't get it to work at all with the
> PostgreSQL-provided driver.

STEP 1: After playing around with RHEL for a while I decided to disable
SEL (in /etc/selinux/config) -- this wasn't responsible for my problem,
but I was able to get a much better understanding of what was going
on when I turned SEL off. (NOTE: I'm not ready to administer SEL on a
complex real-world production system yet; lots of learning still needed)

STEP 2: The key piece of information I was missing was that the
unixODBC-provided driver works with Unix-domain sockets, while the
PostgreSQL-provided driver works with TCP/IP sockets! RHEL / CentOS 4
comes with PostgreSQL TCP/IP socket access turned off, so that's why
the unixODBC-provided driver worked while the PostgreSQL-provided
driver didn't.

SOLUTION: Turn on TCP/IP socket access:
* edit /var/lib/pgsql/data/postgresql.conf and add for TCP/IP access:
tcpip_socket = true
* edit /var/lib/pgsql/data/pg_hba.conf and add for TCP/IP access:
host all all 127.0.0.1 255.255.255.0 trust
host all all 0.0.0.0 255.255.255.255 reject

I should mention for completeness that I went on to do further testing
of the PostgreSQL-provided ODBC driver and found it deficient.
1) It's stuck at ODBC version 2.5, which is problematic for applications
that generate ODBC 3.0 function calls.
2) The driver still has serious bugs, for example I can do:
SELECT COUNT(*) FROM some_table;
and occasionally the result comes back with DataType = varchar
meaning my attempt to read it into a long will fail (I'm using OTL
to generate my ODBC calls).

This last problem was serious enough that the only viable option for me
(since I don't want to deviate too far from the RHEL4 / CentOS 4 base
install) was to purchase the OpenLink ODBC driver. I have only just
started using it, but so far it seems a clear cut above the others.

Anyway, thanks to everyone for working on PostgreSQL, and I'm sure the
PostgreSQL ODBC driver will catch up to OpenLink in a couple years.

Greg Martin
gamartin@shout.net

---------------------------(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