HANA Stored Procedure Exposed in CAP facing Problem when used along CDS query in Node.js

[former_member751031]

Hi Team,

I followed the below blog to use HANA stored procedure in CAP service

https://developers.sap.com/tutorials/hana-cloud-cap-stored-proc.html

Everything works fine but the issue occurs when we use both CDS query and subsequently call HANA Stored procedure in same event handler. The issue is we don't get any error but the scenario is as follows

1. I do some INSERT into some tables using CDS query language

The insert is succesful and data is found in table

2. On the 'After' event handler if I try to call a Stored Procedure which is try to fetch the inserted Data from the Update the same it is not able to find the Inserted data

3.But after the action is executed and if it try to call the stored procedure separately in a separate service then the data is getting recognized from the table and the needed action is working

So the basic question is why is the a lag or is there a execution difference between CDS query and the way we are calling HANA procedure. If so how to over come such lag so that we can run both the methods in same event and get the desired result.

[martinstenzig]

Are you doing a tx.commit() after your insert statements? Your code snippet got cutoff. Not doing an explicit commit created some weird behavior for me before.

[former_member751031]

Hi martinstenzig ,

Thanks for the answer tx.commit() worked for me.I think when we use tx.run() and then dbConn.callProcedurePromisified since tx was not committed yet it was not able to get the data inside the procedure.

But if I try to execute the HANA procedure as cql query and run it it was working without commit, since the cql query was running using tx.run() I think.

const tx = cds.transaction(req)
var vProcedure = `call "proc_approval_flow"(LT_VAR_OUT => ?,LT_CLAIM_REFERENCE=> '${approvalNewData[0].CLAIM_REFERENCE}',VAR_ARF=>'${approvalorRejectFlag}',APP_COMMENT=>'${Approver_Comments}')`
return tx.run (vProcedure)

BTW your solution helped me fix the issue I was facing.

Thanks

Sahasranaman

[former_member751031]

Hi martinstenzig,

Please give your solution as answer so that I can mark it the accepted answer

Thanks

Sahasranaman