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.

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? — Stan
– Stan, Commented Oct 16, 2012 at 12:24

Alnitak · Accepted Answer · 2012-10-17 18:00:07Z

8

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

edited Oct 17, 2012 at 18:00

answered Oct 17, 2012 at 15:38

Alnitak

341k72 gold badges420 silver badges503 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1749815 Over a year ago

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

Matthieu Over a year ago

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 !

John Fowler · Accepted Answer · 2012-10-17 16:29:32Z

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

edited Oct 17, 2012 at 16:29

answered Oct 17, 2012 at 15:27

John Fowler

3,3033 gold badges21 silver badges32 bronze badges

5 Comments

Alnitak Over a year ago

You really ought to have declared your interest in SequelSphere.

John Fowler Over a year ago

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

Alnitak Over a year ago

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

John Fowler Over a year ago

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.

John Fowler Over a year ago

Doh! ... touche ... and fixed.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related