How to set up a JDBC Connection using the Cloud Connector

[former_member224346]

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.¹

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/57ae3d62f63440f7952e57bfcef...


2. Perform the initial configuration: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/071708a655de4486b498cf5b16f...


3. Import the access_control.zip into the Cloud Connector: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/f42fe4471d6a4a5fb09b7f3bb83...


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 the ConnectivityServlet, 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...

Footnotes

1. UDP (and possibly others) also uses the term "port", but the ports mentioned here are well-known TCP ports.