HTML5 WebSQL: how to know when a db transaction finishes?

Question

I've the following code that gets a json recordset and insert some data in three different tables on the client Web Sql storage.

How can I intercept the end of databaseSync() function? What I want to do is display an alert or better an ajax spinner gif in order to inform the user when the sync is complete.

Many thanks for your help, ciao!

function databaseSync() {

        // table one
        $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=one", function(json) {
            $.each(json.results, function(i, res) {
                db.transaction(function(tx) {
                    tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
                });
            });
        });

        // table two
        $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=two", function(json) {
            $.each(json.results, function(i, res) {
                db.transaction(function(tx) {
                    tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
                });
            });
        });

        // table three
        $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=three", function(json) {
            $.each(json.results, function(i, res) {
                db.transaction(function(tx) {
                    tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
                });
            });
        });


    }

Alarise

+1 You need to wait until all onSuccess or onError have been called. +1 for anyone with a nice way to write that. – Hazard

Answer 1

Ok, this is my fifth revision, but I liked this question and I keep coming up with better ideas. This one uses jquery deferred objects and I think it finally covers all cases and works the way it should.

function tableInsert(url) {
    var dfd = $.Deferred();
    var arr = [];
    $.getJSON(url, function(json) {
        $.each(json.results, function(i, res) {
            var dfd = $.Deferred();
            arr.push(dfd.promise()); 
            db.transaction(function(tx) {
                tx.executeSql(
                    "INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", 
                    [res.A, res.B, res.C, res.D], 
                    function(){
                        onSuccess(dfd.resolve);
                    }, 
                    function(){
                        onError(dfd.resolve);
                    }
                );
            });
        });
        $.when.apply(this, arr).then(dfd.resolve);
    });
    return dfd.promise();
}

function databaseSync() {

    $.when( tableInsert("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=one"),
            tableInsert("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=two"), 
            tableInsert("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=three"))
        .then(function(){
            console.log( 'All processing complete' );
        });
}

For this to work you'll need to change onSuccess and onError to execute the resolve function as a callback function after doing whatever else it is they do and then this should work for you. I hope you find this useful.

Lyndell

I think you've mixed tableInsert with insertTable. – Vitus

@JeffHutchins Wouldn't it make sense to move the transaction outside of the each? I used a system similar to yours but when bulk inserting a 1000 rows using a transaction for each insert really slowed the process down – Hereto

If you would put the transaction outside the loop your whole need for keeping track of Deferreds would disappear. Either all executeSql statements would come through or none. You just need to monitor the success or errorcallbacks of the transaction. Plus it would be faster. – Ambassadress

Answer 2

Alternately, you can use one transaction for the bulk insert and use callback function to get notified about completion of the transaction

function doSync(){
  databaseSync(function(){
    console.log('database sync is completed')
  });
}

function databaseSync(onTrxSuccess) {
  db.transaction(function(tx) {
  // table one
  $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=one", function(json) {
        $.each(json.results, function(i, res) {                
                tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
            });
        });


    // table two
    $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=two", function(json) {
        $.each(json.results, function(i, res) {
                tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
            });
    });

    // table three
    $.getJSON("http://192.168.1.40:8888/iOS/mobilesrv/index.php?ACT=three", function(json) {
        $.each(json.results, function(i, res) {
                tx.executeSql("INSERT INTO table1 (A, B, C, D) VALUES (?,?,?,?) ", [res.A, res.B, res.C, res.D], onSuccess, onError);
            });
        });
    }, null, onTrxSuccess);


}

Isometropia

You are not guaranteed that the inserts will happen sequentially. So 2/3 might be lost. – Ambassadress