XS Callling Stored Proc with multiple values in input param
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:
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.
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:
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!!
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.
Hi All,
I am facing issue described in the blog
https://answers.sap.com/questions/686534/issue-in-inserting-multiple-record-in-hana-using-b.html
can anyone take a look please .
Thanks,
Jay
did you try the method described in this blog? i know you are trying to use odata.. but have you tried this way?
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
I am not familiar w the services in the gateway, however, technically, yes you should be able to do a batch operation via odata.
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