CODEWITHSUNDEEP
mysqlnode.js
rickster26ter1
rickster26ter1

Reputation: 433

multiple dependent mysql queries in nodejs...promise not the answer?

I have recently learned how to execute promises in order to run three mysql requests synchronously. I'm disappointed, because I'm learning this is not the solution if I want to run the dependent mysql requests in order. For example, the first gets a variable, which is needed for the second:

My nodejs code:

       data.used_time = 7;
       var sql;
       var sesh_save_val;
       var mysql = require('mysql');
          //Delete whiteboard session
          var con = mysql.createPool({
            connectionLimit: 15,
           host: "xxxx",
           user: "xx",
           password: "xxxx",
           database: "xxxx"
          }); 
          var current_min;
        new Promise ((resolve,reject) => {
            sql = `SELECT minutes FROM Students WHERE sesh_save = (?)`;
            con.query(sql, id, function (err, result)
            {
             if (err) throw err;
             current_min = result[0].minutes;
              console.log("no error thrown I think..");
              console.log("current_min: " + current_min);
            });
            resolve("did first func");
          }).then((val)=> {
              console.log(val);

              sql = 'UPDATE Students SET minutes = (?) WHERE sesh_save = (?)';
              var min_left = current_min - data.used_time;
              console.log("used time: " + data.used_time + " ");
              console.log("min_left: " + min_left + " " + "id: " + id);
               con.query(sql,[min_left, id], function (err, result)
               {
                if (err) {console.log(err);}
               });
               return "did second func";
});

The output is only partially what I would expect. It is:

"did first func", "used_time: 0", min_left: NaN, id: whiteboard-xxxxx-xxxx
"did second func"
"current_min: 4299"

And then error on the line with "con.query(sql,[min_left, id], function (err, result)" because min_left did not exist. Why did min_left not exist? Output showed that current_min existed. However, it existed AFTER the first mysql query resolved, which took so long, that we were able to get into the second .then statement while it's still taking it's time with the first sql query, and the second dependent mysql statement wasn't able to process correctly. How do I fix this?

Thanks for any remarks.

Upvotes: 0

Views: 132

Answers (1)

Kosh
Kosh

Reputation: 18434

You don't really need 2 queries. You will be good to go with:

sql = 'UPDATE Students SET minutes = minutes - (?) WHERE sesh_save = (?)';
con.query(sql,[data.used_time, id], function (err, result)...

Upvotes: 1

Related Questions