Hana Cloud Database Integration with SAP Cloud Integration via JDBC part -1

[amysh95]

As SAP has provided over 100+ services to work on the business scenarios, I have just came up with and interesting requirements where we need to provide the API’s from integration suite which will be connected with the backend as Hana Cloud database to store some data. And based on some requirements we need to do some little bit computation in integration suite.

In this Blog, I will be doing the step-by-step procedure to connect with the Hana cloud database and the formats/available method to push and pull the data from Hana cloud database.

Prerequisites for this Integration

• Integration suite must be provisioned.
• Hana Cloud database must be provisioned

SAP Cockpit Instance and subscription page

Requirement Architecture

Architecture for this Integration

Steps required to connect with Hana cloud database:

1. Get the SQL endpoint from Hana Cloud database.
2. SQL endpoint needs to be configured in JDBC material for Connection.
3. JDBC connection via JDBC Adapter.

Get the SQL endpoint from Hana Cloud Database

Open SAP Hana Cloud, you will be redirected to the All instance tab where database link will be visible.

Hana Cloud Database Instance Page

On the right hand side multiple tabs will be visible, from where copy the  SQL endpoint from Connection tab.

SAP Hana Cloud Database Overview

Example for SQL endpoint: <instanceID>. hana.prod-in30.hanacloud.ondemand.com:443

Connection URL: jdbc:sap://<server>:<port>[/?<options>]

SQL endpoint needs to be configured in JDBC material for Connection

Go to your integration suite tenant and click on JDBC material under Manage security section.

SAP Cloud Integration Monitoring Page

Here under JDBC data source, you need to add one JDBC data source i.e. nothing just a connection string in general term, and fill all the required data.

Name	JDBC Data source name
Description	General description
Database Type	Select “SAP Hana Cloud” from drop down
User/Password	Database username and Password
JDBC URL	Paste the SQL endpoint which is copied from the Hana Cloud database

The JDBC URL required a little modification, which will be like

“jdbc:sap:// <instanceID>. hana.prod-in30.hanacloud.ondemand.com:443/?encrypt=true”

Here the thing to be noted is when we generally create and database connection string we put database name as well, but in case of SAP Hana cloud database, we are getting the SQL endpoint from the database itself which are located under instance. That is reason database name is not required.

Also, in case of 3rd party database such as Microsoft SQL Server, Oracle and DB2 you need to upload the JDBC driver jar file under JDBC driver tab. In case of HANA it is not required.

JDBC Driver upload

JDBC connection via JDBC Adapter

While designing the IFlow using JDBC adapter, you can create the connection with database using jdbc data source. Below are the configuration for the adapter.

JDBC Adapter Configuration

Here the configuration is normal apart from Batch mode. Batch mode provides functionality to modify multiple records in a single payload. Where the subsequent options Atomic and Non-Atomic.

• Atomic: enabling this option will execute each batch and revert successful only in case when all the batches are successfully executed. In case of failure in any of the batch it reverts the transaction back to initial stage.
• Non-Atomic: It depends on the behaviour of the database, and it does not revert the transaction back to its initial stage.

In the next series of this blog post, I will be providing the examples on how to push and pull the data from the database by using standard xml method and with SQL query.

Hana Cloud Database Integration with SAP Cloud Integration via JDBC part – 2.

It would be great if you provide any feedback or improvement points for upcoming content.

Thanks