Nodejs not waiting for mysql response in ForLoop-CodePudding

I've a condition where I need to check weather the value exist in mysql or not if value exists then we need to update if not then we need to insert. here I'm using this condition but because of async task nodejs not waiting for response and when response come it's not checking it's just inserting data, please have a look


db_connection.js
check_by_column(tablename, selectedKeys, values, selectedValues, callback) {

    const show_key = [];
    for (const [key, value] of Object.entries(values)) {
      show_key.push(key " = " "'" value "'");
    }
    const column = show_key.join(" ");

    var sql = "SELECT "  selectedKeys  " FROM "  tablename  " WHERE "  column;
    console.log(sql)
    con.query(sql, function (err, result) {
      if (err) {
        console.log("database Error")
        callback(err);
        // throw err
      } else {
        if (result && result.length == 0 ) {
          callback(5);
        } else {
          callback(3);
        }
      }
      return;
  })
  }

insert_value(tablename, values, callback) {

    const show_key = [];
    const show_Values = [];
    for (const [key, value] of Object.entries(values)) {
      show_key.push(key);
      show_Values.push(value);
    }
    const nKey = show_key.join(", ");
    const nValue = show_Values.join("', '");

    let sql = "INSERT INTO "  tablename  " ("  nKey  ") VALUES ('"  nValue  "')";
    con.query(sql, function (err, result) {
      if (err) {
        console.log("database Error")
        console.log(err);
        throw err;
      }
      // console.log("1 record inserted");
      callback(result);
    });
  }

index.js

var dat = {"IMEI": 80, "rule": 2, "id": 1, "operation": 4, "address": 48, "qty": 6, "delay": 10, "data" : "3231,3039,3339", "error" : 0, "Timestamp":"2021-10-12 11:41:22"};
const topicData = JSON.stringify(dat);
var query = JSON.parse(topicData);
const currentDateTime = moment().format("YYYY-MM-DD HH:mm:ss");

const groups = [1, 2];
for (let i = 0; i < groups.length; i  ) {
    console.log(i)
    db.check_by_column('ml', 'number', { "number": query.data }, {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, function (response) {
        if (response === 5) {
            db.insert_value('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, function(response) {
                console.log("insert")
            });
        } else {
            db.update_by_column('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, {"number":query.data}, function(response) {
                console.log("update")
            });
        }
    });
}

response

0
SELECT number FROM ml WHERE number = '3231,3039,3339'
1
SELECT number FROM ml WHERE number = '3231,3039,3339'
insert
insert

CodePudding user response：

Your db calls are asynchronous and non-blocking. That means that they return immediately after calling them and the code after them continues executing (such as the rest of your for loop) and then the completion callback gets called some indeterminate time later after the entire for loop has completed.

So, asynchronous operations such as these inside a for loop end up running in parallel, not in sequence. The easiest way to run them in sequence (to make the for loop wait for an asynchronous operation to finish before advancing the for loop) is to use async and await. This then requires that your asynchronous operations be modified to return a promise that resolves or rejects rather than use a callback. Let's look at that in steps. First, we modify check_by_column() to return such a promise:

check_by_column(tablename, selectedKeys, values, selectedValues) {
    return new Promise((resolve, reject) => {
        const show_key = [];
        for (const [key, value] of Object.entries(values)) {
            show_key.push(key   " = "   "'"   value   "'");
        }
        const column = show_key.join(" ");

        var sql = "SELECT "   selectedKeys   " FROM "   tablename   " WHERE "   column;
        console.log(sql)
        con.query(sql, function(err, result) {
            if (err) {
                console.log("database Error")
                reject(err);
            } else {
                if (result && result.length == 0) {
                    resolve(5);
                } else {
                    resolve(3);
                }
            }
        })
    });
}

This is done by manually wrapping your asynchronous operation in a promise and capturing all the ways it can finish (both error and result) and hooking them to the promise. Note: if you switched to the msql2 version of the library, it has built-in support for promises and this manual wrapper wouldn't be required as you could use the built-in promise support. But, for purposes of explanation here I show how you manually construct the promise.

The, you do something similar for insert_value():

insert_value(tablename, values, callback) {
    return new Promise((resolve, reject) => {
        const show_key = [];
        const show_Values = [];
        for (const [key, value] of Object.entries(values)) {
            show_key.push(key);
            show_Values.push(value);
        }
        const nKey = show_key.join(", ");
        const nValue = show_Values.join("', '");

        let sql = "INSERT INTO "   tablename   " ("   nKey   ") VALUES ('"   nValue   "')";
        con.query(sql, function(err, result) {
            if (err) {
                console.log("database Error")
                console.log(err);
                reject(err);
                return;
            }
            // console.log("1 record inserted");
            resolve(result);
        });
    });
}

Now, that both of these functions are expressed using promises, we can then use async and await to restructure the for loop so that it actually suspends execution of the for loop for each database operation and thus runs your database operations in sequence instead of in parallel:

async function checkAndUpdate() {
    var dat = {"IMEI": 80, "rule": 2, "id": 1, "operation": 4, "address": 48, "qty": 6, "delay": 10, "data" : "3231,3039,3339", "error" : 0, "Timestamp":"2021-10-12 11:41:22"};
    const topicData = JSON.stringify(dat);
    var query = JSON.parse(topicData);
    const currentDateTime = moment().format("YYYY-MM-DD HH:mm:ss");

    const groups = [1, 2];
    for (let i = 0; i < groups.length; i  ) {
        console.log(i)
        let response = await db.check_by_column('ml', 'number', { "number": query.data }, {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime});
        if (response === 5) {
            let insertResult = await db.insert_value('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime});
            console.log("inserted")
        } else {
            let insertResult = await db.update_by_column('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, {"number":query.data});
            console.log("updated")
        }
    }
}

checkAndUpdate().then(result => {
    console.log("all done");
}).catch(err => {
    console.log(err);
});

One other comment, this code looks like it could be subject to race conditions because if some other code or even another request running this same code is also trying to insert this value, it may conflict with this code because you're checking for a value and then changing your behavior based on whether it already exists or not. I don't know your database well, but usually there is a single atomic operation that will insert or update depending upon whether the value already exists and you can let the database do that atomically.