Обсуждение: ODBC Version supporting specif databases?
Hi All, I am having connection issues with the 08.03.0100 version of the ODBC driver. What happens is that when I attempt to connect to an 8.2.6 PostgreSQL database, using the 08.3.0100 ODBC driver then i get an error connecting during authentication as follows: ------------------------------------------------------------------------- [28000][unixODBC]Communication error during authentication; Error while reading from the socket. [ISQL]ERROR: Could not SQLConnect ------------------------------------------------------------------------- However, if I use the 08.01.0200 version to connect to the same DSN, I am able to successfully connect? Is there a specific version of the ODBC driver that supports a specific version of the PostgreSQL database. The way the above two ODBC drivers were built are as follows: OS: Solaris 10 Update 3 ODBC Manager : unixODBC v1.2.12 I'm not too sure which version of the libpq library was used, but I know for certain that Solaris 10 Update 3 comes with a Sun built version of PostgreSQL 8.1, the one on the system I am trying to build on is version 8.1.10. Am I doing something wrong or are the versions of the odbc driver just not compatible with the versions of libpq that i am using to build with, and thus, unable to connect to the database, or, is it something completely different. If you need any additional info, please dont hesitate to ask. Thanks Bruce -- +-------------------------------------------------------+ | Bruce McAlister Blueface Ltd | | <bruce.mcalister@blueface.ie> http://www.blueface.ie | +-------------------------------------------------------+
Woops, the unixODBC version below should read 2.2.12, not 1.2.12. Bruce McAlister wrote: > Hi All, > > I am having connection issues with the 08.03.0100 version of the ODBC > driver. > > What happens is that when I attempt to connect to an 8.2.6 PostgreSQL > database, using the 08.3.0100 ODBC driver then i get an error connecting > during authentication as follows: > > ------------------------------------------------------------------------- > [28000][unixODBC]Communication error during authentication; > Error while reading from the socket. > [ISQL]ERROR: Could not SQLConnect > ------------------------------------------------------------------------- > > However, if I use the 08.01.0200 version to connect to the same DSN, I > am able to successfully connect? > > Is there a specific version of the ODBC driver that supports a specific > version of the PostgreSQL database. > > The way the above two ODBC drivers were built are as follows: > > OS: Solaris 10 Update 3 > ODBC Manager : unixODBC v1.2.12 > > I'm not too sure which version of the libpq library was used, but I know > for certain that Solaris 10 Update 3 comes with a Sun built version of > PostgreSQL 8.1, the one on the system I am trying to build on is version > 8.1.10. > > Am I doing something wrong or are the versions of the odbc driver just > not compatible with the versions of libpq that i am using to build with, > and thus, unable to connect to the database, or, is it something > completely different. > > If you need any additional info, please dont hesitate to ask. > > Thanks > Bruce -- +-------------------------------------------------------+ | Bruce McAlister Blueface Ltd | | <bruce.mcalister@blueface.ie> http://www.blueface.ie | +-------------------------------------------------------+
Hi All, Does anyone have any ideas/tips on how I could further debug this problem? Thanks Bruce Bruce McAlister wrote: > Hi All, > > I am having connection issues with the 08.03.0100 version of the ODBC > driver. > > What happens is that when I attempt to connect to an 8.2.6 PostgreSQL > database, using the 08.3.0100 ODBC driver then i get an error connecting > during authentication as follows: > > ------------------------------------------------------------------------- > [28000][unixODBC]Communication error during authentication; > Error while reading from the socket. > [ISQL]ERROR: Could not SQLConnect > ------------------------------------------------------------------------- > > However, if I use the 08.01.0200 version to connect to the same DSN, I > am able to successfully connect? > > Is there a specific version of the ODBC driver that supports a specific > version of the PostgreSQL database. > > The way the above two ODBC drivers were built are as follows: > > OS: Solaris 10 Update 3 > ODBC Manager : unixODBC v1.2.12 > > I'm not too sure which version of the libpq library was used, but I know > for certain that Solaris 10 Update 3 comes with a Sun built version of > PostgreSQL 8.1, the one on the system I am trying to build on is version > 8.1.10. > > Am I doing something wrong or are the versions of the odbc driver just > not compatible with the versions of libpq that i am using to build with, > and thus, unable to connect to the database, or, is it something > completely different. > > If you need any additional info, please dont hesitate to ask. > > Thanks > Bruce -- +-------------------------------------------------------+ | Bruce McAlister Blueface Ltd | | <bruce.mcalister@blueface.ie> http://www.blueface.ie | +-------------------------------------------------------+
Hi Bruce I guess that you need first enable Mylog and commlog and send both files to the list. This will help to trace the source code for errors. Bruce McAlister wrote: > Hi All, > > Does anyone have any ideas/tips on how I could further debug this > problem? > > Thanks > Bruce > > Bruce McAlister wrote: >> Hi All, >> >> I am having connection issues with the 08.03.0100 version of the ODBC >> driver. >> >> What happens is that when I attempt to connect to an 8.2.6 PostgreSQL >> database, using the 08.3.0100 ODBC driver then i get an error >> connecting during authentication as follows: >> >> ------------------------------------------------------------------------- >> >> [28000][unixODBC]Communication error during authentication; >> Error while reading from the socket. >> [ISQL]ERROR: Could not SQLConnect >> ------------------------------------------------------------------------- >> >> >> However, if I use the 08.01.0200 version to connect to the same DSN, >> I am able to successfully connect? >> >> Is there a specific version of the ODBC driver that supports a >> specific version of the PostgreSQL database. >> >> The way the above two ODBC drivers were built are as follows: >> >> OS: Solaris 10 Update 3 >> ODBC Manager : unixODBC v1.2.12 >> >> I'm not too sure which version of the libpq library was used, but I >> know for certain that Solaris 10 Update 3 comes with a Sun built >> version of PostgreSQL 8.1, the one on the system I am trying to build >> on is version 8.1.10. >> >> Am I doing something wrong or are the versions of the odbc driver >> just not compatible with the versions of libpq that i am using to >> build with, and thus, unable to connect to the database, or, is it >> something completely different. >> >> If you need any additional info, please dont hesitate to ask. >> >> Thanks >> Bruce > -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda.
Hi Luiz, I have attached the trace.log and there seems to be an issue in SQLAllocHandle.c. I tried to enable commlog and debug but i cant seem to find the files that they are meant to generate. Just to verify, do I add the following parameters to the dsn in odbc.ini? Debug = 1 CommLog = 1 Thanks Bruce Luiz K. Matsumura wrote: > Hi Bruce > > I guess that you need first enable Mylog and commlog and send both files > to the list. > This will help to trace the source code for errors. > > > Bruce McAlister wrote: >> Hi All, >> >> Does anyone have any ideas/tips on how I could further debug this >> problem? >> >> Thanks >> Bruce >> >> Bruce McAlister wrote: >>> Hi All, >>> >>> I am having connection issues with the 08.03.0100 version of the ODBC >>> driver. >>> >>> What happens is that when I attempt to connect to an 8.2.6 PostgreSQL >>> database, using the 08.3.0100 ODBC driver then i get an error >>> connecting during authentication as follows: >>> >>> ------------------------------------------------------------------------- >>> >>> [28000][unixODBC]Communication error during authentication; >>> Error while reading from the socket. >>> [ISQL]ERROR: Could not SQLConnect >>> ------------------------------------------------------------------------- >>> >>> >>> However, if I use the 08.01.0200 version to connect to the same DSN, >>> I am able to successfully connect? >>> >>> Is there a specific version of the ODBC driver that supports a >>> specific version of the PostgreSQL database. >>> >>> The way the above two ODBC drivers were built are as follows: >>> >>> OS: Solaris 10 Update 3 >>> ODBC Manager : unixODBC v1.2.12 >>> >>> I'm not too sure which version of the libpq library was used, but I >>> know for certain that Solaris 10 Update 3 comes with a Sun built >>> version of PostgreSQL 8.1, the one on the system I am trying to build >>> on is version 8.1.10. >>> >>> Am I doing something wrong or are the versions of the odbc driver >>> just not compatible with the versions of libpq that i am using to >>> build with, and thus, unable to connect to the database, or, is it >>> something completely different. >>> >>> If you need any additional info, please dont hesitate to ask. >>> >>> Thanks >>> Bruce >> > -- +-------------------------------------------------------+ | Bruce McAlister Blueface Ltd | | <bruce.mcalister@blueface.ie> http://www.blueface.ie | +-------------------------------------------------------+ [ODBC][7248][__handles.c][447] Exit:[SQL_SUCCESS] Environment = 8067ac0 [ODBC][7248][SQLAllocHandle.c][358] Entry: Handle Type = 2 Input Handle = 8067ac0 [ODBC][7248][SQLAllocHandle.c][476] Exit:[SQL_SUCCESS] Output Handle = 80680e8 [ODBC][7248][SQLConnect.c][3586] Entry: Connection = 80680e8 Server Name = [blueface-services][length = 17 (SQL_NTS)] User Name = [NULL] Authentication = [NULL] UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE' [ODBC][7248][SQLAllocHandle.c][331]Error: SQL_INVALID_HANDLE [ODBC][7248][SQLConnect.c][1570]Error: IM005 [ODBC][7248][SQLError.c][476]Error: SQL_INVALID_HANDLE [ODBC][7248][SQLError.c][424] Entry: Connection = 80680e8 SQLState = 8047a00 Native = 80479fc Message Text = 8047a10 Buffer Length = 500 Text Len Ptr = 80479fa [ODBC][7248][SQLError.c][461] Exit:[SQL_SUCCESS] SQLState = IM005 Native = 80479fc -> 0 Message Text = [[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed] [ODBC][7248][SQLError.c][424] Entry: Connection = 80680e8 SQLState = 8047a00 Native = 80479fc Message Text = 8047a10 Buffer Length = 500 Text Len Ptr = 80479fa [ODBC][7248][SQLError.c][461] Exit:[SQL_NO_DATA] [ODBC][7248][SQLError.c][504] Entry: Environment = 8067ac0 SQLState = 8047a00 Native = 80479fc Message Text = 8047a10 Buffer Length = 500 Text Len Ptr = 80479fa [ODBC][7248][SQLError.c][541] Exit:[SQL_NO_DATA] [ODBC][7248][SQLFreeHandle.c][273] Entry: Handle Type = 2 Input Handle = 80680e8 [ODBC][7248][SQLFreeHandle.c][188]Error: SQL_INVALID_HANDLE [ODBC][7248][SQLFreeHandle.c][324] Exit:[SQL_SUCCESS] [ODBC][7248][SQLFreeHandle.c][206] Entry: Handle Type = 1 Input Handle = 8067ac0
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