WebSQL transaction inside for loop on JS

Question

I am currently trying to insert data from an array in to a WebSQL DB and am getting some weird results. I assume it is because of it being async.

The problem is i am trying to insert data using transactions inside a for loop that is also nested inside another transaction. The code almost works but is only completing the final transaction of the loop.

                    for (i = 0; i < array.gArr.length; i++)
                    {
                        var group_id = array.gArr[i].group_id;
                        var title = array.gArr[i].title;
                        var description = array.gArr[i].description;
                        var photo = array.gArr[i].default_photo;


                        tx_num = "tx" + i;


                        db.transaction(function (tx_num) {     
                            tx_num.executeSql("INSERT OR IGNORE INTO my_groups(group_id, title, description, photo_url) VALUES (?,?,?,?)", [group_id, title, description, photo]);
                        });


                    }

The reason tx_num exists was an attempt to see if it was due to having the same tx name.

I have also tried using just INSERT INTO incase this was causing problems and the results are the same.

Just for your reference the table has been created using:

db.transaction(function (tx) {

    tx.executeSql('CREATE TABLE IF NOT EXISTS my_groups(group_id UNIQUE, title, description, photo_url)');

});

Any help would be appreciated.

score 1 · Accepted Answer · answered Aug 20 '15 at 09:44

I have found the solution for this.

It appears that the transaction is being overwritten each time the loop completes and as such it only completes the final transaction. To ensure all of them are run you need to enclose the loop inside the db.transaction. By doing this all transactions are run when db.transaction is run. This is all due to the async nature.

Here is the code for future reference:

db.transaction(function (tx1) {     
    for (i = 0; i < array.gArr.length; i++)
    {
        var group_id = array.gArr[i].group_id;
        var title = array.gArr[i].title;
        var description = array.gArr[i].description;
        var photo = array.gArr[i].default_photo;

        tx1.executeSql("INSERT OR REPLACE INTO my_groups(group_id, title, description, photo_url) VALUES (?,?,?,?)", [group_id, title, description, photo]);

    }

});

I personally prefer inserting multiple rows with a single query. This is especially useful if you need to do something after all the insertions have completed. Looping `tx1` makes it a bit harder, because you have to reconcile all the async calls. See https://stackoverflow.com/a/5009740/226819 for more information on multiple inserts with a single query (it's for sqlite, but applies to websql). — verboze, Aug 21 '18 at 15:12

WebSQL transaction inside for loop on JS

1 Answers1