Binding Node.js Application to HANA Databases

Introduction

In this blogs, I will explain about the way of accessing HANA tables from Node.js Application.
Especially, I will explain about issuing certification on SCP and implimenting conn_param in app.js

IT Architechture

I will show IT Architecture outline below.
Your Node.js application connect to HANA tables on your Laptop PC.
You deploy your application on Cloud Foundry environment. The application connect to HANA tables.

Topic/table of Content

1. Preparation
2. HANA table Creation
3. Node.js Application Creation
4. Certification
5. app.js implimentation
6. HANA and Node.js Application Linkage

Environment

You need to prepare Cloud Foundry environments and HANA as a Service.
Cloud Environment ：Cloud Foundry enviroment.
Database ：HANA as a Service(CF)
PC/Service ：1 PC （※ In my case, I used WindowsPC）

Version

WindowsPC Edition ：Windows 10 Enterprise 2016 LTSB
CPU ：Intel(R)Core(TM)i5-7300U CPU @ 2.60GHz 2.71GHz
Memory(RAM) ：8.00GB
HANA version(CF) ：4.10.5

1. Preparation

Perhaps, you think about Developing tool to impliment any code.
So, you download Develoning tool such as IntelliJ/Visual Studio Code/WebStorm when necessary.

2. HANA table Creation

I will tell you HANA table creation method.

1. Select your Spaces, after you logged in Cloud Foundry envirinment
2. Select a card of ‘SAPA HANA Service’ in Service Marketplace and select your instance
3. Open your HANA Dashborad
(※ Remember HANA endopint)
4. Click ‘Go to the HANA cockpit’ in the upper right corner of window
Click ‘SQL console’ in the upper right corner of window
You can create any HANA schema and tables
(※ Remember your schema, user and password)

3. Node.js Application Creation

At first, you create simply Node.js application. Any kind of application is fine.
Open your project that you installed Developing tool and also select app.js in the src folder.
Implement connection setting to HANA tables in the node.js. I put a sample code below.

var conn_params = {
  serverNode: "zeus.hana.prod.eu-central-1.whitney.dbaas.ondemand.com:[Your Port Number]",
  encrypt: true,
  schema: "[Your Schema]",
  uid: "[Your User]",
  pwd: "[Your Password]"
};

Next step, you impliment SQL statement in the same file.
Define a method to connect to HANA tables.
I will show a sample code below.

app.use("/conn/xxx", function (req, res, next) {
  conn.connect(conn_params, function (err) {
    if (err) {
      var msg = [{
        msg: "A connection error has occurred"
      }];
      res.json({
        searchResult: msg
      });
      return;
    }
    var sql = 'SELECT * FROM "[Your Schema]"."[Your Table]";';
    conn.exec(sql, function (err, result) {
      if (err) {
        console.log("DB Error: SQL Execution --- ", err);
      }
      conn.disconnect();
      if (Object.keys(result).length == 0) {
        var msg = [{
          msg: "The result is not found."
        }];
        res.json({
          searchResult: msg
        });
        return;
      }
      res.json({
        searchResult: result
      });
    });
  });
});

After Implimentation, you run your Node.js application.
Your sccessfully connect to your HANA Tables.s

4. Certification Creation

You back to SAP Cloud Platfrom Cockpit to generate HANA servuce keys.
You follow steps below.

1. Select Instance Service
2. Select Your HANA Instance
3. Select Service Keys
4. Create Service key

You remember the keys as you use.

5. app.js implimentation

You add two parameters related to ssl on your source codes
Put a ‘sslCryptoProvider’ key and ‘openssl’ value
Put a ‘sslTrustStore’ key and a ‘BEGIN CERTIFICATE’ value as you memorize

var conn_params = {
  serverNode: "zeus.hana.prod.eu-central-1.whitney.dbaas.ondemand.com:[Your Port Number]",
  sslCryptoProvider: "openssl",
  sslTrustStore: "-----BEGIN CERTIFICATE-----\n...(omitted)...\n-----END CERTIFICATE-----\n",
  encrypt: true,
  schema: "[Your Schema]",
  uid: "[Your User]",
  pwd: "[Your Password]"
};

6. HANA and Node.js Application Linkage

After you build source codes and deploy them, you can see your application on SAP Cloud Platfrom Cockpit.

I will show you an instruction step by step.

1. Open windows command prompt
2. Change current directory to your application context root
3. Type ‘cf login -a https://api.cf.eu10.hana.ondemand.com’
4. Tyope your e-mail and password
5. Select Org
6. Type command ‘cf push [Your application name]’
7. Open browser
8. Access ‘https://[Your Application Name].cfapps.eu10.hana.ondemand.com/conn/xxx’

You can see table records.