Javascript SQL Insert Loop

Question

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!

Can you explain which library you're using to execute queries from client side? — Mouser
– Mouser, Commented Jan 17, 2015 at 17:19
I'm developing for iOS using SQLite with Cordova framework. Is that the information you needed? — CountingStacks
– CountingStacks, Commented Jan 17, 2015 at 17:22
How do JavaScript closures work? + Exploits of a Mom (also known as: SQL injection) — Andreas
– Andreas, Commented Jan 17, 2015 at 17:23
Thanks. However could you explain what colorDataQuery and gameOptionSuccess do? They are callBack functions. — Mouser
– Mouser, Commented Jan 17, 2015 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
– CountingStacks, Commented Jan 17, 2015 at 17:27

celeritas · Accepted Answer · 2015-01-17 17:40:04Z

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.

edited Jan 17, 2015 at 17:40

answered Jan 17, 2015 at 17:31

celeritas

2,2892 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mouser Over a year ago

Could you please explain to why this works and the posted example won't?

celeritas Over a year ago

Tried to explain in more depth.

Collectives™ on Stack Overflow

Javascript SQL Insert Loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related