Connect to Data Lake Relational Engine Using the Node.js Driver

Ensure you have Node.js installed and check its version. Enter the following command:

node -v  

If Node.js is installed, the currently installed version is returned, such as v16.15.1.

If Node.js is not installed, download the long-term support (LTS) version of Node.js from Download Node.js.

If an install for Node.js is not provided on Linux, you may choose to install it via a package manager. For more details, please navigate to this link.

During the installation, there is no need to install Chocolatey.

In addition to the Node.js driver covered in this tutorial which is @sap\iq-client, there is also the SQL Anywhere driver. The @sap\iq-client driver supports newer Node.js versions and includes a promise library.

1. Add the dependencies to the driver.

   Shell (Microsoft Windows)

   Copy

   cd %IQDIR17%\sdk\node
   npm install
   

   Shell (Linux)

   Copy

   cd $IQDIR17/sdk/node
   npm install
   

2. Create a folder named node and enter the newly created directory.

   Shell (Microsoft Windows)

   Copy

   mkdir %HOMEPATH%\DataLakeClientsTutorial\node
   cd %HOMEPATH%\DataLakeClientsTutorial\node
   

   Shell (Linux)

   Copy

   mkdir $HOME/DataLakeClientsTutorial/node
   cd $HOME/DataLakeClientsTutorial/node
   

3. Initialize the project and install the @sap\iq-client driver from the install folder.

   Shell (Microsoft Windows)

   Copy

   npm init -y
   npm install %IQDIR17%\sdk\node
   

   Shell (Linux)

   Copy

   npm init -y
   npm install $IQDIR17/sdk/node
   

4. The following command lists the Node.js modules that are now installed locally into the DataLakeClientsTutorial\node folder.

   Shell

   Copy

   npm list
   

   

1. Open a file named nodeQuery.js in an editor.

   Shell (Microsoft Windows)

   Copy

   notepad nodeQuery.js
   

   Substitute pico below for your preferred text editor.

   Shell (Linux or Mac)

   Copy

   pico nodeQuery.js
   

2. Add the code below to nodeQuery.js.

   JavaScript

   Copy

   'use strict';
   const { PerformanceObserver, performance } = require('perf_hooks');
   var t0;
   var util = require('util');
   var datalakeIQ = require('@sap/iq-client');
   
   var connOptions = {
       host: 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443',
       uID: 'USER1',
       pwd: 'Password1',
       enc: 'TLS{tls_type=rsa;direct=yes}',
   };
   
   //Synchronous example querying a table
   var connection = datalakeIQ.createConnection();
   connection.connect(connOptions);
   
   var sql = 'select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;';
   t0 = performance.now();
   var result = connection.exec(sql);
   console.log(util.inspect(result, { colors: false }));
   var t1 = performance.now();
   console.log("time in ms " +  (t1 - t0));
   connection.disconnect();
   

3. Run the app.

   Shell

   Copy

   node nodeQuery.js
   

   

   Note the above app makes use of some of the data lake Relational Engine client Node.js driver methods, such as connect, exec and disconnect.

   Two examples showing the drivers methods being used asynchronously are shown in the next two steps.

   To enable debug logging of the SAP IQ Node.js client, enter the following command and then rerun the app.

   Shell (Microsoft Windows)

   Copy

   set DEBUG=*
   node nodeQuery.js
   

   Shell (Linux or Mac)

   Copy

   export DEBUG=*
   node nodeQuery.js
   

   

   The value of the environment variable DEBUG can be seen and removed with the commands below.

   Shell (Microsoft Windows)

   Copy

   set DEBUG
   set DEBUG=
   set DEBUG
   

   Shell (Linux or Mac)

   Copy

   printenv | grep DEBUG
   unset DEBUG
   printenv | grep DEBUG
   

Asynchronous programming enables non-blocking code execution which is demonstrated in the below example.

1. Open a file named nodeQueryCallback.js in an editor.

   Shell (Microsoft Windows)

   Copy

   notepad nodeQueryCallback.js
   

   Substitute pico below for your preferred text editor.

   Shell (Linux or Mac)

   Copy

   pico nodeQueryCallback.js
   

2. Add the code below to nodeQueryCallback.js.

   JavaScript

   Copy

   'use strict';
   const { PerformanceObserver, performance } = require('perf_hooks');
   var t0;
   var util = require('util');
   var datalakeIQ = require('@sap/iq-client');
   
   var connOptions = {
       host: 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443',
       uID: 'USER1',
       pwd: 'Password1',
       enc: 'TLS{tls_type=rsa;direct=yes}',
   };
   
   //Asynchronous example calling a stored procedure with callbacks
   var connection = datalakeIQ.createConnection();
   
   connection.connect(connOptions, function(err) {
       if (err) {
           return console.error(err);
       }
       //Prepared statement example
       const statement = connection.prepare('CALL HOTEL.SHOW_RESERVATIONS(?,?)');
       const parameters = [11, '2020-12-24'];
       var results = statement.execQuery(parameters, function(err, results) {
           if (err) {
               return console.error(err);
           }
           processResults(results, function(err) {
               if (err) {
                   return console.error(err);
               }
               results.close(function(err) {
                   if (err) {
                       return console.error(err);
                   }
                   statement.drop(function(err) {
                       if (err) {
                           return console.error(err);
                       }
                       return connection.disconnect(function(err) {
                           if (err) {
                               return console.error(err);
                           }
                       });
                   });
               });
           });
       });
   });
   
   function processResults(results, cb) {
       results.next(function (err, hasValues) {
           if (err) {
               return console.error(err);
           }
           if (hasValues) {
               results.getValues(function (err, row) {
                   console.log(util.inspect(row, { colors: false }));
                   processResults(results, cb);
               });
           }
           else {
               return cb();
           }
       });
   }
   

3. Run the app.

   Shell

   Copy

   node nodeQueryCallback.js
   

   

   Notice that asynchronous method calls use callback functions.

The Node.js driver for the data lake Relational Engine client provides support for promises. The following example demonstrates this. Notice that there is less nesting of code then the previous example.

1. Open a file named nodeQueryPromise.js in an editor.

   Shell (Microsoft Windows)

   Copy

   notepad nodeQueryPromise.js
   

   Substitute pico below for your preferred text editor.

   Shell (Linux or Mac)

   Copy

   pico nodeQueryPromise.js
   

2. Add the code below to nodeQueryPromise.js.

   JavaScript

   Copy

   'use strict';
   const { PerformanceObserver, performance } = require('perf_hooks');
   var t0;
   var util = require('util');
   var datalakeIQ = require('@sap/iq-client');
   var PromiseModule = require('@sap/iq-client/extension/Promise.js');
   
   var connOptions = {
       //Specify the connection parameters
       host: 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443',
       uid: 'USER1',
       pwd: 'Password1',
       enc: 'TLS{tls_type=rsa;direct=yes}',
   };
   
   //Asynchronous example calling a stored procedure that uses the promise module
   var connection = datalakeIQ.createConnection();
   var statement;
   
   PromiseModule.connect(connection, connOptions)
       .then(() => {
            //Prepared statement example
            return PromiseModule.prepare(connection, 'CALL HOTEL.SHOW_RESERVATIONS(?,?)');
       })
       .then((stmt) => {
           statement = stmt;
           const parameters = [11, '2020-12-24'];
           return PromiseModule.executeQuery(stmt, parameters);
       })
       .then((results) => {
           return processResults(results);
       })
       .then((results) => {
           return PromiseModule.close(results);
       })
       .then(() => {
           PromiseModule.drop(statement);
       })
       .then(() => {
           PromiseModule.disconnect(connection);
       })
       .catch(err =>  {
           console.error(err);
       });
   
   function processResults(results) {
       return new Promise((resolve, reject) => {
       var done = false;
           PromiseModule.next(results)
               .then((hasValues) => {
                   if (hasValues) {
                       return PromiseModule.getValues(results);
                   }
                   else {
                       done = true;
                   }
               })
               .then((values) => {
                   if (done) {
                       resolve(results);
                   }
                   else {
                       console.log(util.inspect(values, { colors: false }));
                       return processResults(results);
                   }
               })
               .catch (err => {
                   reject(err);
               });
       })
   }    
   

3. Run the app.

   Shell

   Copy

   node nodeQueryPromise.js
   

   

   The above code makes use of the promise module. Additional details on promises can be found at Using Promises.

Visual Studio Code can run and debug a Node.js application. It is a lightweight but powerful source code editor which is available on Windows, macOS and Linux.

1. If required, download Visual Studio Code..

2. In Visual Studio Code, choose File | Add Folder to Workspace and then add the DataLakeClientsTutorial folder.

   

3. Open the file nodeQuery.js.

4. Place a breakpoint inside the connection.exec callback. Select Run | Start Debugging | Node.js.

   Notice that the debug view becomes active.

   Notice that the program stops running at the breakpoint that was set. Observe the variable values in the leftmost pane. Step through code.

   

Congratulations! You have created and debugged a Node.js application that connects to and queries an SAP data lake Relational Engine database.