topic Re: Problems with postgresODBC drivers in Administration and Deployment

Problems with postgresODBC drivers

John_Wick — Tue, 08 Nov 2022 17:18:52 GMT

Hello there!

I came across a problem connecting SAS to PostgreSQL via SAS ACCESS to ODBC.

I had some drivers in <sashome>/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver, but psqlodbcw.so and psqlodbca.so returned data in UTF-8, but I need ANSI. SAS accept them. After I installed the new postgresODBC drivers https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8.5-x86_64/postgresql13-odbc-13.02.0000-1PGDG.rhel8.x86_64.rpm

But SAS refuses to accept them:

"""ERROR: CLI error trying to establish connection: [unixODBC] could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket '/tmp/.s.PGSQL.5432'? """

The request via console isql/iusql passes normally, odbc.ini is the same. isql/iusql returns both in UTF-8 or ANSI, but SAS refuses to work.

Parameters:

System: x86_64 RHEL 8.6

Session encoding SAS - KZ1048,

Encoding DB - UTF-8,

Postgres DB - v13.6 on a remote server x86_64 RHEL 8.5.0-4

unixODBC - v2.3.4

Which version of postgresODBC is better to use, or how to solve the problem of getting data in ANSI?

Changing the encoding of the SAS session, changing the encoding of the database, using DataDirect is not suitable yet((

Re: Problems with postgresODBC drivers

SASKiwi — Tue, 08 Nov 2022 19:18:25 GMT

Have you made the required configuration changes for the new ODBC driver:

"You might have to use a text editor to modify the odbc.ini file in your home directory to configure
data sources. Some ODBC driver vendors may allow a system administrator to maintain a centralized
copy by setting an environment variable.

The ODBC drivers are ODBC API-compliant dynamic link libraries, referred to in UNIX as shared
objects. You must include the full path to the dynamic link libraries in the OS load library
environment variable, i.e., LD_LIBRARY_PATH, LIBPATH, or SHLIB_PATH, so that the ODBC drivers
can be loaded dynamically at run time."

Re: Problems with postgresODBC drivers

John_Wick — Wed, 09 Nov 2022 13:41:22 GMT

Hi, SASKiwi
All paths are set in ld_library_path, which outputs ldd <path to psqlodbca.so >. If they were not specified, the error "File not found" would appear. After installing the new driver postgresODBC, the old driver also broke... with same error...(

Re: Problems with postgresODBC drivers

SASKiwi — Wed, 09 Nov 2022 19:48:31 GMT

I suggest you open a Tech Support track for your problem if you haven't done so already as they are in the best position to help.

Re: Problems with postgresODBC drivers

Sajid01 — Thu, 10 Nov 2022 17:01:43 GMT

1.The drivers in <sashome>/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/1. Driver are for use of SAS Infrastructure Data Server which is incidenally is PostgresQL
The error
""""ERROR: CLI error trying to establish connection: [unixODBC] could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket '/tmp/.s.PGSQL.5432'? """
clearly indicates that SAS is unable to connect to the remote PostgreSQL database.
As you are able to connect using ISQL, the error is most likely due ot some error in configuration. Check if you have a postgresql-odbc client installed on the SAS Application Server and it is configured appropriately.

Re: Problems with postgresODBC drivers

Acamacho85 — Thu, 10 Nov 2022 17:43:44 GMT

Make sure your odbc.ini parameters are correct, for example your error shows that you are trying to connect locally instead of remote. Try to use
servername=remote.server.example
or
HostName=remote.server.example

instead of
server=remote.server.example

Re: Problems with postgresODBC drivers

John_Wick — Fri, 11 Nov 2022 09:50:20 GMT

It turned out to solve the connection problem by using the "noprompt" parameter in assign library.

These properties can be added to Other Options in SAS MC in the library properties.

Example:

LIBNAME <NAME> ODBC noprompt="uid=<login>;pwd=<password>;dsn=<name in odbc.ini>;server=<db host name>;database=<name of db>;" <other default assign options>;