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

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 CF CLI installed and verify the version.

   Shell Copy

   cf -v

   

   To install the CLI, see Installing the cf CLI. After installing, add the Cloud Foundry folder to your path environment variable.

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

   Shell Copy

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

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

   

   If you are an SAP employee, you may need to enter your password followed by a two-factor authentication passcode or if you have single sign-on enabled use the –sso option.

   The API URL, if requested, can be found in the Overview page in SAP BTP cockpit.

   

   The API URL can be set with the below command.

   Shell Copy

   cf api <API Endpoint>

   XS advanced

   Shell Copy

   xs login

   

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

   

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

   XS advanced

   Shell Copy

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

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

   Shell Copy

   mkdir %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF
   cd %HOMEPATH%\HANAClientsTutorial\nodeCF\nodeQueryCF

   Shell Copy

   mkdir -p $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 Copy

   notepad server.js

   Shell Copy

   pico server.js

4. Add the code below to server.js. Be sure to update the serverNode value (SQL Endpoint from your instance) and user credentials if necessary.

   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 HOTELS.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)
   })

   Show all 45 lines Show less

   Update the values for host and port.

5. Run and test the app locally.

   Shell Copy

   node server.js

   Open a browser and enter a URL similar to http://localhost:3000/Customers.

   

1. Create a deployment descriptor.

   Shell Copy

   cd ..
   notepad manifest.yml

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

   Before deploying the app to Cloud Foundry, ensure that you have the Cloud Foundry Runtime Environment entitlement enabled for your subaccount. You can find the Entitlements page in the left-hand side menu of SAP BTP Cockpit.

   

   If necessary, you can add the entitlement by clicking Edit. Don’t forget to save your changes when finished.

   

   After verifying that you have the necessary entitlements, run the following:

   Shell Copy

   cf push

   

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

   Shell Copy

   xs push

   

   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.

   

   XS advanced

   

For additional details see:

Developing Node.js in the Cloud Foundry Environment

Tutorial: Setting up your JavaScript Application in XS Advanced

1. The available buildpacks can be found using the command below.

   Shell Copy

   cf buildpacks

   If you use another buildpack such as .NET and encounter the error “Cannot find a matching libadonetHDB.so”, this can be corrected by adding the below entry to the .csproj file.

   Shell Copy

   <ItemGroup>
       <Content Include="./libadonetHDB.so">
           <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
       </Content>
   </ItemGroup>

2. The app can be stopped and started with the below commands:

   Shell Copy

   cf stop nodeQueryCF
   cf start nodeQueryCF

3. The applications lifecycle events can be seen with the below command:

   Shell Copy

   cf events nodeQueryCF

   

4. The logs of the application can be seen with the below command:

   Shell Copy

   cf logs nodeQueryCF --recent

   The following command will show the tail of the log.

   Shell Copy

   cf logs nodeQueryCF

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

   

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

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

     

   • Add the Connectivity service.

     

   • Provide an instance name such as MyConnectivityService.

     

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

     

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

   Shell Copy

   cd %HOMEPATH%\HANAClientsTutorial\nodeCF
   notepad manifest.yml

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

   cd nodeQueryCF
   copy server.js server.js.bak
   npm install axios

   Shell Copy

   cd nodeQueryCF
   cp server.js server.js.bak
   npm install axios

5. Open the file named server.js in an editor and replace the contents where necessary.

   Shell Copy

   notepad server.js

   Shell 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 HOTELS.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)
           })
       })   
   }

   Show all 73 lines Show less

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.

   

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.