At what point do I return the data from this query?

Question

I have this method for querying a database with an event on row. How do I know when all rows have been iterated through?

I'm not sure when you return when all rows have been processed. The below code seems to cause issues.

 getData : function(data){
    var connection = new Connection(config);
    var newdata = [];
    var dataset = [];
    connection.on('connect', function(err) {

        var sql = "SELECT * FROM dbo."+data.entity+" WHERE "+data.field+" LIKE '%"+data.params+"%'";

        var Request = require('tedious').Request;
        var request = new Request(sql, function (err, rowCount) {
            if (err) {
                return false;
            } else {
                if (rowCount < 1) {
                    return false;
                }
            }
        });

        request.on('row', function(columns) {

            columns.forEach(function(column) {
                   dataset.push({
                       col: column.metadata.colName,
                       val: column.value
                   });


            });

            newdata.push(dataset);

        });

        request.on('done', function(){
            //connection.close();
            return newdata;
        })

        connection.execSql(request);

    });

},

Any help is appreciated!

Answer 1

You can't just return from asynchronous code, you need to use a callback. Pass a callback parameter into your getData function and then call it in Requests callback.

Also, according to the docs, you shouldn't be listening to the done event; you should just use Request's callback. Relevant bit:

This is a relatively low-level event, driven by the receipt of a TDS Done token. Most uses of Tedious can ignore this event, and should rely on the Request's callback function to know when the request has completed.

With that in mind, you'll want to do something like this:

getData : function(data, callback){
    var connection = new Connection(config);
    var newdata = [];
    var dataset = [];
    connection.on('connect', function(err) {

        var sql = "SELECT * FROM dbo."+data.entity+" WHERE "+data.field+" LIKE '%"+data.params+"%'";

        var Request = require('tedious').Request;
        var request = new Request(sql, function (err, rowCount) {
            if (err) {
                callback(err);
            } else {
                if (rowCount < 1) {
                    callback(null, false);
                } else {
                    callback(null, newdata);
                }
            }
        });

        request.on('row', function(columns) {

            columns.forEach(function(column) {
                   dataset.push({
                       col: column.metadata.colName,
                       val: column.value
                   });


            });

            newdata.push(dataset);

        });

        connection.execSql(request);

    });

}

Then to call it, you'd do:

getData(data, function(err, rows) {
    if (err) {
        // Handle the error
    } else if (rows) {
        // Process the rows returned from the database
    } else {
        // No rows returns; handle appropriately
    }
});