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 upgraded 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.
In the old setup, the JDBC adapter connects directly 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 pretty easy to make changes to the host and traffic anyway. It is possible to add an SSL certificate with standard Spring.
We decided to save the username and password 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 the code, installation and build guide on GitHub:
https://github.com/figaf/SAPPItoJDBC
We recommend using OpenJDK to run the application.
Limitations
The proxy does not support the full set of database operations that SAP PI normally supports. It only supports the methods the client uses:
- SQL Select
- SQL Query
The application has not been designed to handle a high volume of data. We had only a few 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 spent 30 development hours on creating and deploying the proxy. Developing the application took a bit longer than expected because it was difficult to test how the process worked and what kinds of errors we were getting.
