This is probably not one of the best solutions we have ever created. It did solve a problem and is working.
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.
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
It is using the standard JDBC XML formats that SAP PI uses.
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.
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.
The solution is built on Spring.
You can find code, installation, and build guide on Github
We recommend using OpenJDK to run the application.
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.
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.