Cannot get return array from javascript function reading from sql database

Question

I have debuged through the code to check to make sure that I am getting data back from my sql connection within node.js. But I cannot get this function to return a the results. When looping through the rows it is adding the item to the array but which i commented the line where the value appears correct...Why is this function returning a blank array []?

function execute_stp_getconfigurationsbyuserid(userid, callback)
{
    var results = [];
    var Connection = require('tedious').Connection;
    var config = {
    };

    function item(id,name)
    {
        this.id = id;
        this.name = name;

        return this;
    }

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

    var connection = new Connection(config);

    var myCallback = function(err,data){
        connection.on('connect', function(err) {

            var request = new Request("stp_GetConfigurationsByUserId @UserId", function(err) {
                if (err) {
                    console.log(err);}
            });

            request.addParameter('UserId', TYPES.Int, userid);

            request.on('row', function(columns) {
                var itm = new item(columns[0].value, columns[2].value);
                results.push(itm);
                //HAS THE CORRECT VALUE HERE
                console.log(results);
            });

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

            connection.execSql(request);

        });
    }

}

Calling function by:

router.get('/getuserconfigurations', function(req, res, next) {
    var config_ddl = execute_stp_getconfigurationsbyuserid(1);
    console.log("Getting user configurations....")
    console.log(config_ddl);
    res.send(JSON.stringify(config_ddl));
});

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

Node works asynchronously.By the time return results is executed, above statements need not be completed. Try using callbacks to pass/act on data. Refer this link nodeJs callbacks simple example

Edit: The code can be modified to

var callback = function(err, data){
  console.log(data);
}

function execute_stp_getconfigurationsbyuserid(userid, callback)
{
    var results = [];
    var Connection = require('tedious').Connection;
    var config = {
    };

    function item(id,name)
    {
        this.id = id;
        this.name = name;

        return this;
    }

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

    var connection = new Connection(config);

 
  connection.on('connect', function(err) {

    var request = new Request("stp_GetConfigurationsByUserId @UserId", function(err) {
      if (err) {
        console.log(err);}
    });

    request.addParameter('UserId', TYPES.Int, userid);

    request.on('row', function(columns) {
      var itm = new item(columns[0].value, columns[2].value);
      results.push(itm);
      //HAS THE CORRECT VALUE HERE
      console.log(results);
    });

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

    connection.execSql(request);

  });
   

}

edited May 23 '17 at 11:45

Community

1
1

answered Mar 29 '16 at 16:44

Rajesh Veeranki

104
6

Can you show me how to do it with what I have it? – user3788671 Mar 29 '16 at 17:31
`var myCallback = function(err, data){ console.log(data); }` Call this myCallback in `request.on('done', function(rowCount, more) { console.log(rowCount + ' rows returned'); myCallback(null,results); });` – Rajesh Veeranki Mar 29 '16 at 17:34
still isn't working. – user3788671 Mar 29 '16 at 17:50
Can you edit the question with your modification done? I can then understand clearly. – Rajesh Veeranki Mar 29 '16 at 18:02
Yes, I can. I have updated the code. I am very new to this stuff in case you haven't realized already. – user3788671 Mar 29 '16 at 18:10
Edited my answer. See if it works – Rajesh Veeranki Mar 29 '16 at 18:36
I am still getting undefined – user3788671 Mar 29 '16 at 18:43
How are you calling this function? execute_stp_getconfigurationsbyuserid(userid, callback) – Rajesh Veeranki Mar 29 '16 at 18:48
I have added my calling code to the question. – user3788671 Mar 29 '16 at 18:50
I don't think the done function is even being called. I don't get that log message – user3788671 Mar 29 '16 at 18:52
Looks like an open bug. https://github.com/pekim/tedious/issues/61 BTW you should call function as `execute_stp_getconfigurationsbyuserid(1, callback);` – Rajesh Veeranki Mar 29 '16 at 18:59
alright, i changed that bit in the code but still no luck. Thanks for the help – user3788671 Mar 29 '16 at 19:03

Cannot get return array from javascript function reading from sql database

1 Answers1