SAP provides 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..
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.
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.
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.
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.
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.
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.
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.
If you want to get column information, you can use the getColumnInfo() method as shown below.
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);
HANA DB Articles
-
JSON_TABLE Function
In this article, we'll take a look at how to use the ++JSON_TABLE++ function to represent JSON data stored in a regular column as a relational table.
31 March 2023 - 2696 views -
Connect To HANA DB Using Rust
Learn how to connect and query a HANA database using Rust with the hdbconnect package.
21 February 2023 - 2757 views -
If Table Exists Function
In this article, I explain how to create a function to determine if a table exists in SAP HANA.
31 October 2019 - 8832 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 7978 views -
Adding Auto Numbers To Table Columns
This article explains how to create an auto generated column using IDENTITY.
14 March 2018 - 6253 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 7639 views -
Connect To HANA DB Using Java
This article describes how to connect to a HANA DB system using the ngdbc.jar Java driver.
03 November 2017 - 5331 views