Connection between PostgreSQL and SAP HANA database - Mailing list pgsql-general

Yes I run it under Windows, I also have a Linux server for testing.
Under Windows I know that the driver works because i use it already in another project (MS SQL Server with
LinkedServer).
In Linux I can test the HDBODBC driver via command line and the ISQL command, this works fine.

I'm desperate about the PostgreSQL configuration. I have no idea how to configure it. I doubt the path (HDBODBC
(SAP-driver)–> SAP HANA database) myself, but i cant connect to the HDBODBC driver directly via an extension.
 
My previous assumption was that i need a extension for the foreign data wrapper, the foreign server, foreign table and
soon...
 

How can I use the system dsn in PostgreSQL?


-----Ursprüngliche Nachricht-----

Mit freundlichen Grüßen

i. A. Andreas Thuermann
Teamleitung IT-Applikationen & BI
IT-Applikationen & BI

Schoenmackers Umweltdienste GmbH & Co. KG
Hooghe Weg 1
47906 Kempen

Tel:    +49 (2152) 207 6765
Fax:
E-Mail: andreas.thuermann@schoenmackers.de

Schönmackers Umweltdienste GmbH & Co KG, Hooghe Weg 1, 47906 Kempen, Sitz: Kempen, Amtsgericht Krefeld, HRA 3763,
p.h.G.: Schönmackers Umweltdienste Verwaltung GmbH, Sitz: Kempen, Amtsgericht Krefeld, HRB 6153,
Geschäftsführer: Sabrina Michelbrink, Oliver Zimmermann
https://www.schoenmackers.de

Hier geht es direkt zum Shop: https://www.schoenmackers.de/containershop



Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Donnerstag, 10. Oktober 2024 17:46
An: Thürmann, Andreas <Andreas.Thuermann@schoenmackers.de>; 'pgsql-general@lists.postgresql.org'
<pgsql-general@lists.postgresql.org>
Betreff: Re: Connection between PostgreSQL and SAP HANA database

On 10/10/24 02:54, Thürmann, Andreas wrote:
> Please excuse the late reply. I didn't have time to continue working
> on this project.
>
> I still don't get a response to the query. The query continues
> indefinitely and the data output window shows only „Waiting for the
> query to complete...“
>
> In the SAP HANA logs i can’t find any access of my PostgreSQL-query,
> the Microsoft solution works perfectly and is visible in the log.

Are you running this on Windows?

>
> Is it not possible to connect to the SAP HANA database with PostgreSQL
> or are there errors in my setup?
>
> My current expectation of the data stream: PostgreSQL -> odbc_fdw ->
> HDBODBC (SAP-driver) –> SAP HANA database

Does this:

HDBODBC (SAP-driver) –> SAP HANA database

work?

Meaning, does test connection complete successfully.

What are the settings for odbc_fdw and the SAP driver?

>
> But how can I set up the connection between the two ODBC drivers?
>
> Has anyone already implemented a project like this and can tell me how
> to establish the connection to SAP?
>
>  > Adrian Klaver (adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
>
>  > <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>) asked:
>
>  >
>
>  > Postgres version?
>
>  >
>
>  > PostgreSQL 16 with pgAdmin 16 4.2.2
>
> Have you tried tried this using psql?
>
>  > Have you looked at Postgres log for errors?
>
>  >
>
>  >                  No errors during the query in the log, only a
>
>  > „unrecognized winsock error 10054“ at the start of the pg client
>
> Have you looked at the SAP HANA logs to see if a connection is even
> attempted?
>
> Ron Johnson (ronljohnsonjr@gmail.com <mailto:ronljohnsonjr@gmail.com>)
> asked me the following. Answers below:
>
> 1. What error did you get?
>
> No error, the query runns into infinity (>30 min.) without a result.
>
> 2. Which command caused the error?
>
> Test-query like SELECT * FROM hana_table LIMIT 10;
>
> 3. What version of the odbc_fdw are you using?
>
> Latest, Version 0.5.2
>
> 4. What version of Postgresql are you using?
>
> PostgreSQL 16 with pgAdmin 16 4.2.2
>
> Adrian Klaver (adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>) asked:
>
> Postgres version?
>
> PostgreSQL 16 with pgAdmin 16 4.2.2
>
> SAP HANA version?
>
> SAP HANA database 2.00.07400
>
> odbc_fdw version and where did you get it from?
>
>                  I use the odbc_fdw from
> https://www.postgresonline.com/journal/index.php?/archives/409-Postgre
> SQL-16-64-bit-for-Windows-FDWs.html
> <https://www.postgresonline.com/journal/index.php?/archives/409-Postgr
> eSQL-16-64-bit-for-Windows-FDWs.html>
>
> How do you know you are not making a connection?
>
>                  No results from the query, no suggestions in the
> foreign table creation menu
>
> Have you looked at Postgres log for errors?
>
>                  No errors during the query in the log, only a
> „unrecognized winsock error 10054“ at the start of the pg client
>
> Hello everyone,
>
> I'm currently trying to establish a connection between PostgreSQL and
> a SAP HANA database. Unfortunately, so far without success.
>
> SAP's own ODBC driver works correctly and I can query the HANA
> database using an ISQL command. However, I can't establish a
> connection from PostgreSQL.
>
> I used the following commands to establish the connection:
>
> CREATE SERVER hana_server
>
> FOREIGN DATA WRAPPER odbc_fdw
>
> OPTIONS (dsn 'SAP_ODBC_DNS');
>
> CREATE FOREIGN TABLE hana_table (
>
>    matnr VARCHAR(40)
>
> )
>
> SERVER hana_server
>
> OPTIONS (table 'SAPABAP1.MARA');
>
> CREATE USER MAPPING FOR postgres
>
> SERVER hana_server
>
> OPTIONS (odbc_user 'secret_user', odbc_password 'secret_password');
>
> Can anyone tell me where the error is?
>
> Mit freundlichen Grüßen
>
> i. A. Andreas Thürmann