How to configure iodbc access to local postgres db?

Rob Yampolsky wrote:

I'm trying to get the iodbc driver manager to access postgres on the
local machine with the odbctest tool that comes with the iodbc stuff.
Having no luck. Fails on the connect attempt, and I know I'm doing
something stupid, but don't know where to look.

Help (or a good .odbc.ini) would be appreciated.
Rob
ryampols@cjds.com

System is Mandrake 7.2 - postgres installed from RPM's on the MDK CD
(libraries in /usr/lib, postgres home is /var/lib/pgsql).

Logs this error in odbc.trace:
SQLDriverConnect ( ... )
SQL_NO_DATA_FOUND

Please try the following.

1) Use ~/.odbc.ini instead of ODBCINI.
2) psqlodbc driver doesn't see the 'Host' entry.
Replace the 'Host' entry by 'Servername' entry.
3) Set a valid user name to the 'Username' Entry.
4) Remove blanks around '='. For example,
Driver=/usr/lib/libsqlodbc.so.0

Good luck.
Hiroshi Inoue

Tried your suggestions, and no luck.

I also went through the exercise of tracing through the odbctest program in
gdb to find out what the

SQLDriverConnect ( ... )
SQL_NO_DATA_FOUND

error in my odbc trace meant. The program did successfuly load my
/usr/lib/libpsqlodbc.so.0 library, and was able to locate and call
functions in it. Specifically, it called SQLAllocateConnect() and got back
SQL_SUCCESS. It also locates and calls SQLDriverConnect(), but gets back
(Duh...) SQL_NO_DATA_FOUND (100). I guess the trace wasn't lying.

So....

What does it mean for SQLDriverConnect() to return 'no data found'. I'm
guessing it means that the ODBC driver was unable to connect to my
postmaster. I guess that could happen for several reasons.

Some guesses:
. The postmaster isn't listening on the 5432 port I have set up in
.odbc.ini. If this were the case, would I be able to run the 'psql'
command-line tool? Does the postgres ODBC driver talk to the postmaster in
a different way than 'psql' does?

. I'm not set up properly to handle TCP connections. I added
"host all <my class C net> 255.255.255.0 trust"
to the default pg_hba.conf file, and there was already a similar entry for
127.0.0.1. Is it possible that local ODBC connections look like they're
coming from somewhere else?

. My postmaster isn't built to handle ODBC. It's a straight binary install
of the MDK 7.2 RPM's, including the postgres ODBC and JDBC interfaces. Is
it possible that MDK builds these things so that they don't work together?

. There's some permission problem. Could that be possible if I'm able (as
the same user) to access this database using 'psql'? Would 'no data found'
be an appropriate error code in this case (I'd think there'd be a more
specific 'access denied'-type error)?

Does anybody out there know (or know where to find) detailed info on how
the postgres odbc driver works? The documentation in the administrators'
guide is strictly for Applix as the client, and mentions that 'you should
be able to get iodbc (or any other driver manager) to work pretty easily'.
Well, I'm sure it's easy once you've got it set up right...

Thanks,
Rob
rob.yampolsky@encodasystems.com

Hiroshi Inoue wrote:

Please try the following.

1) Use ~/.odbc.ini instead of ODBCINI.
2) psqlodbc driver doesn't see the 'Host' entry.
Replace the 'Host' entry by 'Servername' entry.
3) Set a valid user name to the 'Username' Entry.
4) Remove blanks around '='. For example,
Driver=/usr/lib/libsqlodbc.so.0

Good luck.
Hiroshi Inoue

Not to sound too dense, but having loaded all the Postgres RPM's from my
MDK 7.2 CD, and seeing that there is a /usr/bin/libpgsqlodbc.so out
there, can I even assume I have all the pieces I need to get ODBC access
working? I downloaded, built and installed the iodbc driver manager from
source. There was a libodbc.a already on my system, but I wasn't able to
get it to work, and I had used iodbc before on an AIX system.

How about the Postgres setup itself. It looks like the default init.d
script got postmaster started with the -i option (to enable network
connections), but is that (or anything else) even needed to get the ODBC
driver to work.

I'll try your suggestions tomorrow at work and report back.

Thanks for responding,
Rob

Rob Yampolsky wrote:

Tried your suggestions, and no luck.

Seems PostgreSQL ODBC driver seems case-sensitive though
I don't know why.
Please make sure the spelling of the following keywords.

Servername=
Port=
Database=
Username=
Password=
ReadOnly=

regards,
Hiroshi Inoue

Told you it was something stupid. Turns out I was using the wrong
Postgres ODBC driver. My MDK 7.2 installation had 2 things that looked
like the driver.

/usr/lib/libpsqlodbc.so
and
/usr/lib/libodbcpsql.so

The correct one seems to be libodbcpsql

I had tried both, and originally using libodbcpsql caused a segmentation
violation, so I had been concentrating my efforts on the other one.

Well, I ended up following the setup instructions in the unixODBC package
documentation to get it working under that package. Once it worked
there, I was able to get it working with libiodbc (which my application
was using).

Hint- it wants you to specify a UserName in .odbc.ini (you'd think it
would default to the 'current user').

Thanks for listening,
Rob

Rob Yampolsky wrote:

Told you it was something stupid. Turns out I was using the wrong
Postgres ODBC driver. My MDK 7.2 installation had 2 things that looked
like the driver.

/usr/lib/libpsqlodbc.so

This is the driver for iODBC.

and
/usr/lib/libodbcpsql.so

This is the driver for unixODBC.

The correct one seems to be libodbcpsql

You have to choose the driver according to the driver manager.

I had tried both, and originally using libodbcpsql caused a segmentation
violation, so I had been concentrating my efforts on the other one.

Well, I ended up following the setup instructions in the unixODBC package
documentation to get it working under that package. Once it worked
there, I was able to get it working with libiodbc (which my application
was using).

Hint- it wants you to specify a UserName in .odbc.ini (you'd think it

Isn't it Username not UserName ?

would default to the 'current user').

regards,
Hiroshi Inoue