Variables outside connection.query() donot get updated

Question

I have a login code which sends data (input by the user) to a mysql database , matches the password and returns a status based on the success or failure of the verification. For this I have a function defined. The status variable is declared inside the function and updated through a few if statements which verify the password. However, when the function is called, it always returns the default value of the status

This is the code:

var con = mysql.createConnection({
 host: "localhost",
 user: "XXXXXX",
 password: "XXXXXX",
 database: "XXXXXX"
});
con.connect(function(err){
    if(err) throw err;
});

function confirm(uname,pass){
    var query = "select * from clients where name='"+uname+"' ";
    var stat = 0;
        con.query(query,function(err,result){
            if(err) throw err;
            if(result.length<1){
                stat=4;
            }   
            var password = result[0].password;
            if(password === pass){
                console.log('verified');
                stat=1;
            }
            if(password!=pass){
                stat=2;

            }
        });
    return stat;
}

The problem is that the console.log('verified') gets executed when the condition is fulfilled. But when this function is called it always returns 0

How do I make it update the stat variable?

Please read [How do I return the response from an asynchronous call?](https://stackoverflow.com/q/14220321/4642212). — Sebastian Simon, Apr 11 '19 at 06:32

score 2 · Accepted Answer · edited Apr 19 '19 at 08:20

The query will be executed asynchronously. So if you execute the function, stat will be returned BEFORE the callback of con.query(...) is executed. Therefore stat is always 0.

function confirm(uname, pass){
    var query = "select * from clients where name='"+uname+"' ";
    var stat = 0;
        con.query(query, function(err, result){
            // This is executed delayed. return is already called.
            if(err) throw err;
            if(result.length<1){
                stat=4;
            }   
            var password = result[0].password;
            if(password === pass){
                console.log('verified');
                stat=1;
            }
            if(password != pass){
                stat=2;

            }
        });
    return stat;
}

To fix this problem, you must use a callback yourself:

function confirm(uname, pass, cb){
    var query = "select * from clients where name='"+uname+"' ";
    var stat = 0;
        con.query(query, function(err, result){
            if(err) return cb(err);
            if(result.length < 1){
                stat=4;
            }   
            var password = result[0].password;
            if(password === pass){
                console.log('verified');
                stat=1;
            }
            if(password != pass){
                stat=2;
            } 
            cb(null, stat);
        });
}

And call it like so:

confirm("<username>", "<pw>", function(error, stat) {
    if (error) 
    {
          // do error routine
    } else {
         // all fine, stat is set, no error thrown
    }
}

Sagi Rika · Answer 2 · 2019-04-11T06:34:47.570

That's because your return statement is outside of the callback. Try this:

var con = mysql.createConnection({
 host: "localhost",
 user: "XXXXXX",
 password: "XXXXXX",
 database: "XXXXXX"
});
con.connect(function(err){
    if(err) throw err;
});

function confirm(uname,pass){
  return new Promise((resolve, reject) => {
    var query = "select * from clients where name='"+uname+"' ";
    var stat = 0;
    con.query(query,function(err,result){
      if(err) throw err;
      if(result.length<1){
        stat=4;
      }   
      var password = result[0].password;
      if(password === pass){
        console.log('verified');
        stat=1;
      }
      if(password!=pass){
        stat=2;
      }
        resolve(stat);
    });   
  });
}

Then, when you call the function you need to use the Promise/Async syntax.

Promise syntax: confirm('yourusername', 'yourpassword').then(stat => {what to do with stat});

Async syntax: let stat = await confirm('yourusername', 'yourpassword');

Did not work, it returns ```undefined``` when the ```return``` statement is put inside the callback — RishiC, Apr 11 '19 at 06:29

Variables outside connection.query() donot get updated

2 Answers2