Skip to Content

Create a Cloud Foundry or XS Advanced App that Queries SAP HANA

Create a Node.js app that queries SAP HANA and can be run in Cloud Foundry or XS Advanced.
You will learn
  • 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
danielvaDan van LeeuwenJanuary 31, 2022
Created by
danielva
January 12, 2021
Contributors
danielva

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 named xs.

    1. Check to see if you have the CLI installed and verify the version.

      Shell
      Copy
      cf -v
      
      cf cli version

      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 of cf -v. Check the Cloud Foundry installation folder to find the CLI version.

      Shell
      Copy
      xs -v
      
      xs cli version

      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.

    2. Access help by running the following:

      Shell
      Copy
      cf help
      cf help login
      
      Shell
      Copy
      xs help
      xs help login
      
    3. Log in to the Cloud Foundry or XS advanced.

      Shell
      Copy
      cf login
      
      cf 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.

      api URL

      .

      Shell
      Copy
      xs login
      
      xs login

      The API URL, if requested, can be verified in the XSA is up app.

      xsa running
    4. Additional examples are shown below to view the target information, running services, and deployed apps.

      Shell
      Copy
      cf target
      cf services
      cf apps
      cf buildpacks
      
      Shell
      Copy
      xs target
      xs services
      xs apps
      xs buildpacks
      
      services

      Additional details can be found at Getting Started with the cf CLI and Get Started with the XS CLI Client.

  • Step 2
    1. Create a folder named nodeCF\nodeQueryCF and enter the newly created directory.

      Shell (Microsoft Windows)
      Copy
      mkdir %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF
      cd %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF
      
      Shell (Linux or Mac)
      Copy
      mkdir $HOME/HANAClientsTutorial/nodeCF/nodeQueryCF
      cd $HOME/HANAClientsTutorial/nodeCF/nodeQueryCF
      
    2. Initialize the project, install express, and @sap/hana-client from NPM.

      Shell
      Copy
      npm init -y
      npm install express
      npm install @sap/hana-client
      
    3. Open a file named server.js in an editor.

      Shell (Microsoft Windows)
      Copy
      notepad server.js
      
      Shell (Linux or Mac)
      Copy
      pico server.js
      
    4. Add the code below to server.js.

      JavaScript
      Copy
      var 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.

    5. Run and test the app locally.

      Shell
      Copy
      node server.js
      
      running locally
  • Step 3
    1. Create a deployment descriptor.

      Shell (Microsoft Windows)
      Copy
      cd ..
      notepad manifest.yml
      
      Shell (Linux or Mac)
      Copy
      cd ..
      pico manifest.yml
      

      Add the code below to manifest.yml.

      yml
      Copy
      ---
      applications:
      - name: nodeQueryCF
        random-route: true
        type: nodejs
        path: nodeQueryCF
        command: node server.js
        memory: 128M
      

      For additional details, consult App Manifest Attribute Reference.

    2. Deploy the app to Cloud Foundry or XS advanced.

      Shell
      Copy
      cf push
      
      push result

      Notice above the URL to open the app was generated as the manifest.yml contained the random-route setting.

      Shell
      Copy
      xs push
      

      .

      push result

      Alternatively, the URL of the app can be found by running the following command:

      Shell
      Copy
      cf app nodeQueryCF
      
      Shell
      Copy
      xs app nodeQueryCF
      
    3. Test the app.

      cf app

      .

      xs app

    For additional details see:

    Developing Node.js in the Cloud Foundry Environment

    Tutorial: Setting up your JavaScript Application in XS Advanced

  • Step 4
    1. The app can be stopped and started with the below commands:

      Shell
      Copy
      cf stop nodeQueryCF
      cf start nodeQueryCF
      
    2. The applications lifecycle events can be seen with the below command:

      Shell
      Copy
      cf events nodeQueryCF
      
      events
    3. The logs of the application can be seen with the below command:

      Shell
      Copy
      cf logs --recent nodeQueryCF
      

      The following command will show the tail of the log.

      Shell
      Copy
      cf logs nodeQueryCF
      
    4. As of version 2.7, the SAP HANA client interfaces can output trace information to stdout or stderr.

      Shell
      Copy
      cf 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.

      trace
    5. The deployed app can also be managed in the associated cockpit.

      SAP BTP Cockpit

      SAP BTP cockpit

      Details of the application nodeQueryCF

      SAP BTP cockpit

      SAP HANA XS Advanced Cockpit

      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.

    1. Follow step 3 at Access Remote Sources with SAP HANA Database Explorer to install and configure the Cloud Connector.

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

        bind service
      • Add the Connectivity service.

        connectivity service
      • Provide an instance name such as MyConnectivityService.

        connectivity service
      • Examine the values of the connectivity service. In particular, the indicated values below are used for the proxyPort and proxyHostname values in the server.js file and will be to access the proxy service which enables communication with the cloud connector.

        connectivity service
    3. Add the service name to the project’s manifest.yml.

      Shell (Microsoft Windows)
      Copy
      cd %HOMEPATH%\HANAClientsTutorial\nodeCF
      notepad manifest.yml
      
      Shell (Linux or Mac)
      Copy
      cd $HOME/HANAClientsTutorial/nodeCF
      pico manifest.yml
      
      yml
      Copy
        services:
        - MyConnectivityService
      
    4. 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)
      Copy
      cd nodeQueryCF
      copy server.js server.js.bak
      npm install axios
      
      Shell (Linux or Mac)
      Copy
      cd nodeQueryCF
      cp server.js server.js.bak
      npm install axios
      
    5. Open the file named server.js in an editor and replace its contents.

      Shell (Microsoft Windows)
      Copy
      notepad server.js
      
      Shell (Linux or Mac)
      Copy
      pico server.js
      
      JavaScript
      Copy
      var 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)
              })
      	})   
      }
      
      
    6. Redeploy the app.

      Shell
      Copy
      cd ..
      cf push
      
    7. The application running in the cloud, is now accessing data from an on-premise SAP HANA, express instance.

      Result

    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?

Back to top