How to retrieve data from SAP HANA Cloud Database in iFlow Development

I have no doubts that you've heard about SAP HANA. Moreover, I have no doubts that you've heard about SAP HANA Cloud. As for the last one, it is worth mentioning that this service could be activated on the SAP BTP instance, even if you're experimenting with the BTP Trial Account.

See Start Using SAP HANA Cloud Trial in SAP BTP Cockpit

In this post, I'd like to highlight a few key aspects of the JDBC adapter that will allow you to connect to the SAP HANA Cloud instance and retrieve data from a specific table. Hopefully, you'll find this explanation useful for your future development projects.

SAP HANA Cloud instance

If your subscription to SAP HANA Cloud is active, you can access the dashboard from the BTP subaccount.

When you open it, you should be able to see HANA instances and their statuses. Please ensure that the instance you intend to work with is running. If not, start it; this might take a while.

Once it's started, you can launch the SAP HANA Database Explorer or any other client you prefer.

For example, you can use the DBeaver client to connect to the SAP Hana Cloud instance.

DBeaver client

For demonstration purposes, I'll be using the table shown in the screenshot below.

SAP BTP. Define the JDBC connection

Once you've established the connection with the SAP HANA Cloud instance, you need to configure the corresponding settings on the BTP side. Open SAP Integration Suite, navigate to Monitor -> Integrations and APIs, and select the JDBC Material tile.

The SAP BTP Integration Suite requires connection details for the SAP HANA Cloud instance, which can be provided by adding the JDBC data source.

See Managing JDBC Data Sources

To add the source, click the 'Add' button.

Provide the required details

💡

If you're looking for the URL string that can be used for JDBC URL, please open the HANA Cloud dashboard where your database instance is running and select the 'Copy SQL Endpoint' link

SQL Endpoint that could be used as a JDBC URL on BTP

Deploy the changes.

SAP BTP. Create the iFlow

In this step, I'll demonstrate how the JDBC connection we created earlier can be used in the iFlow. Create a simple iFlow that can be triggered via HTTPS and add the 'Request reply' step to it.

See Define Request Reply step

Place the Receiver system in the iFlow and connect the 'Request Reply' step to the receiver system. Select 'JDBC' as the adapter type.

On the 'Connection' tab, provide the JDBC Data Source Alias created earlier.

Deploy the changes.

Perform the testing

Copy the iFlow's endpoint and trigger it using, for example, the Postman client. Before hitting the 'Send' button, switch to the 'Body' tab and provide the SQL query that will select the data from the specific table. For example,

Then hit the 'Send' button.

The iFlow should return the values from the table stored on the HANA Cloud instance.

Additional options to run the SQL query

I triggered the iFlow's API by providing the SQL query in the body of the request. If I trigger the same iFlow without a body populated, it fails with an error.

If you don't want or are not allowed for some reason to provide the SQL query in the body of the request, you can utilize the Change Container step or the Groovy Script.