The following example of usage of the SAP HANA XS $.hdb API shows how to connect to the SAP HANA DB and how to read values of the output declaed as table in HANA Stored procedure.
The example stored procedure already deployed into hana db and it will return OUTPUT_TABLE as a result set
CREATE PROCEDURE "<<SCHEMA_NAME>>"."PROCEDURE_SALES_REPORT"(
IN DISCOUNT INTEGER,
OUT OUTPUT_TABLE "<<SCHEMA_NAME>>"."TT_SALES" )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER
AS
BEGIN
var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT
FROM SCHEMA_NAME.REGION AS T1
INNER JOIN
SCHEMA_NAME.SALES AS T2
ON T1.REGION_ID = T2.REGION_ID;
var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME
FROM :var1 AS T1
INNER JOIN
SCHEMA_NAME.PRODUCT AS T2
ON T1.PRODUCT_ID = T2.PRODUCT_ID;
OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT -
(SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,
PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
FROM :var2
GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;
END;
Syntax for Calling Stored procedure and reading data from the result set
var body = $.request.body.asString();
var reqObj = JSON.parse(body);
var action = reqObj.action;
var discount = $.request.parameters.get('discount');
var data=[];
var rs;
var conn=$.hdb.getConnection({"sqlcc": "PROC_TEST::cred"});
var pstm= conn.loadProcedure('<< scheme name >>', '<< procedure name >>');
rs = pstm(discount);
var rslength = (rs["OUTPUT_TABLE"]).length;
for(var i = 0 ; i<rslength-1;i++)
{
data.push
({
"SALES_AMOUNT":rs["OUTPUT_TABLE"][i]["SALES_AMOUNT"],
"NET_AMOUNT":rs["OUTPUT_TABLE"][i]["NET_AMOUNT"],
"PRODUCT_NAME":rs["OUTPUT_TABLE"][i]["PRODUCT_NAME"]
});
}
conn.commit();
$.response.setBody(JSON.stringify(data));
