undefined value returning while executing sql query in nodejs

Question

I am fetching values from my database having tables:

My code:

function listshops(callback)
{   
 client.connection.query('select * from shop',function(err,rows){
   if(rows.length>0)
   {  
     for(var i=0;i<rows.length;i++)
     {   
       var shopIdFetched = rows[i].shopId;
        client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
           if(data.length > 0){
            console.log(rows[i],data);
            }

        });

     }

   }

 });
}

But when displaying result, the first query shows a undefined value.

When i gives rows [0] and rows1 the values are fetching. But i need to implement rows[i].

Just by looking at it, seems to be a asynch problem, can you do a console.log, and check what the i value is? — AJ_, Jun 08 '16 at 13:14

libik · Accepted Answer · 2016-06-08T13:57:06.643

You misunderstand how asynchronous calls are make.

What happens when you run this part of code?

     for(var i=0;i<rows.length;i++)
     {   
       var shopIdFetched = rows[i].shopId;
       client.connection.query(...) //these are asynchronous methods    
     }

For rows.length=10, it will call 10 times the client.connection.query, which is unfortunately asynchronous method, therefore it is not executed yet, but it put 10 asynchronous methods to Event Stack.

After this method synchronously finishes and one of method indicates, the call to database is finished, the method is executed, which is this

        if(data.length > 0){
             console.log(rows[i],data);
        }

However at this point, the for-cycle already finished, the i=10, therefore rows[10] is undefined (because for rows.length=10 you have data in rows[0] to rows[9]

One workaround can be to put another method to the inner scope, something like this

for(var i=0;i<10;i++)
{
    x(i);
}

function x(i){
    console.log(i);
    //this i will be same even after asynchronous paradighm
}

The same thing can be written as this

for (var i = 0; i < 10; i++) {
    (function(i){
        console.log(i);
    })(i)
}

In your case

 for(var i=0;i<rows.length;i++)
 {   
   (function(i){
   var shopIdFetched = rows[i].shopId;
    client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
       if(data.length > 0){
        console.log(rows[i],data);
        }

    });
    })(i);
 }

For better understanding, this would do the same

 for(var index=0;index<rows.length;index++)
 {   
   (function(i){
   var shopIdFetched = rows[i].shopId;
    client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
       if(data.length > 0){
        console.log(rows[i],data);
        }

    });
    })(index);
 }

In previous example, we just shadowed variable i with different variable i (if more variables with same name are created, the one which is in the most inner scope will be selected)

i understood whats the problem. My question is how to solve this. — midhun k, Jun 08 '16 at 13:21

score 1 · Answer 2 · answered Jun 08 '16 at 13:24

You can not rely on i in async callbacks, because it was changed at time handler called.

You should to create some scope to save iteration data (i or row).

With Array.prototype.forEach:

rows.forEach(row => {
  var shopIdFetched = row.shopId;
  client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
    if(data.length > 0){
      console.log(row,data);
    }
  });
});

With IIFE:

for (var i=0; i<rows.length; i++) {
  !function(i) {
    // here you can use `i`/`rows[i]` without initial issue
  }(i);
}

undefined value returning while executing sql query in nodejs

2 Answers2