CODEWITHSUNDEEP
javascriptmysqlnode.js
RRP
RRP

Reputation: 2853

Executing an query inside a for loop

I am executing an sql query inside a for loop, the function that is executing the query is an asynchronous operation. So I am wrapping the asynchronous operation in an immediate invoking function.

for(var k in bizValuesObj){
    if( existingCountries.hasOwnProperty(bizValuesObj[k]["country"]) ){
        country_id = existingCountries[bizValuesObj[k]["country"]];
    }

    var productName = bizValuesObj[k]["product"];
    var bizTypes = bizValuesObj[k]["bizTypes"];
    var getproductIdQuery = "SELECT `id` FROM `product` WHERE name = ? AND country_id = ?";

    (function(){
        connection.query(getproductIdQuery, [productName, country_id], function(err, result){
            if(err){
                console.error("Error getting product id:", err.stack);
            }
            product_id = result[0].id;
            for(var j= 0; j < bizTypes.length; j++){
                var temp = [];
                temp.push(bizTypes[j]);
                temp.push(country_id);
                temp.push(product_id);
                bizValues.push(temp);
            }
        });
    })(k);
}

At the moment, I am unsure why the bizValues doesn't get populated until the main loop has finished. I am trying to populate bizValues for every value of k.

Upvotes: 0

Views: 330

Answers (2)

marvel308
marvel308

Reputation: 10458

Your code is asynchronous, it will get populated but after some time. You should try using Promises to solve your problem. You can make an array of promise and then call each one of them parallely/serially. I would recommend using Promise.all(). But you would have to handle cases where a promise fails. refer to the code below

function getPromise(getproductIdQuery){
    return new Promise(function(resolve, reject){
            connection.query(getproductIdQuery, [productName, country_id], function(err, result){
                if(err){
                    console.error("Error getting product id:", err.stack);
                    reject(err);
                }
                product_id = result[0].id;
                for(var j= 0; j < bizTypes.length; j++){
                    var temp = [];
                    temp.push(bizTypes[j]);
                    temp.push(country_id);
                    temp.push(product_id);
                    bizValues.push(temp);
                }
                resolve(true);
            }
    })
}

let promiseArray = [];
for(var k in bizValuesObj){
    if( existingCountries.hasOwnProperty(bizValuesObj[k]["country"]) ){
        country_id = existingCountries[bizValuesObj[k]["country"]];
    }

    var productName = bizValuesObj[k]["product"];
    var bizTypes = bizValuesObj[k]["bizTypes"];
    var getproductIdQuery = "SELECT `id` FROM `product` WHERE name = ? AND country_id = ?";
    promiseArray.push(getPromise(getproductIdQuery));
} 

Promise.all(promiseArray).then(function(){
    // here you would find temp array populated
})

Upvotes: 0

Nitish Phanse
Nitish Phanse

Reputation: 562

You could try to alter this code and use promise.all docs

In short Promise.all takes an array of promises and executes the promise only when all the async data has been fetched. One advantage/disadvantage of Promise.all is the catch block is invoked the moment you hit an error in any single iterable

for(var k in bizValuesObj){
    if( existingCountries.hasOwnProperty(bizValuesObj[k]["country"]) ){
        country_id = existingCountries[bizValuesObj[k]["country"]];
    }

    var productName = bizValuesObj[k]["product"];
    var bizTypes = bizValuesObj[k]["bizTypes"];
    var getproductIdQuery = "SELECT `id` FROM `product` WHERE name = ? AND country_id = ?";
    // Async handling from here
    const promiseArray = []
    promiseArray.push(connection.query(getproductIdQuery, [productName, country_id])); // <- This function is a promise
} 

Promise.all(promiseArray).then((response) => {
    // your response after all async fetches
}).catch((error) => handle error)

Now in the success handler your can process your data further.

Upvotes: 1

Related Questions