Connect To HANA DB Using NodeJs

SAP provide a NodeJs client library to connect to a HANA system. The client library is available as part of the SAP HANA HDB client software, which can be downloaded when installing HANA Express or by installing from the SAP npm repository.

Install From SAP Repository


npm config set @sap:registry https://npm.sap.com
npm install -g @sap/hana-client

Connecting To HANA


You can connect to a HANA system either synchronously or asynchronously using callback functions. The following code in listing 1 below shows how to connect synchronously.

Listing 1

const hana = require("@sap/hana-client");

const conn = hana.createConnection();

conn.connect("serverNode=SERVER:30015;uid=USER;pwd=PASSWORD");

const results = conn.exec("SELECT NOW() FROM DUMMY");

console.log(results);

The connect() method accepts either a connection string or an object. Once a connection has been established, you can execute queries using the exec() method as shown above. The exec() method will return an array of JSON objects for successful SQL queries. For unsuccessful queries an error is thrown.

Connecting To HANA Asynchronously


The code above can be modified to connect to HANA asynchronously using callback functions. Both the connect and exec methods accept a callback function that is called when the method completes. Listing 2 below shows the above code rewritten to execute asynchronously.

Listing 2

...
conn.connect(config, err => {
    if(err){
        console.log(err);
    }else{
        conn.exec("SELECT NOW() FROM DUMMY"(err, results) => {
            if(err){
                console.log(err);
            }else{
                console.log(results);
            }
        });
    }
});

Both callback functions take an error argument, which you can use to log any errors that might occur. The exec() method can take up to three arguments. The first is the SQL statement, the second is an optional array of parameter bindings and the third is a callback function. Listing 3 below shows how to use parameter binding using the exec() method.

Listing 3

conn.connect(config, err => {
    if(err){
        console.log(err);
    }else{
        conn.exec("SELECT NOW() FROM DUMMY WHERE CURRENT_TIMESTAMP > ?"["2019-10-28 15:12:42"](err, results) => {
            if(err){
                console.log(err);
            }else{
                console.log(results);
            }
        });
    }
});

Prepared Statements


You can use prepared statements using the prepare() method of the connection object. If you are programming synchronously, the prepare() method will return a Statement object, which you can then use to execute the SQL statement. When programming asynchronously, the Statement object is passed as the second argument of the callback function as shown in listing 4 below.

Listing 4

conn.connect(config, err => {
    if(err){
        console.log(err);
    }else{
        conn.prepare("SELECT NOW() FROM DUMMY WHERE CURRENT_TIMESTAMP > ?"(err, statement) => {
            if(err){
                console.log(err);
            }else{
                statement.exec(["2019-10-28 15:12:42"](err, results) => {
                    if(err){
                        console.log(err);
                    }else{
                        console.log(results);
                    }
                });
            }
        });
    }
});

Notice in both code samples above, the queries use the ? placeholder. For each placeholder, there must be a corresponding item in the array supplied to the exec() method. Named place holders are not allowed.

ResultSet


The exec() method returns an array of objects as you would expect but in some cases you may need column information such as the column data type and precision. If this is the case you should use the execQuery() method, which will return a ResultSet object. One thing to note about a ResultSet is that it can only be used to read a forward-only stream of rows. The ResultSet has an internal cursor that increments every time the next() method is called and returns a boolean value indicating if the ResultSet has more rows. The following code sample below shows how to iterate over the ResultSet and get the data for each row by calling the getValues() method.

Listing 5

...
let stm = conn.prepare('SELECT * FROM [TABLE NAME]');

let resultSet = stm.execQuery();

while(resultSet.next()){
    console.log(resultSet.getValues());
}

If you want to get column information, you can use the getColumnInfo() method as shown below.

Listing 6

let info = resultSet.getColumnInfo();

console.log(info);