Hello Colleagues!

In this post I want to share the way how to call HANA Procedures via XS JS. This is not a how-to guidepost, I want to share the end functionality that we use in our work. This approach might be very convenient in integration projects when the data extraction rules can be changed during the UAT, so you won’t need to change anything in your integration solutions you just need to apply this requirements in your procedures and change will be committed on the fly. This post is partially based on this question: https://answers.sap.com/questions/272549/service-layer-call-procedure-or-view-from-database.html

We don’t pretend that this post will be considered as a sacred cow. We don’t take any responsibilities in case if you decided to implement it to the productive environment without taking a backup and something went wrong. If you have anything to add or you see any pitfalls, kindly share your opinion to discuss.

This functionality is used by us in integration projects with SAP Business One for HANA. Since there is no way how to call procedures via Service Layer we needed to get a web based wrapper for the procedures to call them without any drivers installed. This functionality is not limited by SAP Business One solution only you can call any procedures on Hana server via HTTP GET request.

In attachment to this post you can find the following files:

ProcedurePerformer.zip – An archive with the XS JS project to import ProcedurePerformerTest.zip – An archive with the .NET console application project, that describes how to consume this service via C#

ProcedurePerformer package contains 2 XS JS files:

ExecuteWithOutParam.xsjs – Executes the procedure with a table type output parameter (the procedure body has the following script template: yourOutParam = ) and returns the result table serialized to JSON.

NOTE: This is the recommended approach because your consumer will always receive the                same data structure for deserialization.

ExecuteWithSelect.xsjs – Executes the procedure with the SELECT statement inside and returns the result table serialized to JSON

NOTE: Another point why it is not recommended is that it uses the outdated DB interface $.db. Unfortunately, there is no way how to implement the same functionality using new $.hdb interface, so I included it here as an example and in case if this approach is needed.

NOTE: This import approach might be outdated but that works fine when I couldn’t do that via WebIDE. If you have any suggestions how to export-import packages, please share your knowledge.

THIS IS NOT AN ADVICE HOW TO PERFORM EXPORT-IMPORT OPERATION AND THIS IS NOT A POINT OF THIS TOPIC, THIS IS JUST ONE OF THE WAYS HOW TO START WORKING WITH THE ATTACHED FUNCTIONALITY.

1) First of all we need to have HANA application framework up and running. If you run SAP Business One you might check it in your SLD control center. Go to "Services" tab and find there the "App Framework" row.

The address is https://:43 (I’m, not sure but for HTTP the port would be 80. Please correct me if I’m wrong). If you go to this address you must see the following screen.

If you don’t run SAP Business One you can check the mentioned addresses, the result must be the same. In case if you don’t get the screen above you need to start the necessary HANA services before to begin the next step.

2) As the next step you need to import the package that implements this functionality (ProcedurePerformer.zip from attachments). To import this package go to HANA Studio and choose "SAP HANA Development" perspective.

Go to "Repositories" tab and create a new working space (skip this if you want to use the default or if you already have one)

I have created a workspace "DONE" for our partner namespace (DatatechOne).

We can import the package now. Extract files from the ProcedurePerformer.zip. Go to Project explorer tab and press "Import…" from the context menu.

Choose General -> Projects from Folder or Archive and press "Next>".

Press "Directory…" button and choose the directory with the extracted files. Tick the checkbox against the ProcedurePerformer project and press "Finish".

Add the new project to your workspace and activate it. In context menu choose Team -> Share Project…

Choose your workspace and press "Finish" button.

You can activate your project now. In the same context menu point choose "Activate".

The installation part is done, we can test our http service now.

To call this methods you need to use the following URL: https://:43//.xsjs

In our environment the URL looks https://192.168.200.50:4300/ProcedurePerformer/ExecuteWithOutParam.xsjs for a procedure with an output parameter and https://192.168.200.50:4300/ProcedurePerformer/ExecuteWithSelect.xsjs for a procedure with a SELECT statement inside. You are able to try out one of this URL right in an address bar of your browser. This methods have basic authentication. At the first time you will be redirected to the Login page. You must provide user credentials. Kindly note that you need to provide credentials with authorities to execute procedures and access to the provided schema otherwise you’ll get an error that the user has no permissions to perform the operation.

If you try to open the mentioned URL without any parameters you will receive the following JSON string:

{"ErrorMessage":"Parameters \"dbName\" and \"procName\" must be presented!"}

To consume this functionality you need to provide 2 mandatory parameters:

dbName – Schema name where the procedure must be called procName – Procedure name to be called

To call the procedure without any input parameters you should use the following URL template: https://:43//.xsjs?dbName=&procName=