Re: ODBC Version supporting specif databases?

Hi All,

Sorry for cross-posting, but I would appreciate all the help I can get
to try and get to the bottom of this issue.

I have now tried 3 versions of the pgsqlODBC driver, of which

08.03.0100, and
08.02.0500

fail with the following error code when attempting to connect to a database:

[IM005][unixODBC][Driver Manager]Driver's SQLAllocHandle on
SQL_HANDLE_DBC failed

The only driver that is able to successfully connect to the database is
the 08.01.0200 version of the driver.

To try and assist in finding what it is that is going wrong (or
potentially, what I am doing wrong), I will outline how I have built the
driver and how I am attempting to connect.

I have attached the script that I have used to build PostgreSQL 8.2.6,
and all three versions of the pgsqlODBC driver, this will illustrate the
environment and build options I have specified.

The OS I am building on is Solaris 10 Update 3. The version of unixODBC
I am using is v2.2.12 built from source. The database I am trying to
connect to is PostgreSQL v8.2.6.

my odbcinst.ini file looks like this:

--------------------------------------------------------------------------
[ODBC]
Trace = yes
TraceFile = /tmp/trace.log

[PostgreSQL]
Description = PostgreSQL Official ODBC Driver
Driver = /opt/blueface/lib/psqlodbcw.so
Debug = 1
CommLog = 1
--------------------------------------------------------------------------

my odbc.ini file looks like this:

--------------------------------------------------------------------------
[blueface-services]
Description = Services Database
Driver = PostgreSQL
Database = blueface-service
Servername = 192.168.10.181
UserName = username
Password = password
Port = 5432
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
--------------------------------------------------------------------------

I cannot find any commlog or debug logs for 08.03.0100 or 08.02.0500
anywhere. I assume this to be because the driver fails before it can
even initialize its own logging.

Here is the output of "ldd -r [pgsqlODBC driver]" for each of the three
deivers:

08.03.0100
--------------------------------------------------------------------------
ldd -r ./psqlodbcw.so
         libpq.so.5 =>    /opt/postgresql-8.2.6/lib/libpq.so.5
         libpthread.so.1 =>       /lib/libpthread.so.1
         libodbcinst.so.1 =>      /opt/blueface/lib/libodbcinst.so.1
         libodbc.so.1 =>  /opt/blueface/lib/libodbc.so.1
         libc.so.1 =>     /lib/libc.so.1
         libgcc_s.so.1 =>         /usr/sfw/lib/libgcc_s.so.1
         libssl.so.0.9.7 =>       /usr/sfw/lib/libssl.so.0.9.7
         libcrypto.so.0.9.7 =>    /usr/sfw/lib/libcrypto.so.0.9.7
         libsocket.so.1 =>        /lib/libsocket.so.1
         libdl.so.1 =>    /lib/libdl.so.1
         libthread.so.1 =>        /lib/libthread.so.1
         libnsl.so.1 =>   /lib/libnsl.so.1
         libmp.so.2 =>    /lib/libmp.so.2
         libmd.so.1 =>    /lib/libmd.so.1
         libscf.so.1 =>   /lib/libscf.so.1
         libdoor.so.1 =>  /lib/libdoor.so.1
         libuutil.so.1 =>         /lib/libuutil.so.1
         libgen.so.1 =>   /lib/libgen.so.1
         libm.so.2 =>     /lib/libm.so.2
--------------------------------------------------------------------------

08.02.0500
--------------------------------------------------------------------------
ldd -r ./psqlodbcw.so
         libpq.so.5 =>    /opt/postgresql-8.2.6/lib/libpq.so.5
         libpthread.so.1 =>       /lib/libpthread.so.1
         libodbcinst.so.1 =>      /opt/blueface/lib/libodbcinst.so.1
         libodbc.so.1 =>  /opt/blueface/lib/libodbc.so.1
         libc.so.1 =>     /lib/libc.so.1
         libgcc_s.so.1 =>         /usr/sfw/lib/libgcc_s.so.1
         libssl.so.0.9.7 =>       /usr/sfw/lib/libssl.so.0.9.7
         libcrypto.so.0.9.7 =>    /usr/sfw/lib/libcrypto.so.0.9.7
         libsocket.so.1 =>        /lib/libsocket.so.1
         libdl.so.1 =>    /lib/libdl.so.1
         libthread.so.1 =>        /lib/libthread.so.1
         libnsl.so.1 =>   /lib/libnsl.so.1
         libmp.so.2 =>    /lib/libmp.so.2
         libmd.so.1 =>    /lib/libmd.so.1
         libscf.so.1 =>   /lib/libscf.so.1
         libdoor.so.1 =>  /lib/libdoor.so.1
         libuutil.so.1 =>         /lib/libuutil.so.1
         libgen.so.1 =>   /lib/libgen.so.1
         libm.so.2 =>     /lib/libm.so.2
--------------------------------------------------------------------------

08.01.0200
--------------------------------------------------------------------------
ldd -r ./psqlodbcw.so
         libodbcinst.so.1 =>      /opt/blueface/lib/libodbcinst.so.1
         libpq.so.5 =>    /opt/postgresql-8.2.6/lib/libpq.so.5
         libc.so.1 =>     /lib/libc.so.1
         libgcc_s.so.1 =>         /usr/sfw/lib/libgcc_s.so.1
         libdl.so.1 =>    /lib/libdl.so.1
         libthread.so.1 =>        /lib/libthread.so.1
         libssl.so.0.9.7 =>       /usr/sfw/lib/libssl.so.0.9.7
         libcrypto.so.0.9.7 =>    /usr/sfw/lib/libcrypto.so.0.9.7
         libsocket.so.1 =>        /lib/libsocket.so.1
         libpthread.so.1 =>       /lib/libpthread.so.1
         libnsl.so.1 =>   /lib/libnsl.so.1
         libmp.so.2 =>    /lib/libmp.so.2
         libmd.so.1 =>    /lib/libmd.so.1
         libscf.so.1 =>   /lib/libscf.so.1
         libdoor.so.1 =>  /lib/libdoor.so.1
         libuutil.so.1 =>         /lib/libuutil.so.1
         libgen.so.1 =>   /lib/libgen.so.1
         libm.so.2 =>     /lib/libm.so.2
--------------------------------------------------------------------------

The failure messages I get are as follows:

08.03.0100
--------------------------------------------------------------------------
isql -vvv blueface-services
[IM005][unixODBC][Driver Manager]Driver's SQLAllocHandle on
SQL_HANDLE_DBC failed
[ISQL]ERROR: Could not SQLConnect
--------------------------------------------------------------------------

08.02.0500
--------------------------------------------------------------------------
isql -vvv blueface-services
[IM005][unixODBC][Driver Manager]Driver's SQLAllocHandle on
SQL_HANDLE_DBC failed
[ISQL]ERROR: Could not SQLConnect
--------------------------------------------------------------------------

I have attached the trace/commlog/debug logs of all three drivers, the
08.01.0200 logs are of a successful connection, the rest are failures.
In the case of the failures, there are no commlog/debug logs generated.

If you require any additional information, please dont hesitate to ask.

Thanks
Bruce
#!/bin/ksh

PATH=/usr/xpg4/bin:/usr/bin:/usr/sbin:/usr/sfw/bin:/usr/ccs/bin:/opt/sfw/bin:/opt/blueface/bin
export PATH

GNUCC='/usr/sfw/bin/gcc'
GNUCXX='/usr/sfw/bin/g++'
SUNCC='/opt/SUNWspro/bin/cc'
SUNCXX='/opt/SUNWspro/bin/CC'
CC=$GNUCC
CXX=$GNUCXX
export CC CXX

ASTCFLAGS='-I/usr/sfw/include -I/opt/blueface/include -I/opt/sfw/include'
ASTLDFLAGS='-L/usr/sfw/lib -R/usr/sfw/lib -L/opt/blueface/lib -R/opt/blueface/lib -L/opt/sfw/lib -R/opt/sfw/lib'
CFLAGS=$ASTCFLAGS
LDFLAGS=$ASTLDFLAGS
export ASTCFLAGS ASTLDFLAGS CFLAGS LDFLAGS

NOW='/var/tmp/timer.txt'
GLOBALBASE='/opt/blueface'
BASEDIR=${GLOBALBASE}

umask 022

# [ -d ${GLOBALBASE} ] && rm -rf ${GLOBALBASE}
# mkdir -p ${GLOBALBASE}
# [ -d /opt/asterisk ] && rm -rf /opt/asterisk
# mkdir -p /opt/asterisk

function build_pkg {

  LDIR=$(pwd)

  cd /var/tmp
  echo "PKG=BFS${PKGNAME}"         >  ./pkginfo
  echo "NAME=${NAME}"              >> ./pkginfo
  echo "ARCH=$(uname -p)"          >> ./pkginfo
  echo "CATEGORY=application"      >> ./pkginfo
  echo "VERSION=${SFWVERSION}"     >> ./pkginfo
  echo "VENDOR=${VENDORURL}"       >> ./pkginfo
  echo "EMAIL=support@blueface.ie" >> ./pkginfo
  echo "DESC=${DESCRIPTION}"       >> ./pkginfo

  echo "" > ./depend
  echo "" > ./copyright

  (
    echo "i pkginfo";
    echo "i depend";
    echo "i copyright";
  ) > /var/tmp/prototype
  cd $BASEDIR
  chown -R root:other ./*
  find . -newer $NOW | pkgproto >> /var/tmp/prototype
  cd /var/tmp
  pkgmk -o -b $BASEDIR -a $(uname -p) BASEDIR=${BASEDIR}
  pkgtrans -s /var/spool/pkg /tmp/${PKGFILENAME} BFS${PKGNAME}
  rm -rf /var/spool/pkg/BFS${PKGNAME}
  gzip -9 /tmp/${PKGFILENAME}
  cd $LDIR
}

#
# psqlODBC
#

# [ -d ./postgresql-8.2.6 ] && rm -rf ./postgresql-8.2.6
# gtar zxf ../source/postgresql-8.2.6.tar.gz
# cd ./postgresql-8.2.6
# PGSRC=$(pwd)
# export CFLAGS="$CLAGS -I/usr/xpg4/include"
# export LDFLAGS="$LDFLAGS -L/usr/xpg4/lib -R/usr/xpg4/lib"
#
# ./configure --prefix=/opt/postgresql-8.2.6 \
#             --enable-integer-datetimes \
#             --enable-thread-safety \
#             --with-tcl \
#             --with-tclconfig=/usr/sfw/lib \
#             --with-pam \
#             --with-openssl \
#             --enable-nls \
#             --without-readline
#
# [ $? == 0 ] && gmake
# [ $? == 0 ] && gmake install
# cd ../

# [ -d ./psqlodbc-08.03.0100 ] && rm -rf ./psqlodbc-08.03.0100
# gtar zxf ../source/psqlodbc-08.03.0100.tar.gz
#
# cd ./psqlodbc-08.03.0100
#
#export PG_CONFIG='/opt/postgresql-8.2.6/bin/pg_config'
#
#./configure --prefix=/opt/blueface --with-unixodbc=/opt/blueface/bin/odbc_config --enable-iconv
#[ $? == 0 ] && gmake
#[ $? == 0 ] && gmake check
#[ $? == 0 ] && gmake install

#[ -d ./psqlodbc-08.02.0500 ] && rm -rf ./psqlodbc-08.02.0500
#gtar zxf ../source/psqlodbc-08.02.0500.tar.gz
#
#cd ./psqlodbc-08.02.0500
#
## export CFLAGS="-I${PGSRC}/include $CFLAGS"
#export PG_CONFIG='/opt/postgresql-8.2.6/bin/pg_config'
#
#./configure --prefix=/opt/blueface --with-unixodbc=/opt/blueface/bin/odbc_config --enable-iconv
#[ $? == 0 ] && gmake
#[ $? == 0 ] && gmake check
#[ $? == 0 ] && gmake install

[ -d ./psqlodbc-08.01.0200 ] && rm -rf ./psqlodbc-08.01.0200
gtar zxf ../source/psqlodbc-08.01.0200.tar.gz

cd ./psqlodbc-08.01.0200

# export CFLAGS="-I${PGSRC}/include $CFLAGS"
export PG_CONFIG='/opt/postgresql-8.2.6/bin/pg_config'

./configure --prefix=/opt/blueface --with-unixodbc
[ $? == 0 ] && gmake
[ $? == 0 ] && gmake check
[ $? == 0 ] && gmake install