Skip to Content

First of all, I was inspired on writing this article as I have been doing this type of scenario for a few years and it is a #simple, yet #powerful approach. In this scenario, I want to describe how to send multiple values into a stored procedure and also returning an array of errors.

First, assume that we need to send multiple records in as a structure, such as a list of values. in my case, I am only adding one column in the array, however, the array may contain more than one property (see output structure for more than 1 column).

My input array looks as follows (and I pass that as n input param to the stored proc):

var someArray = [ { paramValue: value},..,{paramValue: someValueN} ];

my output array will look like (returned as an output param from the stored proc):

var outputError = [ { Code: “code”, Message: “someMessage”},.,{ Code: “code2”, Message: “someMessage2”} ];

in order to accomplish this, I created types in my CDS file as:

tts.JPG

my stored proc looks like this:

1) look at the input param data type – it is the same as the type declared in the CDS file

2) similar to 1 – the output param data type is showed in the CDS file

I  purposely left some comments in this stored proc to showcase that:

a) multiple input/output parameters can be provided

b) the output parameter may be built based on some rules (in my case to return errors)

c) the input parameter may be used as a JOIN to return a subset of records based on a business rule, perform a union, etc.

storedProc-in_out.JPG

now from my XSJS code, I need to invoke the stored proc anc pass the input param with multiple values and get the output based on my variable name:

xsjscode.JPG

I hope this is easy to follow and again – this was a personal experience example to demonstrate the use of invoking stored procedures from XSJS (SP09 and later) and passing multiple values into an input parameter as well as being able to read the result set of an output parameter based on the variable name.

please share your experiences and also provide feedback in case you have similar scenarios

thanks for your time in reading this post!!

To report this post you need to login first.

6 Comments

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

  1. Sergio Guerrero Post author

    I should add – this scenario was used on a XSJS REST web service which contains business logic and custom errors – hence I couldn’t use odata due to business rules validations, etc.

    (0) 
  2. Jay Prakash Singh

    Hi Sergio,

     

    Actually i am deploying my service into the gateway there i have created my service with the help of OCI, It only exposes one service at a time it means if i will create xsjs service i have to create another service ,which is not good, that is why i am trying to accommodate in xsodata.

    Is this possible to perform batch in xsodata or i need to use xsjs only?

    Thanks,

    Jay

    (0) 
  3. Jay Prakash Singh

    Hi Sergio Guerrero ,

     

    Can you give me code for xsjs my email id is jayprakash0070@gmail.com also can you check below code if it is correct.

     

    right now i am getting error JSON.parse: unexpected character at line 1 column 1 of the JSON data

     

    actually i am not able to debug xsjs with some technical reason so i am trying to solve using sapui5 request only and when i call service from sapui5 in response i am getting this error.

     

    xsjs code :

     

    function getInputAsInTT(listOfValues){
    var i=0;
    var inTT = [];
    for(i=0;i<listOfValues.length;i++){
    inTT.push({
    paramValue : listOfValues[i]
    });
    }
    return inTT;

    }
    function getTxtData(request) {
    /*var connection = $.db.getConnection();*/

    var connection = $.hdb.getConnection();
    var output = {errors:[],otherOutputs:[]};

    try {

    var dataEntryQuery = connection.loadProcedure(“schema”,”path.procedure::InsertValues”);

    var inVals = getInputAsInTT(request);
    $.response.setBody(‘error1’);
    var result = dataEntryQuery(inVals,[]);
    $.response.setBody(result.OUT.ERRORS);
    output.errors = result.OUT.ERRORS;

    } catch(ex) {
    output.errors = [‘there is error’ + ex.message];
    }
    return output;
    }

    function doGet() {
    try {
    var aCmd = $.request.parameters.get(‘newEntries’);
    var ajson = JSON.stringify(aCmd) ;
    var obj = JSON.parse(ajson);
    var newEntries = obj.data;
    $.response.contentType = “application/json”;
    $.response.setBody(getTxtData(newEntries));
    } catch (err) {

    $.response.setBody(err.message);
    $.response.returnCode = 200;
    }
    }
    doGet();

     

    sapui5 code :

     

    $.ajax({
    headers: myHeadersend,
    url: url2,
    type: “POST”,
    data: {
    newEntries: JSON.stringify(oEntry)
    },
    processData: false,
    async: false,
    timeout: 1000000,
    error: function(jqXHR, textStatus, errorThrown) {

    sap.m.MessageBox.error(“errorAttached”);
    },
    success: function(data, textStatus, jqXHR) {
    sap.m.MessageBox.error(“success”);

    }

    });

     

    oEntry will be a object with attribute and if it work i will pass whole object array to insert batch operation.

     

    Thanks,

    Jay

    (0) 

Leave a Reply