SAP Tech Bytes: HANA Client Tools for JavaScript Developers – Part 4 XSJS and CAP


Introduction

In this blog post series, we’ve explored several aspects of the HANA client tooling for JavaScript developers. In part one, we examined the differences between @sap/hana-client and hdb modules. In part two, we saw how to optimize our code using Promises / Async / Await. Then in part three, we looked at the value of additional wrapper modules like @sap/hdbext and how to use these tools within Express.

For this final blog post, I’ve saved two outliers in the JavaScript tooling space for HANA. We have the historic XSJS approach that still might be useful today for backwards compatibility with legacy code. And more looking to the future we also have the SAP Cloud Application Programming Model. How should each of these frameworks be considered in your choice of HANA tooling if you are a JavaScript developer?

Part of the reason I’m focusing on JavaScript in this blog series is the history around JavaScript and HANA dating back to the earliest days of HANA 1.0 SPS 05. That’s when SAP HANA Extended Application Services or XS was first integrated into the HANA database as its primary application server. Then later in HANA 1.0 SPS 11, XS Advanced evolved that application server infrastructure but JavaScript remained a primary runtime.

Although server-side JavaScript based, XSJS is different in a few keyways from the JavaScript based Node.js we’ve seen in this blog series so far. XSJS in XS Classic was based upon the Mozilla SpiderMonkey Virtual Machine, whereas Node.js is based in Google V8. Beyond that, XSJS’s most radical difference is the synchronous execution.  It has none of the non-blocking I/O aspects of Node.js. The final difference is that SAP added their APIs directly into the JavaScript engine. Those APIs are accessed via the $ variable. The API documentation can still be found here. This is also not general-purpose JavaScript development.  The entire framework is designed for and largely limited to execution of web-based services.  $.request contains the incoming HTTP Request details and $.response is where you write the HTTP response.

XSJS Sample

This is a simple XSJS sample. It uses the $.hdb API to get a connection and execute a query in the HANA database.  The $.hdb API is very much the precursor to the hdb module that we’ve used previously in the blog series.

We then take the ResultSet, which is JSON just as in @sap/hana-client and hdb, stringify it and place it into the response object.  You have a bare bones HTTP service endpoint right there.

/*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, new-cap: 0*/
/*eslint-env node, es6 */ "use strict" var conn = $.hdb.getConnection()
var query = `SELECT SCHEMA_NAME, TABLE_NAME, COMMENTS FROM TABLES LIMIT 10`
var rs = conn.executeQuery(query)
$.response.setBody(JSON.stringify(rs))
$.response.contentType = "application/json"
$.response.status = $.net.http.OK

So, if XSJS is an artifact of XS Classic, which is now deprecated – see service note 2465027, why even discuss it here? Primarily because companies have a considerable amount of historic content developed in XSJS that needs to continue to be used.  Therefore, it’s worth discussing migration and backwards compatible options for XSJS.

The first, and in my opinion best, option is to rewrite the application completely. Either use the HANA client modules we’ve discussed in the previous parts of this blog series or the SAP Cloud Application Programming Model to redesign and rebuild the application from the original requirements. This means considering architectural differences like Asynchronous processing.  But by combining what we’ve learned about Promises/Async/Await and Express you can rewrite most XSJS logic with minimal effort. The syntax itself won’t be that hard to adjust, but you will spend more time adapting to concepts like HDI or Cloud Foundry/Kyma deployment regardless of the language you use.

Another emerging option for the future might be XSK, although it’s important to note right up front that XSK is NOT read for productive use yet. This is an open-source project that emulates the XS Classic XSJS functionality (along with other parts of XS Classic) based upon Eclipse Dirigible. This could be an option in the future to port existing code to a modern runtime and HANA Cloud without having to undertake a rewrite.

The final option is another combability approach but one based upon a Node.js module.  SAP has delivered the module @sap/xsjs. It’s a standard, non-blocking I/O Node.js module that can execute existing synchronous XSJS code. It’s designed to run on XS Advance, Cloud Foundry or even in Kyma/Kubernetes environments since it runs on top of other modules we’ve seen like @sap/xsenv and @sap/hana-client. It can provide a quick way to reuse existing XSJS code (and although we’ve not talked about OData in this blog series also XSODATA artifacts), without rewrite. But it should only be considered for backwards compatibility and not major new development.

Bootstrap

The process does require some bootstrapping via normal Node.js coding to load the XSJS compatibility module.  But then you store the XSJS files in separate folders and they execute largely as they did in XS Classic. Here is an example of a complete Bootstrap but afterwards I’ll break down what it’s doing in smaller chunks.

/*eslint no-console: 0, no-unused-vars: 0*/
// @ts-check "use strict"
if(parseInt(process.versions.node.split('.')[0]) > 14){ console.log(`XSJS only supports Node.js version 10.x, 12.x, or 14.x`) process.exit()
}
import open from 'open'
const {default: xsjs} = await import('@sap/xsjs')
const xsenv = await import('@sap/xsenv') /** @type {Number} - Default Port*/
let port = parseInt(process.env.PORT) || 3_000
if (!(/^[1-9]\d*$/.test(port.toString()) && 1 <= 1 * port && 1 * port <= 65_535)) { console.error(`${port} is not a valid HTTP port value`)
}
var options = { anonymous : true, redirectUrl : "/index.xsjs"
} // configure HANA
xsenv.loadEnv()
try { options = Object.assign(options, xsenv.getServices({ hana: {tag: "hana"} }))
} catch (err) { console.log("[WARN]", err.message)
} // start server
xsjs(options).listen(port) let serverAddr = `http://localhost:${port}`
console.log(`Server listening on ${serverAddr}`)
open(serverAddr)

The first section of the code with the process.version check, I inserted that specifically for the code sample.

if(parseInt(process.versions.node.split('.')[0]) > 14){ console.log(`XSJS only supports Node.js version 10.x, 12.x, or 14.x`) process.exit()
}

Normally you wouldn’t see that in the bootstrap code.  I wanted it because @sap/xsjs only supports Node.js version 10.x, 12.x or 14.x. But the rest of my project was built using Node.js 16.x. Therefore, I just wanted a check to display a graceful error someone tried running the XSJS sample on Node 16.

If you do try to execute this on a newer version of Node.js you should get this explanatory message:

I personally use nvm – Node Version Manager on my development machine. This lets me install and easily switch between multiple versions of Node.js. So, when I want to test the XSJS samples, I just switch back to Node.js 14.x and then the execution starts up normally.

XSJS%20on%20Node.js%2014

XSJS on Node.js 14

The next section of code is also optional. It’s just checking that the port for the server to run on is valid.

 /** @type {Number} - Default Port*/
let port = parseInt(process.env.PORT) || 3_000
if (!(/^[1-9]\d*$/.test(port.toString()) && 1 <= 1 * port && 1 * port <= 65_535)) { console.error(`${port} is not a valid HTTP port value`)
}

You might see the port hard code sometimes, but it’s best to allow an override from the process environment. This way the underlying runtime (XSA, Cloud Foundry, or Kyma) can all assign unique ports when running this in a multi service environment. Furthermore, there is a check to make sure the port is numeric and less than 65535 (the highest, valid HTTP port number).

Then we have the most important part of the bootstrap.  We use the @sap/xsenv module, just like in all the other HANA client examples, to load the connection and security information for the target SAP HANA database.

// configure HANA
xsenv.loadEnv()
try { options = Object.assign(options, xsenv.getServices({ hana: {tag: "hana"} }))
} catch (err) { console.log("[WARN]", err.message)
}

The final block starts the web server on the selected port and then merely for the convivence of this sample opens a web browser to the running XSJS server. A productive service wouldn’t need nor want that browser open at the end.

// start server
xsjs(options).listen(port) let serverAddr = `http://localhost:${port}`
console.log(`Server listening on ${serverAddr}`)
open(serverAddr)

From a look into the past with XSJS, let’s now switch gears to look instead into the future. That would be the SAP Cloud Application Programming Model; the most modern way to design, build and deploy applications and extensions that use SAP HANA databases amongst other capabilities. A lot of learning content about the SAP Cloud Application Programming Model already exists. I won’t rehash it all here, but if you are new to the topic here are a few suggestions.

Main Documentation

Introduction Blog Post

Introduction Video

Starter Tutorial

Learning Journey

So often people look at CAP and only see a way to create OData services. But in fact, CAP has so much more functionality.   It has embedded the SAP Cloud SDK to help with the consumption of services, it has event and logging capabilities, it has domain modeling functionality, increasingly it has more functionality for integration with SAP HANA.  But what I want to focus on this blog post is the ability to use CAP much like we use the @sap/hana-client or hdb modules, as query builder and execution for the HANA Database.

So first, if you want to use any entity metadata within CAP we can “import” existing database tables and views without having to model them natively in CAP CDS. This is like creating a special “proxy entity”.  We use a special annotation, @cds.persistence.exists, and this tells CAP not to try and create this entity.

But this proxy entity does need to match the names of any columns in the underlying database table or view and it needs the data type and length of the column. Rather than retyping all this information, you can use the sample solution hana-cli tool and the inspectTable or inspectView command with the output type of cds.  This will read all the necessary metadata from the HANA catalog and produce the content ready to be used in CAP CDS.

We’ve done exactly that in this sample, pulling in the information about the SYS.TABLES view so that we can recreate the sample queries that we’ve been using across this blog series. Notice that our target isn’t HDI nor are we going to need a connection to an HDI container to query this entity.

CDS%20Proxy%20Entity%20for%20TABLES

CDS Proxy Entity for TABLES

To run CAP CQL (Query Language) or CQN (Query Notation) commands against this entity we do need to compile the cds definition into CSN:

 "cdsCompile": "cds compile cap.cds --to csn > csn.json",

In our standalone JavaScript (no service or OData here), we use cds.connect.to. This will use the default-env.json or any other environment binding (like the @sap/xsenv) to connect us to the target SAP HANA Database. Then we tell it the location of the compiled CSN file to load as a metadata model.

import cds from '@sap/cds'
export const db = await cds.connect.to('db', { model: './csn.json', credentials: null })
export async function testExample1() { try { let dbQuery = SELECT .from(db.entities.TABLES) .columns(TABLES => { TABLES.SCHEMA_NAME.as('SCHEMA'), TABLES.TABLE_NAME }) .limit(10) console.table(await db.run(dbQuery)) } catch (error) { console.error(error) }
}

From there we can build a query using CQL or CQN. That’s a HANA DB Query built and executed by the SAP Cloud Application Programming model with no service layer and no HDI container.

But you don’t require the proxy entity even if you don’t want the metadata parsing of CQL or CQN. You can also pass a raw query string into the db.run command as well.

import cds from '@sap/cds'
export const db = await cds.connect.to('db', { model: './csn.json', credentials: null })
export async function testExample2() { try { console.table(await db.run('SELECT CURRENT_USER, CURRENT_SCHEMA from DUMMY')) } catch (error) { console.error(error) }
}

To match up with our examples from the @sap/hana-client, there is even some brand-new functionality in CAP 5.9 (March 2022 release), that allows for the call of stored procedures via the run command as well.

import cds from '@sap/cds'
export const db = await cds.connect.to('db', { model: './csn.json', credentials: null })
/** * Test cds Await example with Stored Procedure - New in March '22 release https://cap.cloud.sap/docs/releases/mar22#driver-agnostic-results-for-stored-procedures */ export async function testExample3() { try { let dbQuery = ' Call SYS.IS_VALID_PASSWORD(PASSWORD => ?, ERROR_CODE => ?, ERROR_MESSAGE => ? )' // @ts-ignore - CDS Types aren't updated for this new Stored Procedure option yet let result = await db.run(dbQuery, { PASSWORD: "TEST" }) console.table(result) } catch (error) { console.error(error) }
}

Closing

I hope that you’ve enjoyed this overview of all the assorted options in HANA tooling for JavaScript developers. There are many options, each with their own strengths.  In the end, this information should make you aware of the breadth of the options but also help you to decide when best to use each or how to combine them together for the best solutions.

Part 1: hana-client vs. hdb

Part 2: Promises

Part 3: Wrappers and Express

Part 4: XSJS and Cloud Application Programming Model – This blog post