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:
- 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>.
- Once the table was created, insert the passed data using placeholder syntax to avoid SQL injection.
- 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).
- 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.
- 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!
😉
