Service Layer - Call procedure or view from database

[former_member185682]

Hi Experts,

Suppose that I have an Add-on for SAP Business One Hana version, and for this I use UI API and Service Layer. What is the correct approach if I need call a procedure or view from my hana database?

Update:

I found on Service Layer documentation, that "Business object RecordSet (direct SQL) is not supported."

Then I'm not sure, if there are some approach on service layer for this situation, or maybe I need create a function on Hana XSJS that call the view or procedure.

Thanks for your attention.

Kind Regards,

Diego Lother

[former_member197733 · Accepted solution]

Yes, you need to create an XSJS to call a procedure. Here is an example.

Lets say I run this SQL to create a procedure.

/* Create a TABLE TYPE for the procedure results */
CREATE TYPE "SBODEMOUS"."BPDETAILS" AS TABLE (
            "CardCode" NVARCHAR(15),
            "CardName" NVARCHAR(100),
            "CardType" CHAR(1),
            "Balance" DECIMAL
);

/* Create the Procedure */
CREATE PROCEDURE GETBPBYTYPE(IN cardtype CHAR(1), OUT bpListDetails "SBODEMOUS"."BPDETAILS")
 LANGUAGE SQLSCRIPT
 READS SQL DATA AS
 BEGIN
   bpListDetails = 
    SELECT T0."CardCode", T0."CardName", T0."CardType", T0."Balance" 
    FROM OCRD T0 
    WHERE T0."CardType" =:cardtype;
 END;

I can call this procedure from an XSJS:

function showData(bpType){
    $.response.contentType = "application/json";
    
    var connection = $.hdb.getConnection();
    
    //Load procedure of specified schema
    var getBpByTypeProc = connection.loadProcedure('SBODEMOUS', 'GETBPBYTYPE');

    //The getBpByTypeProc object act as proxy to the procedure 
    var results = getBpByTypeProc(bpType); 

    //Build the response
    $.response.status = $.net.http.OK;
    $.response.contentType = "application/json";
    $.response.setBody(JSON.stringify(results));

    connection.close();    
}

var bpType = $.request.parameters.get("bpType");

showData(bpType);

Sample xsjs call:

http://<hanaserver>:8000/<project>/getBp_proc.xsjs?bpType=C

[former_member185682 · Accepted solution]

Hi Ralph,

Thanks for the help.

Do you know if is there some intention to create a similar Recordset functionality on Service Layer in future versions?

Kind Regards,

Diego Lother

[former_member197733 · Accepted solution]

It's on the backlog. But i would say still better to rely on XSodata on top of a HANA View to perform this kind of operation.

[Former Member · Accepted solution]

When making these XSJS are you setting it for anonymous access? Or have you worked out some other method of authentication. Let us say you have a web app that authenticates using the service layer, can you pass this to the XSJS or, again, would you suggest using anonymous calls?

[former_member197733 · Accepted solution]

Best case is your Web App is hosted in the HANA XS Engine, so the user will already be authenticated when acessing it.

You can also set a annonymous connection (with a xssqlcc file, just like I did with the B1 Assistant) in the back (for the views you want ot expose) but require B1 Credentials to enter on your app. You can validate those credentials using the B1H Extreme App Framework Login Service

[former_member500538 · Accepted solution]

Hi,

Thank you for your help. I applied your solution. But I have a question.

We are developing an integration project and I dont want to share my hana user and password with partners. What is your suggestion for authentication? I tried token but still partner can reach my whole database. What can I restrict service layers?

Thanks

Haktan

[former_member197733 · Accepted solution]

use this instead

[former_member197733 · Accepted solution]

UPDATE.

It is already in the product