Skip to Content

Service Layer - Call procedure or view from database

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 08, 2017 at 10:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded