Using jQuery deferred and done on asynchronous SQLite database queries in a loop

Question

I am making an app where an insert query is placed inside a loop:

db.transaction(function(ctx) {
    ctx.executeSql("DELETE from table",  [], function(x,y){
        $.each(result, function(i, val) {                       
            db.transaction(function(ctx) {
                ctx.executeSql("INSERT INTO table(value1, value2) VALUES('"+val.value1+"','"+val.value2+"')", []);
            }, function(err){
                alert("Error processing SQL: "+err.message);
            },function(){
                console.log("finished one loop of insert"); 
            });
        });     
    });

}, function(){
    //error
}, function(){
    //success
    console.log("finished syncing");
    //this runs before all the inserts as the inserts are seperate queries  
});

But I cant seem to figure out how to run a function or alert or something when all of the INSERTS in the $.each() loop are finished. I have an idea I could use jQuery's deferred/done/promise but can't apply it to this problem.

Any ideas?

score 2 · Accepted Answer · edited May 23 '17 at 11:49

I tried to find a duplicate and to my surprise I didn't, so here goes:

You'd generally use $.when in order to aggregate multiple promises, if it's a dynamic number you could use $.when.apply which takes an array. Generally - this looks something like:

var promises = [p1, p2, p3, ...];
$.when.apply($, promises).then(function(){
      // all done,
      arguments; // contains all the results in an array-like
});

In your example - this would look like this:

var promises = [];
var transactionDone = $.Deferred(); // represent the transaction
promises.push(transactionDone);
db.transaction(function(ctx) {
    ctx.executeSql("DELETE from table",  [], function(x,y){
        $.each(result, function(i, val) {
            var d = $.Deferred();
            db.transaction(function(ctx) {
                ctx.executeSql("INSERT INTO table(value1, value2) VALUES('"+val.value1+"','"+val.value2+"')", []);
            }, d.reject, d.resolve); // resolve/reject when done/fail
            promises.push(d); // keep track of it.
        });
    });
}, transactionDone.reject, transactionDone.resolve);


$.when.apply($, promises).then(function(){
      // transaction itself is done and all inserts are complete
});

This should give you a general idea of how to proceed but personally I'd promisify at a lower level. Please refer to this answer on more details on how to do so.

Sir, you are a genius! I have been trying to do this for about a year and never been able to. Thanks!! — JamesG, Nov 06 '14 at 12:19

score 1 · Answer 2 · answered Nov 06 '14 at 22:13

A slightly tidier solution is available - one in which outer vars are avoided by using jQuery's $.Deferred(function(dfrd) {...}) form - twice - one outer and one (looped) inner - and by opting for a slightly different fulfilment strategy.

$.Deferred(function(outerDfrd) {
    db.transaction(function(ctx) {
        ctx.executeSql("DELETE from table",  [], function(x,y) {
            var promises = $.map(result, function(val) {
                return $.Deferred(function(innerDfrd) {
                    db.transaction(function(ctx) {
                        ctx.executeSql("INSERT INTO table(value1, value2) VALUES('" + val.value1 + "','" + val.value2 + "')", []);
                    }, innerDfrd.reject, innerDfrd.resolve);
                }).promise();
            });
            $.when.apply(null, promises).then(outerDfrd.resolve, outerDfrd.reject);
        });
    }, outerDfrd.reject, outerDfrd.notify);
}).progress(function() {
    //outer db.transaction has successfully completed
}).fail(function(err) {
    //report/handle error here
}).done(function() {
    //overall success here
});

The difference from the other answer is subtle. Exactly the same number of Deferreds will be created, but the mechanism for overall fulfilment differs in that the outer Deferred is resolved in response to all the inners having resolved. Here $.when.apply(...) is limited to the inner Deferreds, excluding the outer, and a chained .then() makes the final connection from inner success (or failure) back to the outer.

Into the bargain, you are afforded the opportunity to log/report intermediate progress - success of the outer db.transaction (the DELETE).

Using jQuery deferred and done on asynchronous SQLite database queries in a loop

2 Answers2

Linked