Create a Cloud Foundry or XS Advanced App that Queries SAP HANA
- How to use the command line interface (CLI) to deploy a Node.js app to Cloud Foundry or XS advanced
- How to view the logs and enable tracing in the deployed app
- How to connect from a Node.js app running in Cloud Foundry to an on-premise SAP HANA instance through the Cloud Connector
Prerequisites
- You have completed the first 4 tutorials in this mission
In the previous tutorials, applications that queried SAP HANA were run on a local machine. In this tutorial, a simple application will be run within the SAP BTP which uses Cloud Foundry or within the SAP HANA, express edition which uses XS advanced (and is also based on Cloud Foundry).
For additional details, consult The XS Advanced Programming Model. For a more complete example, see the Node.js topics in week 3 of Software Development on SAP HANA.
- Step 1
The command line interface (CLI) for Cloud Foundry is named
cf
while the CLI used for apps running in SAP HANA, express edition is namedxs
.-
Check to see if you have the CLI installed and verify the version.
ShellCopycf -v
To install the CLI, see Installing the CLI and Installing the cf CLI. After installing, add the
Cloud Foundry
folder to the path.Note that if a different version of CLI is installed, for instance version 7, then use
cf7 -v
instead ofcf -v
. Check theCloud Foundry
installation folder to find the CLI version.ShellCopyxs -v
The installer for xs can be downloaded from SAP Software Downloads under SAP HANA PLATFORM EDITION | SAP HANA PLATFORM EDITION 2.0 | XS RUNTIME 1.
-
Access help by running the following:
ShellCopycf help cf help login
ShellCopyxs help xs help login
-
Log in to the Cloud Foundry or XS advanced.
ShellCopycf login
If you are an SAP employee, you may need to enter your password plus a two-factor authentication passcode.
The API URL, if requested, can be found in the SAP BTP cockpit.
.
ShellCopyxs login
The API URL, if requested, can be verified in the XSA is up app.
-
Additional examples are shown below to view the target information, running services, and deployed apps.
ShellCopycf target cf services cf apps cf buildpacks
ShellCopyxs target xs services xs apps xs buildpacks
Additional details can be found at Getting Started with the cf CLI and Get Started with the XS CLI Client.
-
- Step 2
-
Create a folder named
nodeCF\nodeQueryCF
and enter the newly created directory.Shell (Microsoft Windows)Copymkdir %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF cd %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF
Shell (Linux or Mac)Copymkdir $HOME/HANAClientsTutorial/nodeCF/nodeQueryCF cd $HOME/HANAClientsTutorial/nodeCF/nodeQueryCF
-
Initialize the project, install express, and
@sap/hana-client
from NPM.ShellCopynpm init -y npm install express npm install @sap/hana-client
-
Open a file named
server.js
in an editor.Shell (Microsoft Windows)Copynotepad server.js
Shell (Linux or Mac)Copypico server.js
-
Add the code below to
server.js
.JavaScriptCopyvar express = require('express'); var hana = require('@sap/hana-client'); var app = express(); app.get('/', function (req, res) { res.send('Hello World'); }) app.get('/Customers', function (req, res) { var connOptions = { serverNode: 'XXXXXX.hana.trial-XXXXX.hanacloud.ondemand.com:443', //serverNode: 'linux-bj72:39015', UID: 'USER1', PWD: 'Password1' //traceFile: 'stdout', //traceOptions: 'sql=warning' }; var connection = hana.createConnection(); connection.connect(connOptions, function(err) { if (err) { return console.error(err); } var sql = 'select * from HOTEL.CUSTOMER;'; var rows = connection.exec(sql, function(err, rows) { if (err) { return console.error(err); } console.log(rows); res.send(rows); connection.disconnect(function(err) { if (err) { return console.error(err); } }); }); }); }) const port = process.env.PORT || 3000; var server = app.listen(port, function () { var host = server.address().address var port = server.address().port console.log("Example app listening at http://%s:%s", host, port) })
Update the values for host and port.
-
Run and test the app locally.
ShellCopynode server.js
-
- Step 3
-
Create a deployment descriptor.
Shell (Microsoft Windows)Copycd .. notepad manifest.yml
Shell (Linux or Mac)Copycd .. pico manifest.yml
Add the code below to
manifest.yml
.ymlCopy--- applications: - name: nodeQueryCF random-route: true type: nodejs path: nodeQueryCF command: node server.js memory: 128M
For additional details, consult App Manifest Attribute Reference.
-
Deploy the app to Cloud Foundry or XS advanced.
ShellCopycf push
Notice above the URL to open the app was generated as the
manifest.yml
contained the random-route setting.ShellCopyxs push
.
Alternatively, the URL of the app can be found by running the following command:
ShellCopycf app nodeQueryCF
ShellCopyxs app nodeQueryCF
-
Test the app.
.
For additional details see:
Developing Node.js in the Cloud Foundry Environment
Tutorial: Setting up your JavaScript Application in XS Advanced
-
- Step 4
-
The app can be stopped and started with the below commands:
ShellCopycf stop nodeQueryCF cf start nodeQueryCF
-
The applications lifecycle events can be seen with the below command:
ShellCopycf events nodeQueryCF
-
The logs of the application can be seen with the below command:
ShellCopycf logs --recent nodeQueryCF
The following command will show the tail of the log.
ShellCopycf logs nodeQueryCF
-
As of version 2.7, the SAP HANA client interfaces can output trace information to
stdout
orstderr
.ShellCopycf set-env nodeQueryCF HDB_SQLDBC_TRACEFILE stdout cf set-env nodeQueryCF HDB_SQLDBC_TRACEOPTS SQL=WARN cf restage nodeQueryCF cf env nodeQueryCF cf logs nodeQueryCF
Alternatively, the trace settings can be specified in the application code.
Refresh the browser and notice that the trace information can now be seen.
-
The deployed app can also be managed in the associated cockpit.
SAP BTP Cockpit
Details of the application
nodeQueryCF
SAP HANA XS Advanced Cockpit
Note that the number of running instances can be scaled if needed.
-
- Step 5
The Cloud Connector enables communication from the SAP BTP running in the public internet to securely connect to a configured on-premise system such as SAP HANA, express edition. The following steps demonstrate how to do this with the previously deployed app
nodeQueryCF
.-
Follow step 3 at Access Remote Sources with SAP HANA Database Explorer to install and configure the Cloud Connector.
-
In the project created in step 2, perform the following steps to bind a connectivity service instance to the application.
-
Navigate to Service Bindings and choose Bind Service.
-
Add the Connectivity service.
-
Provide an instance name such as
MyConnectivityService
. -
Examine the values of the connectivity service. In particular, the indicated values below are used for the
proxyPort
andproxyHostname
values in the server.js file and will be to access the proxy service which enables communication with the cloud connector.
-
-
Add the service name to the project’s manifest.yml.
Shell (Microsoft Windows)Copycd %HOMEPATH%\HANAClientsTutorial\nodeCF notepad manifest.yml
Shell (Linux or Mac)Copycd $HOME/HANAClientsTutorial/nodeCF pico manifest.yml
ymlCopyservices: - MyConnectivityService
-
Make a backup of the server.js file and add the node module
axios
which is promise based HTTP client and is used to fetch a JWT token. For further details see SAP Cloud Platform: How to call – on-Premise System – from Node.js app – via Cloud Connector.Shell (Microsoft Windows)Copycd nodeQueryCF copy server.js server.js.bak npm install axios
Shell (Linux or Mac)Copycd nodeQueryCF cp server.js server.js.bak npm install axios
-
Open the file named
server.js
in an editor and replace its contents.Shell (Microsoft Windows)Copynotepad server.js
Shell (Linux or Mac)Copypico server.js
JavaScriptCopyvar axios = require('axios'); var express = require('express'); var hana = require('@sap/hana-client'); var app = express(); const VCAP_SERVICES = JSON.parse(process.env.VCAP_SERVICES); const conSrvCred = VCAP_SERVICES.connectivity[0].credentials; app.get('/', function (req, res) { res.send('Hello World'); }) app.get('/Customers', async function (req, res) { const connJwtToken = await _fetchJwtToken(conSrvCred.token_service_url, conSrvCred.clientid, conSrvCred.clientsecret); var connOptions = { serverNode: 'v-linux-bj72:39015', // Virtual host specified in the Cloud Connector proxyUsername: connJwtToken, proxyPort: conSrvCred.onpremise_socks5_proxy_port, proxyHostname: conSrvCred.onpremise_proxy_host, //proxyScpAccount: 'myLocID', // Cloud Connector's location ID if specified in the Cloud Connector // A location ID is used when multiple Cloud Connectors are connected to the same subaccount UID: 'USER1', PWD: 'Password1' //traceFile: 'stdout', //traceOptions: 'sql=warning' }; var connection = hana.createConnection(); connection.connect(connOptions, function(err) { if (err) { return console.error(err); } var sql = 'select * from HOTEL.CUSTOMER;'; var rows = connection.exec(sql, function(err, rows) { if (err) { return console.error(err); } console.log(rows); res.send(rows); connection.disconnect(function(err) { if (err) { return console.error(err); } }); }); }); }) const port = process.env.PORT || 3000; var server = app.listen(port, function () { var host = server.address().address var port = server.address().port console.log("Example app listening at http://%s:%s", host, port) }) const _fetchJwtToken = async function(oauthUrl, oauthClient, oauthSecret) { return new Promise ((resolve, reject) => { const tokenUrl = oauthUrl + '/oauth/token?grant_type=client_credentials&response_type=token' const config = { headers: { Authorization: "Basic " + Buffer.from(oauthClient + ':' + oauthSecret).toString("base64") } } axios.get(tokenUrl, config) .then(response => { resolve(response.data.access_token) }) .catch(error => { reject(error) }) }) }
-
Redeploy the app.
ShellCopycd .. cf push
-
The application running in the cloud, is now accessing data from an on-premise SAP HANA, express instance.
Congratulations, you have built, deployed and run an app that queries SAP HANA in Cloud Foundry and XS advanced as well as become familiar with the command line interface.
Which of the following statements are true?
-