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

DISCLAIMER

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.

WHO MIGHT BE INTERESTED

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.

ATTACHMENTS OVERVIEW

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#

PROJECT OVERVIEW

    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 = <SELECT statement>) 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.

HANA XS JS INSTALLATION

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://<address>:43<instance number> (I’m, not sure but for HTTP the port would be 80<instance number>. 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.

ProcedurePerformer CONSUMPTION

To call this methods you need to use the following URL: https://<Address>:43<InstanceNumber>/<PackageName>/<XsJsFileName>.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://<Address>:43<InstanceNumber>/<PackageName>/<XsJsFileName>.xsjs?dbName=<SchemaName>&procName=<ProcedureName>

The mandatory parameters order doesn’t matter, but they are to be called exactly the same as it specified in the XSJS files (by default it’s dbName and procName). To pass parameters to procedure you need to attach additional parameters to the query string (ex: if your procedure takes 1 parameter you need to add &par=<value> at the end of your URL). For procedure parameters the order is extremely important as it will be passed to procedure with the same order. In attached project ProcedurePerformerTest.zip you can find an example how to consume it via C#.

USAGE EXAMPLE

As we have everything installed and up and running we can try to play with this. Further examples will be in Business One context, so, if you don’t run B1 you will need to change the procedures creation scripts.

Let’s create a table type and 2 procedures: one with an output parameter of our table type and another one with a regular statement inside.

Run the following scripts:

• Create a type:

CREATE TYPE "SBODEMOGB"."INVOICEDETAILS" AS TABLE (
            "DocEntry" INT,
            "CardCode" NVARCHAR(15),
            "CardName" NVARCHAR(100),
            "DocTotal" DECIMAL,
            "DocDate" DATE
);

• Create a procedure with an output param:

CREATE PROCEDURE GETDOCSWITHOUTPARAM(IN docDate TIMESTAMP, IN cardCode NVARCHAR(40), IN maxTotal DECIMAL, OUT invDetails "SBODEMOGB"."INVOICEDETAILS")
 LANGUAGE SQLSCRIPT
 READS SQL DATA AS
 BEGIN
   invDetails = 
    SELECT "DocEntry",
    	"CardCode", 
    	"CardName", 
    	"DocTotal", 
    	"DocDate" 
    FROM OINV 
    WHERE OINV."CardCode" = :cardCode
    	AND OINV."DocDate" >= :docDate
    	AND OINV."DocTotal" <= :maxTotal;
 END;

• Create a procedure with a select statement:

CREATE PROCEDURE GETDOCSWITHSELECT(IN docDate TIMESTAMP, IN cardCode NVARCHAR(40), IN maxTotal DECIMAL)
 LANGUAGE SQLSCRIPT
 READS SQL DATA AS
 BEGIN
    SELECT "DocEntry",
    	"CardCode", 
    	"CardName", 
    	"DocTotal", 
    	"DocDate" 
    FROM OINV 
    WHERE OINV."CardCode" = :cardCode
    	AND OINV."DocDate" >= :docDate
    	AND OINV."DocTotal" <= :maxTotal;
 END;

Let’s call them via our wrapper. You can try it out right in the browser address bar, but I prefer Postman REST client. We need to specify our URL path and 5 parameters (2 mandatory and 3 for the procedure). Also we need to choose Basic authorization at “Authorization” tab and specify the credentials.

An expected result is the serialized to JSON list of objects. The same result will be for both of our procedures.

Result from SBODEMOGB:

[
    {
        "DocEntry": 334,
        "CardCode": "C30000",
        "CardName": "Microchips",
        "DocTotal": "8",
        "DocDate": "2012-07-04T22:00:00.000Z"
    },
    ...
    {
        "DocEntry": 1267,
        "CardCode": "C30000",
        "CardName": "Microchips",
        "DocTotal": "30",
        "DocDate": "2016-06-11T22:00:00.000Z"
    }
]

Hope it was useful. Feel free to share your feedback.

ATTACHMENTS

• ProcedurePerformer.zip: https://drive.google.com/uc?export=download&id=1B22zXo1Y8qgULR7hPe9JnX4OJXDzUYeQ
• ProcedurePerformerTest.zip: https://drive.google.com/uc?export=download&id=1STnbj7d-YugoZFZW_wrLclvztIytv_ds

Cheers,

Sergei Travkin,

DatatechOne