unixODBC vs postgeSQL driver on RHEL4? - Mailing list pgsql-odbc

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