Thread: psqlODBC driver does not work
I've problem getting the psqlODBC driver and ODBC in
general to work on RedHat 9. I've built and installed
PostgreSQL ver. 7.4.2, unixODBC driver manager
ver. 2.2.8, and psqlODBC driver ver. 07.03.0200 without
errors. In the documentation I've seen that the
driver's name is libpsqlodbc.so, but I cannot find
libpsqlodbc.so anywhere on the system. The psqlODBC
distribution builds 'psqlodbc.so' and 'psqlodbc.la'. I
tried using 'psqlodbc.so' in 'odbcinst.ini' as a value
of the 'Driver' field, but I could not connect to my
database using 'isql', namely, once I type the command
isql -v examples_ODBC userfoo passwd
and press ENTER 'isql' hangs and I have to kill it with
^C.
Could somebody point me in the right direction. I
don't even sure of the right library name. EVEN SHORT
SUGGESTIONS APPRECIATED :-)
If relevant, this is the log of this run:
---psqlodbc_userfoo626.log-----------------------------
conn = 134628032, PGAPI_Connect(DSN='examples_ODBC',
UID='userfoo', 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=134628032, query=' '
---(END OF) psqlodbc_userfoo626.log--------------------
I've built ODBC driver from unixODBC distribution:
'libodbcpsql.so' and connected using that one with
isql. I could run queries, but 'isql' hangs and has to
be killed with ^C as described above once I enter
'quit' on its prompt. Moreover, I could connect
if I set authentication in 'pg_hba.conf' for 'local'
and 'host' to 'trust' or 'passwd', but connection fails
if I set it to 'crypt' or 'md5'.
If relevant, here are my '.ini' files:
------odbcinst.ini---------
[PostgreSQL_unixODBC]
Description = unixODBC for PostgreSQL
Driver = /usr/local/lib/libodbcpsql.so
Setup = /usr/local/lib/libodbcpsqlS.so
FileUsage = 1
CommLog = 1
Debug = 1
---end of odbcinst.ini-----
---.odbc.ini---------------
[ODBC Data Sources]
examples_ODBC = Database Examples
[examples_ODBC]
Database = examples
Description = Various tutorial examples
ReadOnly = No
Servername = localhost
Port = 5432
Protocol = 7.4
Driver = PostgreSQL_unixODBC
Username =
Password =
Trace = Yes
Tracefile = /tmp/unixODBC.log
CommLog = 1
Debug = 1
---end of .odbc.ini-----
_________________________________________________________________
Getting married? Find tips, tools and the latest trends at MSN Life Events.
http://lifeevents.msn.com/category.aspx?cid=married
On Fri, 14 May 2004 06:41:04 +0000
"Yury Koles" <fresh_impression@hotmail.com> wrote:
> I've problem getting the psqlODBC driver and ODBC in
> general to work on RedHat 9. I've built and installed
> PostgreSQL ver. 7.4.2, unixODBC driver manager
> ver. 2.2.8, and psqlODBC driver ver. 07.03.0200 without
> errors. In the documentation I've seen that the
> driver's name is libpsqlodbc.so, but I cannot find
> libpsqlodbc.so anywhere on the system. The psqlODBC
> distribution builds 'psqlodbc.so' and 'psqlodbc.la'. I
> tried using 'psqlodbc.so' in 'odbcinst.ini' as a value
> of the 'Driver' field, but I could not connect to my
> database using 'isql', namely, once I type the command
>
> isql -v examples_ODBC userfoo passwd
>
> and press ENTER 'isql' hangs and I have to kill it with
> ^C.
>
> Could somebody point me in the right direction. I
> don't even sure of the right library name. EVEN SHORT
> SUGGESTIONS APPRECIATED :-)
Are you using the full path to psqlodbc.so in your odbcinst.ini -
e.g.,
Driver = /usr/local/lib/psqlodbc.so
or wherever it got installed?
> I've built ODBC driver from unixODBC distribution:
> 'libodbcpsql.so' and connected using that one with
> isql.
In a word, don't. I'm not up on the history of that driver, but
I have the impression that it's fairly obsolete. I've never gotten it to
work properly. Someone else on the list may be able to shed some light
on its history.
By the way, I think you can drop the Setup variable from your
odbcinst.ini. I think that only had meaning for the unixODBC version of
the driver. Here's what the postgres entry in my odbcinst.ini looks
like.
[PostgreSQL]
Description = PostgreSQL 7.4.2 driver (psqlodbc-7.3.2)
Driver = /opt3/lib/psqlodbc.so
FileUsage = 1
CPTimeout =
CPReuse =
CommLog = 1
Debug = 1
Cheers,
Bob
Still a problem... I need suggestions of how do I
troubleshoot. Any tools or techniques I might use?
Thank you for all your previous help!
Some info about my case, in case if needed:
RedHat 9, PostgreSQL 7.4.2, unixODBC 2.2.8 (driver
manager), psqlODBC 07.03.0200 driver. The driver's
directory: /usr/local/lib/psqlodbc.so
>Are you using the full path to psqlodbc.so in your
>odbcinst.ini ?
Yes.
I modified the 'odbcinst.ini' as suggested:
--- odbcinst.ini ---
[PostgreSQL_psqlODBC]
Description = psqlODBC for PostgreSQL
Driver = /usr/local/lib/psqlodbc.so
#Setup = /usr/local/lib/libodbcpsqlS.so
FileUsage = 1
CommLog = 1
Debug = 1
---(END) odbcinst.ini ---
But still, if I enter literally (I set my **PostgreSQL
account** password to 'pword' which is different from
my reqular user account password).
isql -v examples_ODBC yury pword
'isql' hangs and I have to kill it with ^C.
Same thing happens if I uncomment
'Setup = /usr/local/lib/libodbcpsqlS.so'
and the same log files are produced. Similarly, the
'DataManager' or 'DataManagerII (what's a difference?)
GUI applications hang once I enter the password and
also produces the same log files. More advanced
software such as OpenOffice's data sources obviously do
not work (they work with the JDBC driver though and do
that even using md5-encrypted passwords). Here are two
debugging log files:
---mylog_yury6543.log---
[1074834880]CC_connect: entering...
[1074834880]CC_connect(): DSN = 'examples_ODBC', server
= 'localhost', port = '5432', database = 'examples',
username = 'yury', password='xxxxx'
[1074834880]connecting to the server socket...
[1074834880]connection to the server socket succeeded.
[1074834880]sizeof startup packet = 292
[1074834880]sent the authentication block.
[1074834880]sent the authentication block successfully.
[1074834880]gonna do authentication
[1074834880]read 15, global_socket_buffersize=4096
[1074834880]auth got 'R'
[1074834880]areq = 0
[1074834880]auth got 'K'
[1074834880]auth got 'Z'
[1074834880]sending an empty query...
[1074834880]send_query(): conn=134628032, query=' '
---(END) mylog_yury6543.log---
---psqlodbc_yury6543.log-----------------------------
conn = 134628032, PGAPI_Connect(DSN='examples_ODBC',
UID='yury', 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=134628032, query=' '
---(END) psqlodbc_yury6543.log--------------------
I either don't see or don't understand the trouble in
the log files (no explicit ERROR messages). I also
tried leaving my system wide open securitywise as the
rest of my config files suggest. unixODBC driver did
not work with 'md5' or 'crypt' passwords, but did work
with 'trust' or 'password' in 'pg_hba.conf'.
---.odbc.ini---
[ODBC Data Sources]
examples_ODBC = Database Examples
[examples_ODBC]
Database = examples
Description = Various tutorial examples
ReadOnly = No
Servername = localhost
Port = 5432
Protocol = 7.4
Driver = PostgreSQL_psqlODBC
Username =
Password =
Trace = Yes
Tracefile = /tmp/ODBC.log
CommLog = 1
Debug = 1
---(END) .odbc.ini-----
My systemwide 'odbc.ini' is empty.
---postgresql.conf---
tcpip_socket = true
max_connections = 63
password_encryption = false
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
# The rest are defaults.
---(END) postgresql.conf---
---pg_hba.conf---
local all all trust
host all all 127.0.0.1/32 trust
---(END) pg_hba.conf---
Again, I will be thankful for your good advice!
Yury
> By the way, I think you can drop the Setup variable from your
>odbcinst.ini. I think that only had meaning for the unixODBC version of
>the driver. Here's what the postgres entry in my odbcinst.ini looks
>like.
>
>[PostgreSQL]
>Description = PostgreSQL 7.4.2 driver (psqlodbc-7.3.2)
>Driver = /opt3/lib/psqlodbc.so
>FileUsage = 1
>CPTimeout =
>CPReuse =
>CommLog = 1
>Debug = 1
>
>
>Cheers,
>Bob
>
_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee�
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
Bob Woodside <dumbledore@woodsway.com> writes:
> "Yury Koles" <fresh_impression@hotmail.com> wrote:
>> I've built ODBC driver from unixODBC distribution:
>> 'libodbcpsql.so' and connected using that one with
>> isql.
> In a word, don't. I'm not up on the history of that driver, but
> I have the impression that it's fairly obsolete. I've never gotten it to
> work properly. Someone else on the list may be able to shed some light
> on its history.
I don't know anything about its history, but recently I changed the
specfile for Red Hat's unixODBC RPM to have this package description:
: Install unixODBC if you want to access databases through ODBC. This
: package includes low-level drivers for MySQL, PostgreSQL, and local files.
: However, the included drivers are not as up-to-date as the ones distributed
: separately. It is recommended that you install and use the MyODBC package
: if you need a driver for MySQL, and/or the postgresql-odbc package for
: PostgreSQL.
libodbcpsql.so is the obsolete driver included in unixODBC. psqlodbc.so
is the one from the postgresql-odbc package.
(If anyone on the list has a comment about that description, let me
know.)
regards, tom lane