Using A While Loop With An SQL Request In NodeJS

Question

so I was trying to use a while loop to iterate through an array. I have succesfully gotten the sql connection to print a result using code without the while loop, so I know for a fact that the query works, it just doesn't work when in a while loop.

var sql = "SELECT algorithm FROM algorithms WHERE AlgorithmName = " + con.escape(PLLNames[0]);
    con.query(sql, function (err, result) {
      console.log("Sup")
      if (err) throw err;
      console.log("Result: " + result);
      x+=1;
      console.log(x)
    });
  res.render("PLL", {
    algorithmTest: result,
  });

Using something like this works fine, but when I edit this to have my loop, the code breaks. I know it's not performing the query because it's not logging the result and it's also not giving an error. For some reason the code doesn't even try to perform the query. This is what I tried to make work

 x = 0;
  while (x < 20) {
    var sql = "SELECT algorithm FROM algorithms WHERE AlgorithmName = " + con.escape(PLLNames[0]);
    con.query(sql, function (err, result) {
      console.log("Sup")
      if (err) throw err;
      console.log("Result: " + result);
      x+=1;
      console.log(x)
    });
  };
  res.render("PLL", {
    algorithmTest: result,
  });

Does anyone know why this might be happening?

The assumption would be that the `con.query` is asynchronous in nature, given a callback is provided. — Taplar, Aug 12 '20 at 18:38
please use only **prepared statements with parameters** see https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js to prevent **sql injection** — nbk, Aug 12 '20 at 18:41

antonku · Answer 1 · 2020-08-12T20:18:28.547

The issue happens because con.query works asynchronously, due to this res.render does not wait for sql queries scheduled by the while loop. You can solve this by using a recursive function instead of the while loop:

function next(counter, limit, callback) {
    if (counter >= limit) {
        callback()
        return;
    }
    var sql = "your SQL query";
    con.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Result: " + result);
        counter+=1;
        next(counter, limit, callback);
    });
}
next(0, 20, function() {
    res.render("PLL", {
        algorithmTest: result,
    });
})

Griffin · Answer 2 · 2020-08-12T21:20:44.457

The best solution is to have a stack where you keep what should be run. I think this example will help you get started and be easy to follow and understand.

const stack = [];
let x = 0;
const results = [];
// Fill the stack
while (x < 20) {
  // are you sure you don't want PLLNames[x] ?
  stack.push("SELECT algorithm FROM algorithms WHERE AlgorithmName = " + con.escape(PLLNames[0]));
  x++;
}

function worker() {
  if(stack.length > 0) {
    let sql = stack.shift();
    con.query(sql, function (err, result) {
      console.log("Sup")
      if (err) throw err;
      console.log("SQL: " + sql);
      console.log("Result: " + result);
      results.push(result);
      worker();
    });
  } else {
    // Not sure what you want to do with the results, but you got 20 of them so somehow you need to process it into one result.
    res.render(JSON.stringify(results));
  }
}

Using A While Loop With An SQL Request In NodeJS

2 Answers2