SAP HANA XS Advanced - Database access from Node.js

In the previous tutorial, you added a handler for a path called /node by modifying the files myNode.js.

Add a new route for example1 to get the database connection/client from the express request object (req.db). Then create a prepared statement for the SELECT of SESSION_USER from dummy (dummy is the synonym created in the initial HDI tutorial). Execute the statement and send the results as JSON in the response object.

//Simple Database Select - In-line Callbacks
//Example1 handler
app.get("/example1", function(req, res) {
var client = req.db;
client.prepare(
	"select SESSION_USER from \"DUMMY\" ",
	function(err, statement) {
		if (err) {			
			res.type("text/plain").status(500).send("ERROR: " + err.toString());	return;	}
	statement.exec([],
		function(err, results) {
			if (err) {			
				res.type("text/plain").status(500).send("ERROR: " + err.toString());	return;						

		} else {							
			var result = JSON.stringify({ Objects: results});					
			res.type("application/json").status(200).send(result);
		}
		});
	});
});

As follows:

The default programming approach in Node.js is using callbacks/event handlers. This is because even the different parts of a database request (connection, prepared statement, execution, etc) are all non-blocking operations.

Hint: A callback is an asynchronous operation that gets executed after another one notifies completion.

Add a second route handler, called example2, that uses the async module:

var async = require("async");
	//Simple Database Select - Async Waterfall
	app.get("/example2", function(req, res) {
		var client = req.db;
		async.waterfall([

			function prepare(callback) {
				client.prepare("select SESSION_USER from \"DUMMY\" ",
					function(err, statement) {
						callback(null, err, statement);
					});
			},

			function execute(err, statement, callback) {
				statement.exec([], function(execErr, results) {
					callback(null, execErr, results);
				});
			},
			function response(err, results, callback) {
				if (err) {
					res.type("text/plain").status(500).send("ERROR: " + err.toString());
					return;
				} else {
					var result = JSON.stringify({
						Objects: results
					});
					res.type("application/json").status(200).send(result);
				}
				callback();
			}
		]);
	});

Take a look at both newly-added examples. You can easily notice that the second one is easier to read, although the runtime does not change. You can check for more information on the async module in the async documentation.

Look at the package.json file in the editor. You will see the dependencies section which lists all required libraries and their versions. Add the new modules which we referenced in the myNode.js file for this exercise part.

  	"async": "latest"

Now change the path in the browser to /node/dummy. You should see the output of your successful SELECT statement.

You might have noticed that the default Node.js programming approach is to use callbacks/event handlers for each operation. This is because even the different parts of a database request (connection, prepared statement, execution, etc) are all non-blocking operations. While this provides considerable parallelization and performance opportunities; it can also make the code more difficult to read. As a bonus part of this exercise you can return to myNode.js and add a second route handler, /dummy2 that performs the same select but uses the async module. This module doesn’t really change the runtime aspects of the code, but organizes the callback functions in an easier to read array instead of in-lining them within each other.

//Simple Database Select - Async Waterfall

app.route("/dummy2")
  .get(function(req, res){
  var client = req.db;
 async.waterfall([
 function prepare(callback){
    client.prepare("select SESSION_USER from \"dev602.data::DUMMY\" ",
  function(err,statement){callback(null, err, statement);});
  },

function execute(err, statement, callback){
   statement.exec([], function(execErr, results){callback(null,execErr,results);});
},
function response(err, results, callback){
 if(err){
 res.type("text/plain").status(500).send("ERROR: " + err);
}else{
 var result = JSON.stringify( { Objects: results });
 res.type("application/json").status(200).send(result);
}
callback();
} ]);
});