Query works but cant retrieve the data

Question

I am new to Node.js (3 days total experience). I am using Node.js and the tedious package to query a database (azure SQL). I use the example as explained here: https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-nodejs?tabs=macos

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
    if (err) {
        console.error(err.message);
    } else {
        console.log("Reading rows from the Table...");

        // Read all rows from table
        const request = new Request(
            "SELECT * FROM clients",
            (err, rowCount, columns) => {
                if (err) {
                    console.error(err.message);
                } else {
                    console.log(`${rowCount} row(s) returned`);
                }
            }
        );

        request.on("row", columns => {
            columns.forEach(column => {
                console.log("%s\t%s", column.metadata.colName, column.value);
            });
        });

        connection.execSql(request);
    }
});

I have two issues:

1. I do not know how to get the queried data into an object and
2. If I run the script it does print the items to the console, but it doesn't close the connection after it has done so. If I add a connection.close() at the bottom, it will close the connection before its done. I get the feeling that node.js executes everything at the same time (I am used to Python..).

Update
I found a way to close the connection, to my understanding the request object has several "events" that are predefined by the library. It seems I need to add the event "done" through request.on('done', ...) in order to make sure that it can even BE done. My updated code looks like this:

var connection = new Connection(config);

connection.connect(function(err) {
        // If no error, then good to go...
        executeStatement();
    }
);

connection.on('debug', function(text) {
        //remove commenting below to get full debugging.
        //console.log(text);
    }
);

function executeStatement() {
    request = new Request("SELECT * FROM clients", function(err, rowCount) {
        if (err) {
            console.log(err);
        } else {
            console.log(rowCount + ' rows');
        }

        connection.close();
    });

    request.on('row', function(rows) {
        _.forEach(rows, function(value, collection){
            console.log(value)
            console.log(value.value);
            console.log(value.metadata.colName)
            console.log(collection)
        })
    });

    request.on('done', function(rowCount, more) {
        console.log(rowCount + ' rows returned');
    });

    // In SQL Server 2000 you may need: connection.execSqlBatch(request);
    connection.execSql(request);
}

Anyways, your help would be much appreciated!

Regards Pieter

Answer 1

The package tedious is synchronous package, it uses the callback to return results. So when we call connection.close(), it will disable connection and stop the callback function. If will want to close the connection, I suggest you use async package to implement it.

For example

const { Connection, Request } = require("tedious");
const async = require("async");
const config = {
  authentication: {
    options: {
      userName: "username", // update me
      password: "password", // update me
    },
    type: "default",
  },
  server: "your_server.database.windows.net", // update me
  options: {
    database: "your_database", //update me
    encrypt: true,
    validateBulkLoadParameters: true,
  },
};

const connection = new Connection(config);
let results=[]
function queryDatabase(callback) {
  console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request("SELECT * FROM Person", (err, rowCount) => {
    if (err) {
      callback(err);
    } else {
      console.log(`${rowCount} row(s) returned`);
      callback(null);
    }
  });
  
  request.on("row", (columns) => {
     let result={}
    columns.forEach((column) => {
     
      result[column.metadata.colName]=column.value
      console.log("%s\t%s", column.metadata.colName, column.value);
    });
    // save result into an array
     results.push(result)
  });

  connection.execSql(request);
}
function Complete(err, result) {
  if (err) {
    callback(err);
  } else {
    connection.close();
    console.log("close connection");
  }
}
connection.on("connect", function (err) {
  if (err) {
    console.log(err);
  } else {
    console.log("Connected");

    // Execute all functions in the array serially
    async.waterfall([queryDatabase], Complete);
  }
});
connection.connect();

Besides, you also can use the package mssql. It supports asynchronous methods and depends on package tedious. We can directly call close after querying.

For example

const mssql = require("mssql");
const config = {
  user: "username",
  password: "password",
  server: "your_server.database.windows.net",
  database: "your_database",
  options: {
    encrypt: true,
    enableArithAbort: true,
  },
};
let pool = new mssql.ConnectionPool(config);

async function query() {
  try {
    await pool.connect();
    const request = pool.request();
    const result = await request.query("SELECT * FROM Person");
    console.dir(result.recordset);

    await pool.close();
    console.log(pool.connected);
  } catch (error) {
    throw error;
  }
}

query().catch((err) => {
  throw err;
});

Answer 2

this article should help you, to solve all the issues you are facing...which were the same I had when I started using Node :)

https://devblogs.microsoft.com/azure-sql/promises-node-tedious-azure-sql-oh-my/

Answer 3

You can custom a class first and declare an Array to save ojects such as:

let sales = new Array();

class SalesLT{
    constructor(catagryName,productName){
        this.catagryName = catagryName;
        this.productName = productName;
    }

Here my sql statement returns 2 properties, so every time the loop takes out two elements from the ColumnValue[].

  request.on("row", columns => {
      for(let i=0; i<columns.length; i=i+2){
        let sale = new SalesLT(columns[i].value,columns[i+1].value);
        sales.push(sale);
      }
      
    sales.forEach( item => {
        console.log("%s\t%s",item.catagryName, item.productName)
    })
  });

The code is as follows:

const { Connection, Request } = require("tedious");

let sales = new Array();

class SalesLT{
    constructor(catagryName,productName){
        this.catagryName = catagryName;
        this.productName = productName;
    }
    
}
// Create connection to database
const config = {
  authentication: {
    options: {
      userName: "<***>", // update me
      password: "<***>" // update me
    },
    type: "default"
  },
  server: "<****>.database.windows.net", // update me
  options: {
    database: "<***>", //update me
    encrypt: true
  }
};

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on   ("connect", err => {
  if (err) {
    console.error(err.message);
  } else {
    queryDatabase();
  }
});

function queryDatabase() {
  console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request(
    `SELECT TOP 2 pc.Name as CategoryName,
                   p.name as ProductName
     FROM [SalesLT].[ProductCategory] pc
     JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log(`${rowCount} row(s) returned`);
      }
     connection.close();
    }
  );

  request.on("row", columns => {
      for(let i=0; i<columns.length; i=i+2){
        let sale = new SalesLT(columns[i].value,columns[i+1].value);
        sales.push(sale);
      }


    sales.forEach( item => {
        console.log("%s\t%s",item.catagryName, item.productName)
    })
  });

  connection.execSql(request);
}