Skip to Content
0

Service Layer - Call procedure or view from database

Aug 02, 2017 at 12:27 PM

422

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Ralph Oliveira
Aug 08, 2017 at 10:01 AM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

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

0

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.

0
Former Member

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?

0

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

0