db.transaction inside an for loop

Question

I need to do an db.transaction with the result of an for loop. But before the db.transaction completes the next iteration happens for the loop.

db.transaction(function(tx) {

tx.executeSql("select * from unassigned_item", [], function(tx, res) {
    var list = '';

    if (res != null && res.rows != null && res.rows.length>0) { 

        for (var i = 0; i < res.rows.length; i++) {
            var row = res.rows.item(i);
            var serial = row.serialno;
            var id = row.unassigned_itemid;
            var theSerialNo = row.serialno;

            tx.executeSql("select * from unassigned_item where serialno="+ serial + "", [], function(tx, res) {
                });

            if(row.serialno.indexOf("'")>-1){
                theSerialNo = theSerialNo.replace(/'/g,"\\'");
            }
            list = list + '<li data-icon="false"><a onClick="selectedSerialNo(\'' + theSerialNo + '\', +\'' +  id + '\');">' + theSerialNo + '</a></li>';
            //list = list + '<li data-icon="false"><a onClick="selectedSerialNo(\'' + theSerialNo + '\', +\'' +  id + '\');">' + serial + '</a></li>'
        }
    }
    //list = list + '<li data-icon="false"><a onClick="selectedSerialNo(\'NEW\', 0);">ADD_NEW</a></li>'
    $("#serial_suggestions li").remove();
    $("#serial_suggestions").append(list);

});
},function(){

},function(){


});

How can i acheive this ??

Answer 1

The db operations are asynchronous, so you can't use it in a for loop if you expect the next iteration to be done after the previous one is finished.

What you can do is use a variable as a counter and check for its value in the success callback of executeSql.

Something like this (sorry no time to check deeper what your function is supposed to do):

db.transaction(function (tx) {
    tx.executeSql("select * from unassigned_item", [], function (tx, res) {
        var list = '';
        if (res != null && res.rows != null && res.rows.length > 0) {
            var i = 0;
            function iteration(i, rows) {
                var row = res.rows.item(i);
                var serial = row.serialno;
                var id = row.unassigned_itemid;
                var theSerialNo = row.serialno;
                tx.executeSql("select * from unassigned_item where serialno=" + serial + "", [], function (tx, res) {
                    if (row.serialno.indexOf("'") > -1) {
                        theSerialNo = theSerialNo.replace(/'/g, "\\'");
                    }
                    list = list + '<li data-icon="false"><a onClick="selectedSerialNo(\'' + theSerialNo + '\', +\'' + id + '\');">' + theSerialNo + '</a></li>';
                    i += 1;
                    if (i < rows.length)
                        iteration(i, rows);
                    else
                        theEnd();
                });
            }
            function theEnd() {
                $("#serial_suggestions li").remove();
                $("#serial_suggestions").append(list);
            }

            iteration(i, rows);
        }

    });
});