ex5

Exercise 5 - Function Implemented as Stored Procedure

In this exercise, we will create a service function which is implemented via an SAP HANA SQLScript Stored Procedure.

Exercise 5.1 Add Stored Procedure and Use it to Implement a CAP Function

Video Link

1. In the /db/src folder create a new file named sleep.hdbprocedure. This is a very simple SAP HANA Stored Procedure that calls the built-in SYNC library to put processing to sleep for 10 seconds. It's a nice tool to be able to test the impact of long running queries without actually putting unnecessary load on the system.
   

PROCEDURE "sleep" ( )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   READS SQL DATA AS
BEGIN USING SQLSCRIPT_SYNC as SyncLib;
 
call SyncLib:SLEEP_SECONDS(10);

END

2. Save. Run npm run build from the terminal. Although this new stored procedure isn't part of CAP, the build will still copy it into the /gen/db folder.
   

3. Run npm run hana. Likewise the CDS deploy to HANA will also deploy native SAP HANA artifacts as well. Not everything in your project has to be implemented via CAP. You can also mix in HANA native development as well.
   

4. If you wish you can return to the Database Explorer. This new Procedure is there now and can be tested.
   
   
   

5. But now we want to add this Procedure to the CAP service as a function. Edit /srv/cat-service.cds.
   Add: function sleep() returns Boolean; to the service definition. This will expose an OData Function as part of the service interface.
   

6. Just adding the function doesn't do anything. We need to use the service handler exit in cat-service.js again to implement the call to the Stored Procedure. This logic will implement the exit handler for this function which in turn uses the standard @sap/hdbext module to call the Stored Procedure from HANA.
   

    this.on('sleep', async () => {
        try {
            const db = await cds.connect.to('db')
            const dbClass = require("sap-hdbext-promisfied")
            let dbConn = new dbClass(await dbClass.createConnection(db.options.credentials))
            const hdbext = require("@sap/hdbext")
            const sp = await dbConn.loadProcedurePromisified(hdbext, null, 'sleep')
            const output = await dbConn.callProcedurePromisified(sp, [])
            console.log(output.results)
            return true
        } catch (error) {
            console.error(error)
            return false
        }
    })

7. But since we used two additional HANA modules in our code, we need to add those to our root package.json. Please add sap-hdbext-promisfied and @sap/hdbext as shown.
   

8. Save your open files and Run the npm install from the Terminal.

9. Run npm run build then npm start. The CAP preview UI doesn't list functions or actions, however. Just click on the /catalog link for the entire service
   

10. Manually add /sleep() to the end of the URL. If it works correctly it should take 10 seconds to respond since the procedure is running a sleep operation for that long.
    

Summary

You've now added an OData function to your service layer which in turn is implemented as an SAP HANA Stored Procedure

All Done - Congratulations you've completed this course workshop