Node.js Tedious Rows is an empty array but RowCount is correct

Question

So this is my basic chunk of code that I'm running. I've got RowCount coming back as expected (there's only 5 items right now) and but rows comes back as an empty array.

Am I doing something wrong? By the way I'm connecting to SQL azure. I don't have any connection problems and I do believe that I've put the correct options (rowCollectionOnRequestCompletion to true).

Any ideas?

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

exports.list = function(req, res){
    var connection = new Connection({
    "userName": "myCoolUsername",
    "password": "SoMePa$$word",
    "server": "something.database.windows.net",
    "options": {
        "database": "mySampleDbName",
        "encrypt": true,
        "rowCollectionOnDone": true,
        "rowCollectionOnRequestCompletion": true
    }
});

connection.on('connect', function(err){
    //if no error, then we are good to go.
    if(err){
        console.log(err);
    }else
    {
        var request = new Request("SELECT * FROM Products", function(err, rowCount, rows){
            console.log(rowCount);
            res.send(rows);
        })
        connection.execSql(request);
    }
});

marioosh · Accepted Answer · 2014-03-10T17:49:34.503

I had the same problem. Solved using rowCollectionOnDone: true option and doneInProc event on Request object like below. I don't know why callback function return empty array, when it should.

var config = {
    userName: '...',
    password: '...',
    server: 'localhost',
    options: {
        port: 2005,
        database: 'db1',
        rowCollectionOnDone: true
    }
}

connection.execSql(new Request('SELECT * FROM Products', function(err, rowCount, rows){
        if(err) {
            throw err;
        }
    })
    .on('doneInProc',function(rowCount, more, rows){
        console.log(rows); // not empty
    })
);

score 4 · Answer 2 · edited Jul 25 '18 at 22:31

4

Regarding the code:

  var request = new Request("SELECT * FROM Products", function(err, rowCount, rows) {
      console.log(rowCount);
      res.send(rows);
  })

The problem occurs because rows is undefined. This callback only receives 2 parameters:

  var request = new Request("SELECT * FROM Products", function(err, rowCount) {
      console.log(rowCount);
      res.send(rows);
  })

edited Jul 25 '18 at 22:31

Elletlar

3,136
7
32
38

answered Jul 25 '18 at 19:03

philip brodovsky

751
6
4

Hi Philip. Thanks for your answer. Please don't write cryptic messages like: "rows = undefined => this callback get only 2 parameters". I had to read that several times to understand what you are talking about. Please present your answers clearly in a way that busy professionals can understand them quickly. [Answering Tips](https://stackoverflow.com/help/how-to-answer) Cheers! – Elletlar Jul 25 '18 at 19:44

score 4 · Answer 3 · answered May 25 '19 at 05:16

I had to use this config to make it work

    var dbConn = await this.db.connect(); // Here add your connection code in connect() function
    const allRows = [];
    return await new Promise((resolve,reject) => {
       var SELECT_QUERY = 'SELECT * FROM your_table ';
       const request = new Request(SELECT_QUERY, function(err, rowCount) {
            if (err) {
                return reject(err);
            } else {
                console.log(rowCount + ' rows');
            }
        });

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

            columns.forEach(function(column) {
                const row = [];
                row.push({
                    metadata: column.metadata,
                    value: column.value,
                    toString: () => column.value
                });
                allRows.push(row);
            });
        });

        request.on('doneProc', function (rowCount, more, returnStatus, rows) {
            console.log('onDoneProc');

            console.log('all rows',allRows);

            return resolve(allRows);
        });

        dbConn.execSql(request);

    });

Actually this works perfect – jsanchezs Jan 04 '22 at 00:49 — jsanchezs, Jan 04 '22 at 00:49

score 2 · Answer 4 · answered Jan 31 '17 at 16:48

as per the Tedious API

    callback
    function (err, rowCount, rows) { }

rows Rows as a result of executing the SQL statement.

Will only be available if Connection's config.options.rowCollectionOnRequestCompletion is true.

I had to use this config to make it work

     var config = {
    server: "localhost",
    database: "*****",
    userName: "sa",
    password: "******",
    port: 1433,
    options:{rowCollectionOnRequestCompletion:true}
};

score 1 · Answer 5 · answered Dec 05 '20 at 06:56

1

set "rowCollectionOnRequestCompletion" is true

var config = {
    ...
    options : {
        rowCollectionOnRequestCompletion : true   // add this line
    }
}

answered Dec 05 '20 at 06:56

Beck Bian

11
1

score 0 · Answer 6 · answered Jun 27 '13 at 08:01

This is just a wild guess, but looking at the source, the rows get assigned an empty array when the procDone event is called. Perhaps, try setting rowCollectionOnDone to false? Also, there seems to be a row event emitted on the request. You could also subscribe to that and see if that gets you any output.

Node.js Tedious Rows is an empty array but RowCount is correct

6 Answers6