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.

Fluorinate

Is it feasible to move inner transacion out of the cycle through the results, so that the cycle of INSERTs goes inside the transaction completely? – Viper

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

Fillip

Thank you for your answer! I never thought of using recursive functions! – Fluorinate

thanks for the idea, but it seems that web-sql results doesn't support the shift() function, as it's not an array. So I had to increment a counter manually, and it worked ! – Bailly

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...

Phillada

You really ought to have declared your interest in SequelSphere. – Fillip

Good Point. You're absolutely right. Please forgive... I am connected to SequelSphere, and couldn't help giving the plug. – Phillada

p.s. async callbacks aren't cumbersome - see my answer for how to do this right! – Fillip

p.p.s. async is highly cumbersome, and highly necessary. My opinion only. I do like your answer very much, but if I'm reading it right, your answer will error out when no rows are returned from the select. – Phillada

Doh! ... touche ... and fixed. – Phillada