Calling a procedure with table input on HANA in a CAP Node.js handler

By /

During a customer’s migration from XSA to CAP on BTP, the following question came up: How can a stored procedure with table input be called in a custom handler? As this is certainly not the only customer facing this challenge, I wanted to share the results of my research (without claiming it being the best solution).

On XSA, this was typically done using @sap/hdbext, as its loadProcedure function provides this functionality out of the box. However, using @sap/hdbext includes handling credentials, creating connections, managing transactions, etc. And this is something CAP already does for you. Additional to the development overhead, you also want to join the same transaction that is used by all other database interactions to maintain atomicity. That is, either all operations are applied or none are. Hence, we need to do what @sap/hdbext does behind the scenes, which is creating so-called local temporary tables, populating the tables with the respective data, and then call the respective procedure.

This blog post shows how to do so based on our well-known bookshop example. We’ll make use of a stored procedure with a table input that returns the best-selling book per author, filtered by a list of author IDs that is provided by the client.

 

Data Model

We start with a basic data model including Books and Authors, where each book can have one author.

db/schema.cds

namespace my.bookshop;

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  author : Association to Authors;
}

entity Authors {
  key ID : Integer;
  name   : String;
  books  : Association to many Books on books.author = $self;
}

 

Stored Procedure

The stored procedure bestseller_by_author uses a window function to determine the book per author with the least items in stock (assuming this means the most sales), where the author’s ID must be included in the IN TABLE author, which shall be provided by the client.

db/src/bestseller_by_author.hdbprocedure

PROCEDURE bestseller_by_author (
  IN author TABLE ( ID INTEGER ),
  OUT bestseller TABLE ( ID INTEGER )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  bestseller =
    SELECT
      ID
    FROM (
      SELECT
        ID,
        ROW_NUMBER() OVER (PARTITION BY author_ID ORDER BY stock) AS ROW_NUM
      FROM my_bookshop_Books
      WHERE author_ID in (SELECT ID FROM :author)
    )
    WHERE ROW_NUM = 1;
END

 

Service Model

We define a CatalogService that exposes a projection on Books as well as a custom action getBestsellerByAuthor that can be called with an array of integers as filter input for the stored procedure from above.

srv/cat-service.cds

using my.bookshop as my from '../db/data-model';

service CatalogService {
  @readonly entity Books as projection on my.Books;

  action getBestsellerByAuthor(authors: many Integer) returns many Books;
}

 

Custom Handler

After all this ground work, we have now reached the interesting part: the custom handler calling the stored procedure with table input. As mentioned in the introduction, this is only possible using so-called local temporary tables (see CREATE TABLE Statement in HANA documentation).

Hence, the handler works as follows:

  1. Create the local temporary table based on the stored procedure (i.e., with a single column ID of type INTEGER) with a random name to prevent any table already exists errors. Further, the name of a local temporary table must start with #. Hence, we generate a name following pattern #ltt_<random chars>.
  2. Once the table was created, insert the passed data using placeholder syntax to avoid SQL injection.
  3. Call the stored procedure while referencing the newly created local temporary table. Please note that this currently must be done in uppercase in order for the runtime to match in- and output parameters (for which a metadata lookup is required).
  4. Drop the local temporary table for housekeeping purposes. This is not necessarily mandatory, as we use random names and local temporary tables only exist in the confines of a session. However, sessions may be reused (fetch idling connection from pool), so it should be considered good practice.
  5. Finally, we select the books based on the book IDs we got from the stored procedure, and return the full entities to the client.

As you may or may not have noticed, we simply do await cds.run(…) for all database interactions (plus the final await SELECT). We can do so due to CAP’s automatic transaction management. If not explicitly requested via APIs such as cds.tx(tx => {…}), CAP executes all statements inside a custom handler in a so-called nested transaction, which is committed or rolled back together with the respective root transaction that defines a unit of work.

srv/cat-service.js

const cds = require('@sap/cds')
const LOG = cds.log('cat-service')

module.exports = function () {
  const { Books } = this.entities

  this.on('getBestsellerByAuthor', async function (req) {
    const ltt = `#ltt_${cds.utils.uuid().replace(/-/g, '')}` //> random name
    await cds.run(`CREATE LOCAL TEMPORARY TABLE ${ltt} (ID INTEGER)`)
    await cds.run(`INSERT INTO ${ltt} VALUES (?)`, req.data.authors.map(a => [a]))
    const query = `CALL BESTSELLER_BY_AUTHOR(AUTHOR => ${ltt}, BESTSELLER => ?)`
    const { BESTSELLER } = await cds.run(query)
    await cds.run(`DROP TABLE ${ltt}`) //> cleanup
    return await SELECT.from(Books).where('ID in', BESTSELLER.map(b => b.ID))
  })
}

 

Test

Finally, let’s test our custom action that calls the stored procedure with the following request:

POST http://localhost:4004/odata/v4/catalog/getBestsellerByAuthor
Content-Type: application/json

{
  "authors": [150, 42]
}

After some time (this is a costly implementation!), the server answers with the following response:

HTTP/1.1 200 OK
Content-Type: application/json;odata.metadata=minimal

{
  "@odata.context": "$metadata#Books",
  "value": [
    {
      "ID": 251,
      "title": "The Raven",
      "stock": 100,
      "author_ID": 150
    },
    {
      "ID": 261,
      "title": "The Hitchhiker's Guide to the Galaxy",
      "stock": 100,
      "author_ID": 42
    }
  ]
}

 

Now you’ll either have to trust me that there were multiple books per author in the initial data, or you’ll have to try it yourself! 😉

Scroll to Top