Skip to Content

Create HANA Stored Procedure and Expose as CAP Service Function (SAP HANA Cloud)

Further combine SAP HANA Cloud native artifacts with the SAP Cloud Application Programming Model (CAP), and expose SQLScript procedures as service functions.
You will learn
  • How to create and deploy an SAP HANA Cloud SQLScript stored procedure
  • How to create a service function implemented via an SAP HANA SQLScript stored procedure
  • How to use a CAP service exit to implement the service function
jung-thomasThomas JungSeptember 26, 2022
Created by
jung-thomas
March 1, 2021
Contributors
thecodester
jung-thomas

Prerequisites

  • This tutorial is designed for SAP HANA Cloud. It is not designed for SAP HANA on premise or SAP HANA, express edition.
  • You have created database artifacts and loaded data as explained in the previous tutorial.

Video Version

Video tutorial version:

  • Step 1
    1. In the /db/src folder create a new database artifact named sleep of type hdbprocedure

      Create Procedure
    2. 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.

      sleep
      SQL
      Copy
      PROCEDURE "sleep" ( )
         LANGUAGE SQLSCRIPT
         SQL SECURITY INVOKER
         READS SQL DATA AS
      BEGIN USING SQLSCRIPT_SYNC as SyncLib;
      
      call SyncLib:SLEEP_SECONDS(10);
      
      END
      
    3. Save. Deploy the to the database again using the SAP HANA Projects view.

      Deploy
    4. Check the deployment log to make sure everything was successfully created in the database.

      Check Log
    5. Open the HDI Container in the Database Explorer

      Open Database Explorer
    6. This new Procedure is there now and can be tested.

      Test from Database Explorer
    7. There isn’t much output from the execution, but the 10 second wait makes testing quite easy.

      10 second wait
  • Step 2
    1. Now we want to add this Procedure to the CAP service as a function. Edit /srv/interaction_srv.cds.

      Add: function sleep() returns Boolean; to the service definition.

      This will expose an OData Function as part of the service interface.

      Add Function
    2. Just adding the function to the service definition doesn’t implement it. Unlike tables and views which are implemented by the built-in handler classes of CAP, we need to use a service handler exit to implement this ourselves. In order to do this create a file named interaction_srv.js in the /srv folder. The name must match interaction_srv.cds just with the different file extension for JavaScript. The matching naming tells CAP that you want to use this file to write custom exit handlers for the services defined in interaction_srv.cds.

      Create JavaScript Exit Handler
    3. In interaction_srv.js we will implement the call to the Stored Procedure. This logic will implement the exit handler for this function which in turn uses the standard hdb Node.js module to call the Stored Procedure from SAP HANA Cloud. Save the file.

      Call Stored Procedure
      JavaScript
      Copy
      const cds = require('@sap/cds')
      module.exports = cds.service.impl(function () {
          this.on('sleep', async () => {
          try {
              let dbQuery = ' Call "sleep"( )'
              let result = await cds.run(dbQuery, { })
              console.log(result)
              return true
          } catch (error) {
              console.error(error)
              return false
          }
          })
      })
      
    4. From the terminal return to the root of the project and issue the command: cds build

      shell
      Copy
      cds build
      
    5. From the console in the project root hopefully you still have the cds watch ... running. Otherwise start it again with cds watch --profile hybrid to start the CAP service layer for testing. If you have performed the tutorial SAP HANA Cloud, Add User Authentication to Your Application, remember you must also run the application router to test your service with authentication.

    6. The CAP preview UI doesn’t list functions or actions, however. Manually add /catalog/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.

      Test Service

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

Back to top