Querying SQL NodeJS + Tedious: request.on('done', cb) not being called and nested queries

Question

I am trying to make a request, add the returned data to an object on the cb in the "row" event on the request, and then do something in the "done" cb. Pretty simple; however, the "done" event is apparently not being triggered. Here is my current code:

var connection = new Connection(config);
var executeTest = function (connection) {
 var result = [];
 var statement = "select val1, val2 from table;"

 var request = new Request(statement, function (err, rowCount) {
   if (err) {
    console.log(err);
   }
   console.log(rowCount);
   connection.close();
 });

 request.on('row', function(columns) {
  var thisRow = {};
  columns.forEach(function(column) {
    thisRow[column.metadata.colName] = column.value;
    result.push(thisRow);     
  });
  console.log(result); //not empty
  //tried making request2 here, no dice
  //tried making a second connection here, no dice
  //got error:
  //'requests can only be made in the LoggedIn state, 
  //not the SentClientRequest state' :(
 })  

 request.on('done', function (rC, more, row) { //never called
   console.log(rC, more, row);
   //other things to do
 })
 connection.execSql(request);

}

function test () {
 var connection = new Connection(configure);
 connection.on('connect', function(err) {
   if (err) {console.log(err);}
   console.log("Connected");
   executeTest(connection);
 })
}

test();
console.log(result); // []
//make second request using values from result

Also if anyone could explain how to use the results from a query as parameters in another, that would be great. After I call test, obv result is empty, but when it isn't empty in the request.on('row', cb), I have not been able to make a second request in that cb with the current thisRow obj. Would I need to use transactions? If so would someone please give an example of how to do nested queries with transaction in tedious where the results of the first query are fed to the second query as parameters? Thanks.

Answer 1

To make the second request, you need to pass a callback into the function wrapping the tedious request. I dumbed down the code a bit for simplicity sake:

var statement = "select val1, val2 from table";

function executeTest(connection, callback) {

    var results = [];

    var request = new Request(statement, function(error) {

        if (error) {
            return callback(error);
        }
        // pass the results array on through the callback
        callback(null, results);
    });

    request.on("row", function(rowObject) {
        // populate the results array
        results.push(rowObject);
    });
    connection.execSql(request);
}

function test() {
    /*
    create connection code
     */
    executeTest(connection, function(error, results) {
        // here is the results array from the first query
        console.log(results);
    });
}

test();

The key point is that the request is asynchronous, so the results must be passed to a callback. That means if you want the code somewhere else, you need to pass a callback parameter into executeTest .