Javascript WebSQL query within for loop. How to know when finished?

Question

I guess I have a relative simple question, but I keep thinking in circles and even Google is not giving me an answer I can work with.

Basically I am trying to copy some records that are stored locally using WebSQL. Copying is not the problem, but I need to know when all copy actions are finished before my program can proceed.

WebSQL calls are made asychronous so the only way for me to usually do these things is by using the callback function. However, because the queries are done within a for loop, I can't use the callback function as it will fire at the first completed query as shown in the code.

The code is as follows:

function copyRecords(old_parent_id, new_parent_id, callback){
    var db = openDatabase('test', '1.0', 'test', 50 * 1024 * 1024);
    db.transaction(function (tx) {
        tx.executeSql('SELECT * FROM table WHERE parent_id = ?', [old_parent_id], function(tx, results){
            for(var i = 0; i < results.rows.length; i++){
                db.transaction(function (tx2) {
                    tx2.executeSql('INSERT INTO table (name, parent_id) VALUES (?, ?)', [results.rows.item(i).name, new_parent_id], callback);
                })
            }
        });
    });
}

I've also tried calling the callback function when i == results.rows.length, but that does not asure me that all queries are completed.

I guess some of you have encountered the same issue before, so any help on how to solve this and make sure that the callback function is only called when the for loop is complete is much appreciated.

Thank you in advance.

Answer 1

The usual approach for this would be to use a recursive async callback to process each individual record instead of a for loop.

While there are more records left, the async callback calls itself. When there are no records left, it can call your supplied callback.

The code below would replace the contents of your inner callback handler:

(function nextRecord() {
    var row = results.rows.shift();
    if (row) {
        db.transaction(function (tx2) {
            tx2.executeSql('INSERT INTO table (name, parent_id) VALUES (?, ?)',
                [row.item(i).name, new_parent_id], nextRecord);
       });
    } else {
        callback();
    }
})();

Answer 2

This is best done by keeping a count of the number of executes the "callback" function has, and only proceeding once it reaches the full amount of the result set.

Here's your code with the modifications:

function copyRecords(old_parent_id, new_parent_id, callback){
    var db = openDatabase('test', '1.0', 'test', 50 * 1024 * 1024);
    db.transaction(function (tx) {
        tx.executeSql('SELECT * FROM table WHERE parent_id = ?', [old_parent_id], function(tx, results){
            if (results.rows.length == 0) 
                callback(); // don't forget this case!
            else {
                var nbrInserted = 0; // This will keep track of how many have been inserted
                for(var i = 0; i < results.rows.length; i++){
                    db.transaction(function (tx2) {
                        tx2.executeSql('INSERT INTO table (name, parent_id) VALUES (?, ?)', [results.rows.item(i).name, new_parent_id], function() {
                            ++nbrInserted; // increment this for every insert
                            if (nbrInserted == results.rows.length) // check if complete
                                callback(); // Do your callback.
                        });
                    });
                }
            }
        });
    });
}

As for me, I found the async APIs of WebSQL to be a bit combersome, and since the WebSQL databases are likely to go away (the standard has been dropped), I would recommend that everyone switch over to SequelSphere. It is an HTML5 / JavaScript relational database that works across all browsers and all platforms. It also stores data in localStorage, giving it all the benefits of WebSQL, without all the hassles.

Let me know if the above solution doesn't work for you.

Good luck!

john...