Problems with postgresODBC drivers

[John_Wick]

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...

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((

[SASKiwi]

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."

[John_Wick]

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...(

[SASKiwi]

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.

[Sajid01]

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.

[Acamacho85]

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

[John_Wick · Accepted solution]

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>;