Feedback-ID: i76984098:Fastmail
Message-ID: <8023c908-bf4a-4e04-a0a3-2ce5cfebe9b2@aklaver.com>
Date: Thu, 10 Oct 2024 08:45:34 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Connection between PostgreSQL and SAP HANA database
To: =?UTF-8?Q?Th=C3=BCrmann=2C_Andreas?=
 <Andreas.Thuermann@schoenmackers.de>,
 "'pgsql-general@lists.postgresql.org'" <pgsql-general@lists.postgresql.org>
References: <DEEF3EF111E75A4AA8B1205635F235DC0276A2418F@sud-exc22>
 <DEEF3EF111E75A4AA8B1205635F235DC0276A24515@sud-exc22>
 <DEEF3EF111E75A4AA8B1205635F235DC0276A29F32@sud-exc22>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <DEEF3EF111E75A4AA8B1205635F235DC0276A29F32@sud-exc22>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8023c908-bf4a-4e04-a0a3-2ce5cfebe9b2%40aklaver.com>
Precedence: bulk

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-PostgreSQL-16-64-bit-for-Windows-FDWs.html <https://www.postgresonline.com/journal/index.php?/archives/409-PostgreSQL-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
> 
> 
> Mit freundlichen Grüßen
> 
> i. A. Andreas Thürmann
> Teamleitung IT-Applikationen & BI
> IT-Applikationen & BI
> 
> Schönmackers Umweltdienste GmbH & Co. KG
> Hooghe Weg 1
> 47906 Kempen 	Tel:
> Fax:
> E-Mail:
> 	+49 (2152) 207 6765
> 
> andreas.thuermann@schoenmackers.de 
> <mailto:andreas.thuermann@schoenmackers.de>
> 
> ------------------------------------------------------------------------
> **
> Unsere aktuellen Datenschutzhinweise gemäß Artikel 13 DSGVO finden Sie 
> hier: Kunden 
> <https://www.schoenmackers.de/wp-content/uploads/2018/05/Datenschutzerklaerung-DSGVO_Kundeninfo.pdf> / Bewerber <https://jobs.schoenmackers.de/content/Datenschutzerklaerung/?locale=de_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
> www.schoenmackers.de <http://www.schoenmackers.de/>
> 
> <https://www.schoenmackers.de/containershop/>
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com