Cordova-sqlite-storage: Execute sql in for loop

[TommyQu]

Hi,

My name is Haotian Qu and I'm using your Cordova-sqlite-storage plugin. I meet the following problem:

I'm trying to read some data from database and then loop the results, and execute another query based on the results. But the for loop doesn't wait for my inside execution to be completed, it directly move to the second loop. The code is listed below:

tx.executeSql("select inspection_seq, station_seq from inspection_tran where trip_id = ?;", [tripId], function(tx, res) {
for(var i = 0; i < res.rows.length; i++) {
alert("Row index:"+i);
var stationIndex = i;
tx.executeSql("select * from station where station_seq = ?;", [currentStationSeq], function(tx, resStation) {
alert(stationIndex);
});
}
});

When i = 1, it will alert "Row index:0", "Row index:1" and then "Station index:1", "Station index:1". But I want it to show messages like "Row index:0", "Station index:0" and then "Row index:1", "Station index:1".

Is there a way I can solve this problem?

Thank you!

[brodybits]

I cannot see what could cause the problem with the code you posted here. I would need a complete program including how you populated the tables to try it out.

In addition, I think it would be more efficient to use a single SELECT query with JOIN.

[TommyQu]

test.zip

Hi brodybits,

I write a simple test project with mytable_one and mytable_two.

tx.executeSql("select * from mytable_one;", [], function(tx,res) {
    for(var i=0; i<res.rows.length;i++) {
        alert("i:"+i);
        var index = i;
        tx.executeSql("select * from mytable_two;", [], function(tx,res) {
            alert("index:"+index);
        });
    }
}, function(e){
    alert("ERROR: " + e.message);
});

I expect it to give alerts like this: i:0, index:0, then i:1, index:1.

However, it gives me i:0, i:1, then index:1, index1;

[brodybits]

I already marked this as a bug and hope to look at it next week. PLEASE remind me in case I forget thanks!

[TommyQu]

Sure, thank you!

Best,
Haotian Qu

On Fri, Mar 25, 2016 at 12:50 PM, Chris Brody [email protected]
wrote:

I already marked this as a bug and hope to look at it next week. PLEASE
remind me in case I forget thanks!

—
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub
https://github.com/litehelpers/Cordova-sqlite-storage/issues/445#issuecomment-201360282

[brodybits]

Since tx.executeSql is always asynchronous, you will get the alerts for i:0 and i:1 before you get the index alerts.

Also, the index variable is bound to the function scope, not the for loop scope. I can think of two ways to solve this:

• wrap the code in the body of the for loop in an IIFE:

                    for(var i=0; i<res.rows.length;i++) {
                      (function() {
                        alert("i:"+i);
                        var index = i;
                        tx.executeSql("select * from mytable_two;", [], function(tx,res) {
                            alert("index:"+index);
                        });
                      })();
                    }

• Use ES6 let or const

For more some information: http://stackoverflow.com/questions/750486/javascript-closure-inside-loops-simple-practical-example

As I said before, you should be able to use JOIN to get the data in a single SELECT statement. This would be much more efficient.

[TommyQu]

Hi Chris,

Thank you very much! I have solved my problem by using the first approach.

Actually, my project has more complex tables and should change some HTML
tag based on each loop execution. Even with JOIN, I still need two loop to
query from database.

Thanks again for your help!

Best,
Haotian Qu

On Thu, Mar 31, 2016 at 5:23 AM, Chris Brody [email protected]
wrote:

Since tx.executeSql is always asynchronous, you will get the alerts for
i:0 and i:1 before you get the index alerts.

Also, the index variable is bound to the function scope, not the for loop
scope. I can think of two ways to solve this:

• wrap the code in the body of the for loop in an IIFE:

for(var i=0; i<res.rows.length;i++) { (function() { alert("i:"+i); var index = i; tx.executeSql("select * from mytable_two;", [], function(tx,res) { alert("index:"+index); }); })(); }

• Use ES6 let or const

For more some information:
http://stackoverflow.com/questions/750486/javascript-closure-inside-loops-simple-practical-example

As I said before, you should be able to use JOIN to get the data in a
single SELECT statement. This would be much more efficient.

—
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub
https://github.com/litehelpers/Cordova-sqlite-storage/issues/445#issuecomment-203843338

[ajay92x]

I have also faced the similar issue and wrapping the code does not help, I think because of nested loop.

for loop does not even wait for the db.executeSql to complete.
Following code which I'm using and it always execute the INSERT query even though the same id exist. Not even once it execute the UPDATE command.

for (let gparent = 0; gparent < array.length; gparent++) {
for (let parent = 0; parent < array[gparent].innerArray.length; parent++) {
for (let child of array[gparent].innerArray[parent]) {
db.executeSql('SELECT * FROM tableName WHERE id = ?', [child.id]).then(re => {
if (re.rows.length > 0) {
for (let i = 0; i < re.rows.length; i++) {
console.error(JSON.stringify(re.rows.item(i)));
}
//data update section
db.executeSql('UPDATE tableName SET name = ? WHERE id = ?', [child.name, child.id])
.then((re2) => {
console.log('Updated to local database', re2);
})
.catch(e => console.log(e));
} else {
//data insert section
db.executeSql('INSERT INTO tableName(id, name) VALUES(?, ?)', [child.id, child.name])
.then((re2) => console.log('Inserted in Table', re2))
.catch(e => console.log(e));
}
}).catch(e => console.log(e));
}

}

}

Is there any way it can be solved?

Thank you!