Calling a Procedure using event handlers in Node.JS with CAPM

[dev_amar]

Hello SCP Experts,

I am using the CAP Model to develop an application in which I have a requirement to call a Custom Logic in a Procedure using event handler in node.js (CDS-service). Below is the code snippet that I have used, however, while running the service the procedure call always returns an empty object (i.e) oReturn in the below cat-service.js is always empty.

But when I run the procedure individually I get the expected outcome from the procedure.

cat-service.js

srv.on("CREATE", "LocInfo", async (req) => {
try {
const tx = cds.transaction(req)
var sKUNNR = req.data.KUNNR;
var sPOSITION = req.data.POSITION;
var sERROR = null;
var oOutput;
const sQuery = "CALL <MY_PROCEDURE_NAME> (KUNNR => ?,POSITION => ?,T_OUTPUT => ?)";
var oReturn = await tx.run(sQuery, [sKUNNR, sPOSITION]);
console.log(oReturn);
return oReturn;
} catch (e) {
req.reject(405,"Unknown exception has occurred: " + e.toString());
}

MyProcedure.hdbprocedure

PROCEDURE "write_location_info" ( IN KUNNR NVARCHAR(10), IN POSITION NVARCHAR(100), OUT T_OUTPUT <MY_TABLE_NAME>)
   LANGUAGE SQLSCRIPT
   SQL SECURITY DEFINER
   AS
BEGIN
   /*************************************
       Write your procedure logic 
   *************************************/
   DECLARE e_error NVARCHAR(1);
   IF :KUNNR = null or :POSITION = null then
    e_error = 'X';
   else
    e_error = 'X';
    UPSERT "<MY_TABLE_NAME>"
    VALUES (:KUNNR, :POSITION);   
   end if;
   T_OUTPUT = SELECT * FROM "<MY_TABLE_NAME>" WHERE "KUNNR" = :KUNNR;
END

Please guide me if I am making any mistakes here. I have referred the CAP Documentation, however, i couldn't find any reference that calls a procedure,

CAP Reference Document

Let me know if any further information is required for the above logic.

Regards,

Amar Shukla

[johannesvogel · Accepted solution]

Hi Amar,

for connecting to SAP HANA are you using the "hdb" or "@sap/hana-client" module?

With "@sap/hana-client" it should work out of the box.

Best regards,

Johannes

[dev_amar · Accepted solution]

Hi Johannes,

Thanks for your prompt response. Actually, I have created few design-time tables using CDS and these tables are then exposed as a odata service using node js module. While I expose the service, the generic handlers are not able to handle my requests. Thus i followed CAP Documentation to add custom logic. To answer your question, I am not using either of the modules. I am using const tx = cds.transaction(req) and with the help of tx i am running SQL Query. The cds module used here is @sap/cds. Please guide if my understanding is incorrect.

[gregorw · Accepted solution]

Hi Amar,

please check the package.json files of your project. There must be somewhere a reference to either the "hdb" or "@sap/hana-client" module.

CU
Gregor

[dev_amar · Accepted solution]

Thanks, Johannes. Post changing the HDB module to @sap/hana-client, the application is now running as expected. Just out of curiosity, is there documentation of this behavior on SAP Portal or In the developer guide? Or maybe a brief document on the difference between these two modules?

[johannesvogel · Accepted solution]

Hi Amar, unfortunately I do not know of any document. But the "hdb" module is deprecated already for a while and the recommendation is to use the successor "@sap/hana-client".

[dev_amar · Accepted solution]

Perfects! 🙂 Thanks once again.

[udita10 · Accepted solution]

Hi dev_amar,
You did the post call via Cloud SDK ? We are trying a post call to an On premise service but getting a 403 error.
Can you please help?
Thanks & Regards,

Udita Saklani