Skip to Content

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

 

Cheers,

Sergei Travkin,

DatatechOne

 

 

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Lucia Subatin

    Hi, Sergei,

    With all of the warnings it feels like you see this kind of feedback coming. I generally enjoy hitting the like button and moving on but I feel I need to add some warnings in case someone accidentally falls in this post before getting any of the many available official or community results:

    • First, my mom told me not to talk to strangers nor import random software into my instances, so I’m not even clicking those links and not implying they are fine by not commenting about their content. I know, I just made a warning about a warning… My suggestion: have you considered code snippets? They are transparent and paranoids like me can check them without second thoughts.
    • Everything you mention here is already vastly documented and a lot of what you are mentioning is deprecated. If your quest was only solved by the quoted answer and you decided to create this blog post as a contribution, the search engine seems to be playing some nasty tricks on you. I understand that sometimes we do not even know how to ask, but the developer guide and product managers should be bullet-proof starting points. Here are some known-good sources from a quick and back-dated search:
    • The method you are using to import packages deserves a special mention. Why not sharing the proper import methods? Delivery units are just as easy and add extra checks for the same price.

    Take luck,

    Lucia.

    (2) 
    1. Sergei Travkin Post author

      Hi Lucia,

      Thank you for your comment, I really appreciate so detailed feedback.

      Let me describe the prerequisites why I decided to write this post. That was my first experience working with XS and I faced with some pitfalls when I needed to implement the mentioned functionality. For experienced HANA developers that might be vest known and not interesting but I considered the possibility to call any procedures via HTTP without any changes in the end XS JS files useful for people who have the same need. The attached file is just an archive, so you can check all files inside to make sure they are safe. The project contains 2 files and I mentioned that for one of them I use an outdated approach, so, you can Import all of them or manually create a project and add only one with hdb. The pointed why I showed this import approach is because you can manage everything using HANA Studio only and that’s transparent to see all files inside. Also I have problems with creating a delivery unit (yes, firstly I wanted to suggest this way) and I was afraid that people can have problems with importing as well. Please note that it was developed and tested on HANA 1.0 as SAP B1 does not support HANA 2.0 yet. And again, that is not a guide how to call a procedure that is the project that allows you call any procedure without any changes in the XS JS part.

      P.S. https://help.sap.com/viewer/52715f71adba4aaeb480d946c742d1f6/2.0.00/en-US/17c4ada7f4214d4aabdcf941e178f27b.html btw, this link doesn’t work. It shows 404 page

      (0) 
      1. Lucia Subatin

        Hi, Sergei,

        The link was missing the final “l”, now I fixed it.

        No need to explain really, we are both doing our share. You are free to publish how to perform a poor implementation and not follow recommendations or existing documentation. I am free (even a bit responsible) to point out it is a bad idea.

        I am still not opening the files but please don’t take it personally, the Nigerian prince also keeps insisting their deal is legit and i still do not click on their links 😉

        Last but not least, everything you are describing has been around for a while so chances are that even if something is new to you, someone else has already solved it. If you have trouble following official documentation, I would suggest you keep at it or seek help (answers.sap.com).

        Cheers,

        Lucia.

        (1) 

Leave a Reply