Skip to Content

To OpenSQL or Native on JAVA

Over the last year and a half we have been developing our SAP PI/PO testing application, Integration Regression Tool. Which is a tool to test SAP PI/PO applications.

The application is a Spring based application where we are using some of the different components of the Spring framework as MVC, JDBC, Security and etc.

We have med the application to native Java application that can be deployed using  SCA on your SAP PI Java system. It has been quite easy for customers to just install it as an add-on component. You just installed the SCA and did about a minutes worth of configuration and you were up and run. We could care less about which database the customers were using underneath which is still good. User maintenance works just by giving users SAP Roles for our application.

The downside of using the OpenSQL data source this is that we need to create a lot of data mapping and custom SQL queries to make it work because of OpenSQL limitations, we also can’t automate database version control both on development (non SAP) and production (SAP PRO with OpenSQL) environment using the Liquibase tool. So we could not use Spring Data JPA with Eclipselink to implement and maintain data layer easier because it could generate an SQL which is not supported by OpenSQL.

Now we are going to support SAP Cloud Platform(SCP) and provide the customer’s cloud testing capabilities. We should have the possibility to deploy IRT in SCP and Netweaver PRO environment both (2 different builds from the same source code repository). From Netweaver and Cloud platform it should be possible to test PRO and Cloud scenarios.  It would make sense to start to create the application using technologies which will help us during development, testing and future maintaining. On SCP we can use flexible stack fully. But we still have previously listed problems on Netweaver PRO environment.

It seems like SAP Netweaver supports both the OpenSQL and the native connection to databases. So we are considering if we can just use the native connection to the backend systems. It would make the code much easier and add the possibility to maintain database structure using migrations.

It will add some complexity to our end-to-end testing because we need to run multiple databases and verify that the system works as expected. But the one code line is a nice benefit that will make it much easier to maintain code.

It is probably good in theory, but how does this stack up in real life. Have anybody tried using this feature and how did it work?

You must be Logged on to comment or reply to a post.
  • Hi Daniel,

    I do not understand why you have to use Open SQL in production, but I do not know SAP PRO either.

    SAP Netweaver Java supports three kind of data source: Open SQL, native, and vendor. "Vendor" is indeed the most native, the only that bypasses Open SQL completely. We develop a large application on SAP Netweaver Java with JPA and Eclipselink. JPQL is the database agnostic standard for queries so there is no need for Open SQL and its limitations. You have to define the data source as "vendor".

    But there is probably a reason why you cannot do this and cannot define your production data source as "vendor"?



    • Hi Ralf


      When we started it seems like the best way to support multiply databases without having to code it our self. But if you have success with datasource vendor we would try it.


      • Hi Daniel,

        not yet, but in the near future we will add HANA support and we did already basic tests for HANA but not yet mass tests.

        EclipseLink supports the big database vendors including SAP HANA, EclipseLink is the JPA provider of SAP Cloud Platform. (Probably not SAP Max DB.)

        For DDL we use flyway, not Liquibase. Therefore we will have to write our DDL scripts twice.

        If you have issues setting up the data source just ask. Documentation for HANA JDBC driver is hard to find (not to say impossible) but we figured it out.