Skip to Content

Abstract

In this blog, I’m going to show you, how to consume data from a MySQL-database running on-premise in a Java Application in SAP Cloud Platform (we will use neo). This will require using the Cloud Connector and one of it’s recent features.

To make the example more realistic and less ugly to look at, we will display the data in a small UI5 App instead of looking at raw json data.


1. Introduction

The SAP Cloud Connector is a great tool for consuming data from legacy systems and other on-premise applications in the cloud. Previously this required the application to use either the HTTP, RFC or LDAP protocol for data exchange. While these protocols (especially HTTP) cover a high percentage of use-cases, there are some applications which require other protocols. One example is connecting to a database, as many databases are using their own protocols, which are normally based on TCP. Since Cloud Connector version 2.10.0.1 TCP connections are also supported by the Connectivity Services.

As a proof-of-concept, I created a Java application which executes SQL queries using the MySQL Connector/J where the Java application is deployed in the cloud and the database runs locally. I thought of creating the same example except using PostgreSQL instead of MySQL, let me know in the comments, whether you’re interested. For the application, I’m using the “world dataset” which is available for download in the Documentation of MySQL. I’m calculating the fraction of people living in the city compared to the whole population of a given country, the result is not very accurate (e.g. the fraction calculated for Singapore is greater than 1) as it’s only a dataset meant for testing, but that’s good enough for a POC.

The blog is split into three parts:

  • Technical Background you can skip this part if you’re already familiar with TCP and SOCKS.
  • How to set up at home How to set up the Cloud Connector and MySQL, import the dataset and deploy the application.
  • Code Review briefly explaining the Java Code.

2. Technical Background

The Connectivity Services provides a SOCKS-Proxy which allows your application to connect to a TCP-based system using its virtual hostname and port.

2.1 TCP

Have you ever heard of “Port 80”, “Port 8080”, “Port 21”, “Port 443”, etc.? That’s TCP.1

TCP or Transmission Control Protocol basically has four main tasks:

  1. Guarantees the order of packages/bytes sent.
  2. Automatic retransmission in case of package loss.
  3. Flow control (controlling the speed of data transmission according to the available bandwidth and the ability of the receiver to process the data).
  4. Delivering the data to the correct “port” (meaning the correct program and socket).

The TCP protocol is settled lower in the protocol stack than the other protocols supported by the Cloud Connector (Layer 4 of the OSI model). Actually the all the other protocols are based on TCP. This implies two things:

  • Starting with the good one: As almost any protocol used in business applications (without a proof, sorry) is based on TCP the Cloud Connector now supports (almost) any protocol, you’ll ever want to use.
  • Now the downside: As the protocol operates on a lower level (possibly even forwarding encrypted data) it is not possible to provide the same fine granular access control as it’s possible e.g. for HTTP (path) and RFC (function name). Which does NOT make it insecure to use, but – as stated – affects granularity. So be aware of that.

2.2 SOCKS

You possibly already came across this term in the proxy settings of your Brower, Operating System, IDE or EMail-Client. SOCKS is a proxy protocol based directly on TCP and supports proxying raw TCP connections (and more), which makes it a perfect fit for the Cloud Connector TCP use case. To get a more detailed overview of the protocol, I can recommend the Wikipedia article about SOCKS. We are using SOCKS5, so concentrate on that parts of the article, if you’re going to read it.

3. How to set up at home

To set up my POC you need three things:

  1. A MySQL Server with the sample data.
  2. A Cloud Connector instance connected to your Cloud Platform Subaccount.
  3. The Webapp deployed to your Cloud Platform Subaccount.

The next subchapters will help you set up everything. Each subchapter contains a quick summary as well as a more detailed video tutorial. If you already an advanced Cloud Platform / Cloud Connector user feel free to skip the videos.

3.1 MySQL

  1. Download and Install MySQL. The version should not matter, but if you experience any problems use 5.7.18 (the version I have installed).
    This document will help you: https://dev.mysql.com/doc/refman/5.7/en/installing.html
  2. Import the world dataset: You can download it here: https://downloads.mysql.com/docs/world.sql.zip
    and installation guidance is available here: https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html

https://downloads.mysql.com/docs/world.sql.zip

3.2 Cloud Connector

  1. Download and install the Cloud Connector as described here: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/57ae3d62f63440f7952e57bfcef948d3.html
  2. Perform the initial configuration: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/071708a655de4486b498cf5b16fb8ea8.html
  3. Import the access_control.zip into the Cloud Connector: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/f42fe4471d6a4a5fb09b7f3bb83c66a4.html
  4. If you changed the port or installed MySQL on another machine than your Cloud Connector, change the internal hostname and port of the access control accordingly.
  5. Finally, click “check connection” to ensure everything is configured correctly.

https://tools.hana.ondemand.com/#cloud

3.3 Deploy App

  1. Open https://account.hanatrial.ondemand.com/ and log in.
  2. Deploy a new Java application. Use the provided war file: map-app.war and “Java Web Tomcat 8” as Runtime.
  3. After the file was successfully uploaded, deployed and started, open the application url.
  4. You should now see a map displaying the aggregated data from your database.

 https://account.hanatrial.ondemand.com/

4. Code Review

The biggest class in the code is the SocketFactory. It might look a bit complex for a POC, but it’s just a copy of the StandardSocketFactory provided by the MySQL Connector/J with a small change. This is necessary because the default implementation does a DNS lookup before connecting to the proxy instead of relying on the DNS resolver of the proxy (a feature introduced with SOCKS5) and fails in our case because virtual hosts are not real DNS names. There is already a confirmed bug report in the bug tracking to of MySQL (MySQL Bug #77924). Hopefully, this workaround can be removed soon.

The Driver is loaded in the DBConnection class. There is nothing special in the class, just a very basic JDBC driver instantiation, except for line 19 where the SocketFactory is configured.

Also in thConnectivityServlet, there is only one thing which is special: the init method (beginning in line 39), where the proxy authentication is configured. This strictly follows the documentation.

5. Related Information

SAP Cloud Platform Documentation

Using TCP for Cloud Applications

Configure Access Control (TCP)

Blogs containing other examples for Cloud Connector TCP

How to connect via SAP Cloud Platform Integration to my On-Premise mail server

How to access an On Premise HANA (through SAP Cloud Connector) via JDBC from SAP Cloud Platform (NEO) Java App

Footnotes

  1. UDP (and possibly others) also uses the term “port”, but the ports mentioned here are well-known TCP ports.
To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    Great stuff Philipp! Is there a solution for using Cloudfoundry to connect to On-Prem HANA? Have been struggled for some time trying to find a solution.

    (0) 
    1. Philipp Stehle
      Post author

      I don’t think this is currently possible using the Connectivity Services as TCP Connectivity is not yet available in CF.

      The only thing I can imagine is an HTTP based on-premise application which accesses the HANA database and makes selected data available via the Cloud Connector to CF.

      (0) 
  2. Former Member

    Thanks for the informative blog Philipp. I am trying to connect to an Oracle DB. This does not seem to work. Are you aware if Oracle driver has a issue similar to MySQL Bug #77924 where in the DNS resolver of the SOCKS proxy is not used? I am not finding any info on this.

    (0) 
    1. Philipp Stehle
      Post author

      Possibly there is some similar Bug in the Oracle JDBC implementation, after all MySQL is developed by Oracle (and Community) so yeah.

      Unfortunately I don’t have access to an Oracle Database, so I can’t try it myself. Could you give me a call and we figure it out together?

      (0) 
  3. Chadi Mohamed

    Great blog Philipp.

    I have another scenario, I want to develop Sap Cloud Application in which the destination backend is Oracle E-Business Suite, Is it applicable to use cloud connector to integrate my application with this on-premise system, and should I use OData when I try to read or push data from/to the system ?

    Best Regards,
    Chadi Mohamed

    (0) 
    1. Philipp Stehle
      Post author

      Thank you.

       

      I’m not familiar with the Oracle E-Business Suite, but if it offers a restful interface (OData or not) the Cloud Connector is an appropriate solution.

      But this blog might not be a good starting point for you (as it uses the TCP instead of the HTTP protocol). Please take a look at the documentation: Exchanging Data via HTTP

       

      Kind Regards,

      Philipp

      (0) 
  4. Artiom Sargesjan

    Hi Philipp,

    Thank you for the blog.

    I followed your blog to connect to a AS400 DB. Everything is working fine except the fact that SCC is denying access to the AS400 system. In the SCC I can see that my java application is connected, but in the Audits it says “Denying access to system … ” With HTTP I know you have to make the resouces available, but with TCP this is not possible. Do you know how to resolve this?

     

    Kind regards,

    Artiom

    (0) 
    1. Philipp Stehle
      Post author

      Hi Artiom,

       

      TCP on Cloud Connector does not support Resources like RFC or HTTP do.

      This is because TCP is on a lower level in the network stack. So it’s sufficient to have an Access Control mapping the virtual host to the internal host and setting the protocol to TCP, without any further configuration.

      To me your problem sounds like a typo, can you double-check that please?

       

      Kind Regards,

      Philipp

      (0) 
      1. Artiom Sargesjan

        Hi Philipp,

         

        Thank you for the fast reply.

         

        I have already checked my code for several times. If you like I can email the code to you.

         

        Kind regards,

        Artiom

        (0) 
      2. Artiom Sargesjan

        Hi Philipp,

        I was able to resolve my issue. It appears that JDBC requires ports: 449, 8470, 8471, and 8476. I had to add all those ports in the Access Control.

         

        (1) 
  5. M M SAI THARUN LAKKOJU

    Hi Philipp,

    It was a good blog. A lot of good information in one place. I tried accessing data of MySQL server from the cloud. It worked like a charm. But there is an issue for me while trying to  Microsoft SQL server on-premise database., even after following all the steps in your blog, It is giving me time out. From my knowledge what I suspecting is I am trying to use Socket configuration for MySQL server to use Microsoft SQL database. so it’s not applying the configuration. Could you please help me with this issue. I am attaching the error log below.

    (0) 
    1. Philipp Stehle
      Post author

      It looks suspicious to me, that there is an globally routed IP Address in the log (19.100.0.123 which is some IP Address registered in USA).

      Are you using the virtual host correctly?

      (0) 
      1. M M SAI THARUN LAKKOJU

        Hi Philipp,

        For us we have configured both the virtual address and local address same(19.100.0.123) so it might be looking like a globally routed address. But actually we are trying to access virtually configured address only.

         

        (0) 
        1. Philipp Stehle
          Post author

          Ok, I see.

          The problem is, the Microsoft SQL driver automatically resolves the hostname instead of relying on the SOCKS Proxy to do this. You’ll need to find a way to stop the driver from doing this.

          If you can’t find something in the documentation about that, take a look at this Issue on GitHub https://github.com/Microsoft/mssql-jdbc/issues/462 and leave a comment if necessary.

          (0) 
          1. M M SAI THARUN LAKKOJU

            Hi Philipp,

            Thanks a lot for the reply. As far as I understand, there is a problem with Microsoft SQL driver but they were asking to modify the sources of the driver for skipping Microsoft SQL driver throwing UnknownHostException, but in my case, I cant do that because I’m doing this for a productive environment. Could you please suggest another solution if possible?

            (0) 
            1. Philipp Stehle
              Post author

              Hi,

              actually it is not a big change, so you should be able to do this even for production, after proper testing of course.

              But the better way would be, getting in contact with Microsoft (e.g. via the GitHub Issue mentioned) to get this fixed. If you start the conversation there, providing some details why you want to get this fixed, I can provide some technical details to them.

              (0) 
              1. M M SAI THARUN LAKKOJU

                Hi Philipp,

                I am trying to do that fix that is mentioned in the GitHub issue. I have done the change in the Driver but still not able to call the JDBC URL and get a response. I am not able to track the issue could you please help me? If i want to share my code with you how would I do that?

                Attaching a couple of snips, how I am trying to access JDBC.

                1. Servlet where I am trying to get Connection.

                2. DbConnect class where I am trying to set Properties

                (0) 

Leave a Reply