Обсуждение: Re: 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

> My .odbc.ini file (accessed via ODBCINI env variable) looks like:
>
> [ODBC Data Sources]
> testdb          = PostgreSQL test database
>
> [testdb]
> Driver          = /usr/lib/libpsqlodbc.so.0
> Description     = PostgreSQL test database
> Host            = localhost
> ServerType      = postgres
> Port            = 5432
> FetchBufferSize = 99
> UserName        =
> Password        =
> Database        = testdb
> ServerOptions   =
> ConnectOptions  =
> Options         =
> ReadOnly        = no
> Trace           = 1
> TraceFile       = /tmp/odbc.trace
> Debug           = 1
> DebugFile       = /tmp/odbc.debug
> CommLog         = 1

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