Skip to Content

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

Create and debug a Node.js application that connects to data lake Relational Engine.
You will learn
  • How to install Node.js and the data lake Relational Engine Node.js driver
  • How to create and debug a Node.js application
  • How to use both the synchronous and asynchronous driver interfaces
danielvaDan van LeeuwenOctober 5, 2022
Created by
danielva
May 13, 2021
Contributors
danielva

Prerequisites

  • You have completed the first tutorial in this group.

Node.js provides a JavaScript runtime outside of the browser and uses an asynchronous event driven programming model. For more details, see Introduction to Node.js.

  • Step 1

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

    Shell
    Copy
    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.

    Chocolatey

  • Step 2

    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
      
      npm list
  • Step 3
    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 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
      
      Running 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
      
      debug output

      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
      
  • Step 4

    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
      
      Running nodeQueryCallback.js

      Notice that asynchronous method calls use callback functions.

  • Step 5

    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
      
      Running nodeQueryPromise.js

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

  • Step 6

    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.

      Workspace
    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.

      VS Code Debugging

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

    Which of the following statements are true?

Back to top