SAP PI JDBC Proxy Open Source project

[DG]

This is probably not one of the best solutions we have ever created. It did solve a problem and is working. 

Purpose

One of our clients is on SAP PI 7.1 and their partner upgrade their MSSQL database to 2019. That meant the current drivers were no longer supported. 

The easy approach would be to upgrade to SAP PI 7.5 but that would be a huge undertaking and not something that was feasible at the moment. 

We, therefore, needed a solution that could make it easy to continue with the integration without having to redevelop all the integrations. 

Hopefully, the upgrade will then happen later. 

 

Scope

We have tested with SAP PI 7.1 and MSSQL 2019. But there should probably not be any issues in using the proxy with other databases.

We have selected only to support the cases the client used

• SELECT


• QUERY

It is using the standard JDBC XML formats that SAP PI uses.

 

Setup

The idea is to minimize the number of changes needed to support the case. 

 

The old setup looks like this. The JDBC adapter connects to the SQL database.

We have created a small Java application that serves HTTP requests. That means we just need to replace the JDBC adapter with an HTTP adapter. This adapter then calls the JDBC Proxy with the JDBC XML. The proxy then interprets the XML and queries the database. The result is returned in a similar structure.  

Security

We have decided to deploy the app on the SAP PI system. We, therefore, did not use SSL certificates for the connection between PI and the Proxy. If users have access to localhost it is anyway pretty easy to make changes to the host and traffic anyway. It is possible to add an SSL certificate with standard Spring.

 

For the Username and Password, we decided to save it in the SM59 connection. It is much easier to configure everything there and keep it safe.

 

Code

The solution is built on Spring.

 

You can find code, installation, and build guide on Github

https://github.com/figaf/SAPPItoJDBC

 

We recommend using OpenJDK to run the application. 

 

Limitations

Der er ikke udviklet et fuld database set svarende til hvad SAP PI normalt supportere. Der er kun de metoder der bliver benyttet på SSI. 

• SQL Select


• SQL Query

 

The application has not been designed to handle a high volume of data. We had a few mostly SQL calls. If you end up with many concurrent calls there could be ways to improve the processing. 

 

Lessons

It was possible to simply run the tool and deploy it pretty easily. We did have to make a few modifications to the SQL queries mostly because the data structures in the SQL database had been changed. 

 

We spend 30 development hours on creating the proxy and deploy it. The process of developing the application to a bit longer than expected because it was difficult to test how the process worked and what kinds of errors we were getting.