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
– Taplar, Commented Aug 12, 2020 at 18:38
please use only prepared statements with parameters see stackoverflow.com/questions/15778572/… to prevent sql injection — nbk
– nbk, Commented Aug 12, 2020 at 18:41

antonku · Accepted Answer · 2020-08-12 20:18:28Z

0

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,
    });
})

edited Aug 12, 2020 at 20:18

answered Aug 12, 2020 at 20:10

antonku

7,7252 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Griffin · Accepted Answer · 2020-08-12 21:20:44Z

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));
  }
}

Collectives™ on Stack Overflow

Using A While Loop With An SQL Request In NodeJS

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related