Can't connect with ODBC on FreeBSD

HI,

I am having a real problem trying to use ODBC on FreeBSD. I have been
trying for several hours with no success. Having no success I tried it
on another server (just in case all the installed crap on the
development server was causing an issue). However this did not help. I
am hoping one of you may be able to shed some light for me.

This is what I have :-

bash# uname -a
FreeBSD xxx 4.10-STABLE FreeBSD 4.10-STABLE #1: Thu Oct 7 11:10:57 GMT
2004 suffo_b(at)xxx:/usr/src/sys/compile/DELL-PE1650 i386

bash# pkg_info
XFree86-libraries-4.4.0_1 XFree86-4 libraries and headers
apache-2.0.50_3 Version 2 of Apache web server with prefork MPM.
autoconf-2.59_2 Automatically configure source code on many Un*x
platforms
bash-1.14.7 The GNU Borne Again Shell
cvsup-without-gui-16.1h General network file distribution system
optimized for CVS
expat-1.95.8 XML 1.0 parser written in C
fontconfig-2.2.3,1 An XML-based font configuration API for X Windows
freetype2-2.1.4_1 A free and portable TrueType font rendering engine
gettext-0.13.1_1 GNU gettext package
glib-1.2.10_11 Some useful routines of C programming (previous
stable vers
gmake-3.80_2 GNU version of 'make' utility
gtk-1.2.10_12 Gimp Toolkit for X11 GUI (previous stable version)
imake-4.3.0_1 Imake and other utilities from XFree86
jpeg-6b_1 IJG's jpeg compression utilities
libgnugetopt-1.2 GNU getopt library
libiconv-1.9.1_1 A character set conversion library
libiodbc-3.51.2 An ODBC 3.x driver manager, for universal data
source acces
libtool-1.3.5_2 Generic shared library support script (version 1.3)
libtool-1.5.8 Generic shared library support script (version 1.5)
m4-1.4.1 GNU m4
openssl-0.9.7d_1 SSL and crypto library
pdflib-5.0.1 A C library for dynamically generating PDF
pkgconfig-0.15.0 A utility used to retrieve information about
installed libr
png-1.2.5_2 Library for manipulating PNG images
postgresql-7.4.5 The most advanced open-source database available
anywhere
postgresql-odbc-7.2.5_1 PostgreSQL ODBC client support
rc_subr-1.31 Common startup and shutdown subroutines used by
scripts
rsync-2.5.6_1 A network file distribution/synchronization utility

bash# cat /usr/local/etc/libiodbc/odbc.ini
;
; odbc.ini
;
[ODBC Data Sources]
PgSQL=PostgreSQL

[PgSQL]
Driver=/usr/local/lib/psqlodbc.so
Description=Test
Servername=localhost
Port=5432
Protocol=6.4
FetchBufferSize=99
Username=test
Password=test
Database=test
ReadOnly=no
Debug=1
CommLog=1

[ODBC]
InstallDir=/usr/local/lib

bash# cat /usr/local/etc/libiodbc/odbcinst.ini
;
; odbcinst.ini
;
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/local/lib/psqlodbc.so
CommLog=1

[ODBC]
Trace=1
Debug=1
Pooling=No

bash# iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.51.0002.0224

Enter ODBC connect string (? shows list): ?

DSN | Description
---------------------------------------------------------------
|
P㿿㿿(㿿?\H,?H |
PgSQL | PostgreSQL

Enter ODBC connect string (? shows list): DSN=PgSQL

Have a nice day.bash#

Everything was built fresh from the FreeBSD ports. And I have tried
numerous odbc.ini & odbcinst.ini file combinations, starting with the
default sample, and then trying all differing ones I could find in
examples and howto's on google.

As you can see listing the available DSN's comes up with a few odd
things (I can't say any of the examples I have seen on the web had this
kind of corruption in it, but I get this on both servers. A second ?
returns just the 1 correct entry in the list, so I guess this is some
form of initialization error). Using the PgSQL DSN reports no error but
just does not work.

Postgres is running with TCP support, and works from from other
machines find using psql and libpq applications. I have turned
connection logging on and no connection attempt is made.

After the connection attempt above this file was created
/tmp/psqlodbc_root70765.log :-

bash# cat /tmp/psqlodbc_root70765.log
conn=134582272, PGAPI_DriverConnect( in)='dsn=test', fDriverCompletion=1
DSN info: DSN='test',server='',port='',dbase='',user='',passwd=''

onlyread='',protocol='',showoid='',fakeoidindex='',showsystable=''
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''

By adding Trace & Debug into the [PostgreSQL] section of the
odbcinst.ini file, and trying again a different file is created in
/tmp. This has an actual error it that may be of use in tracking this
issue :-

bash# cat /tmp/mylog_root71147.log
[134533120]globals.extra_systable_prefixes = 'dd_;'
[134533120][SQLAllocEnv][134533120]**** in PGAPI_AllocEnv **
[134533120]** exit PGAPI_AllocEnv: phenv = 134541472 **
[134533120][SQLAllocConnect][134533120]PGAPI_AllocConnect: entering...
[134533120]**** PGAPI_AllocConnect: henv = 134541472, conn = 134610944
[134533120]EN_add_connection: self = 134541472, conn = 134610944
[134533120] added at i =0, conn->henv = 134541472, conns[i]->henv
= 134541472
[134533120][SQLDriverConnect][134533120]PGAPI_DriverConnect: entering...
[134533120]**** PGAPI_DriverConnect: fDriverCompletion=1,
connStrIn='PgSQL'
[134533120]our_connect_string = 'PgSQL'
[134533120]globals.extra_systable_prefixes = ''
[134533120]our_connect_string = 'PgSQL'
[134533120][SQLError][134533120]**** PGAPI_Error: henv=0,
hdbc=134610944 hstmt=0
[134533120]**** PGAPI_ConnectError: hdbc=134610944 <250>
[134533120]enter CC_get_error
[134533120]enter CC_create_errormsg
[134533120]msg = ''
[134533120]exit CC_create_errormsg
[134533120]exit CC_get_error
[134533120]CC_Get_error returned nothing.
[134533120]**** PGAPI_Error exit code=100
[134533120][SQLFreeConnect][134533120]PGAPI_FreeConnect: entering...
[134533120]**** in PGAPI_FreeConnect: hdbc=134610944
[134533120]enter CC_Destructor, self=134610944
[134533120]in CC_Cleanup, self=134610944
[134533120]after CC_abort
[134533120]SOCK_Destructor
[134533120]after SOCK destructor
[134533120]exit CC_Cleanup
[134533120]after CC_Cleanup
[134533120]after free statement holders
[134533120]exit CC_Destructor
[134533120]PGAPI_FreeConnect: returning...

I hope I have included enough information above, if not please let me
know what else you would like to see.

Regards

Ben