4

I'm attempting to pass a function an array that should run through a loop and call a db.transaction for each incremented SQL statement.

function updateColorData (colorArray) {
    for (var i=0; i<colorArray.length; i++) {
        var sql = 'INSERT INTO SPColorData (color) VALUES (\''+colorArray[i]+'\')';
        if (i < colorArray.length-1) {
            db.transaction(function (tx) {tx.executeSql(sql, [], gameOptionSuccess, errorCB)}, errorCB);
        } else {
            db.transaction(function (tx) {tx.executeSql(sql, [], colorDataQuery, errorCB)}, errorCB);
        }
    }
}

As a test I'm calling the updateColorData function passing in an array like this

['one', 'two', 'three', 'four']

but when I have the database read back the information it received I'm getting 

['four', 'four', 'four', 'four']

I realize that calling 4 database transactions in a loop like this is not the most efficient method but I'm not sure why this isn't working or what other method to try.

Thanks!

Mouser
  • 13,132
  • 3
  • 28
  • 54
CountingStacks
  • 267
  • 2
  • 10
  • Can you explain which library you're using to execute queries from client side? – Mouser Jan 17 '15 at 17:19
  • I'm developing for iOS using SQLite with Cordova framework. Is that the information you needed? – CountingStacks Jan 17 '15 at 17:22
  • [How do JavaScript closures work?](http://stackoverflow.com/a/111111/402037) + [Exploits of a Mom](http://xkcd.com/327/) (also known as: [SQL injection](http://en.wikipedia.org/wiki/SQL_injection)) – Andreas Jan 17 '15 at 17:23
  • Thanks. However could you explain what `colorDataQuery` and `gameOptionSuccess` do? They are callBack functions. – Mouser Jan 17 '15 at 17:23
  • gameOptionSuccess is empty right now but colorDataQuery just calls a Select query that re-renders out the javascript array I have that stores the data from the database. – CountingStacks Jan 17 '15 at 17:27
  • Could you also add your test example (the one calling `updateColorData`). – Mouser Jan 17 '15 at 17:30

1 Answers1

4

You need to create a new scope for i before you call the database function; try this:

function updateColorData (colorArray) {
    for (var i=0; i<colorArray.length; i++) {
        (function(i){
            var sql = 'INSERT INTO SPColorData (color) VALUES   (\''+colorArray[i]+'\')';
            if (i < colorArray.length-1) {
                db.transaction(function (tx) {tx.executeSql(sql, [], gameOptionSuccess, errorCB)}, errorCB);
            } else {
            db.transaction(function (tx) {tx.executeSql(sql, [], colorDataQuery, errorCB)}, errorCB);
            }
        })(i);
    }
}

That creates an individual function scope for each value of i, using an anonymous function. You need to do this because the for loop in your original example keeps updating i without waiting for your database function to return. So you need to create "safe" contexts for your database functions to run without the for loop changing the value of i, and that's exactly what the anonymous functions provide.

celeritas
  • 2,191
  • 1
  • 17
  • 28