javascript for-loop in SQLite query

Question

I have created a function that will return the string "path" from my DB.

    function getAudio(mid, cb) {
    //mid is an array containing the id to some multimedia files.
    for(i=0; i < mid.length; i++) {

        db.transaction(function(tx) {
                tx.executeSql('SELECT * FROM Multimedia WHERE Mid=' + mid[i] + ' AND Type=' + 2, [], function(tx, results) {    
                //type=2 is audio. (type=1 is picture file etc) There is only one audiofile in the array.
                    if(results.rows.length > 0) {
                        path = results.rows.item(0).Path; 
                        cb(path);
                    }
                }, errorCB);
        }, errorCBQuery);

    }//end for
}//end getAudio()

When I remove the for-loop, the query is successfull, when the for-loops is there, errorCBQuery or errorCB is called.

Any Ideas on how to fix this? Thanks :)

lol that is just a typo :p there has been plenty of removing and adding code the last hour. My bad :) — TorK
– TorK, Commented Mar 26, 2012 at 11:40
@T.J.Crowder ok, thanks for notifying me about that. edited :) — TorK
– TorK, Commented Mar 26, 2012 at 11:57
You should use parameterized sql instead of using string concatenation, in order to protect yourself from SQL injection. Given the array argument you passed I guess it would look something like tx.executeSql('SELECT * FROM Multimedia WHERE Mid=? AND Type=?', [mid[i], 2]. — hugomg
– hugomg, Commented Mar 26, 2012 at 13:09

T.J. Crowder · Accepted Answer · 2012-03-26 13:13:30Z

7

This is the classic closure problem. transaction is an asynchronous call, which means your loop completes before the function you pass in is triggered. The function has an enduring reference to the i variable, not a copy of it as of when you called transaction. So each of those functions (you're generating one on each loop) is seeing i == mid.length and thus mid[i] is undefined and your SQL gets messed up.

What you need to do is have the callbacks close over another variable that won't change when the loop proceeds. The usual way to do that is to use a factory function:

function getAudio(mid, cb) {
    //mid is an array containing the id to some multimedia files.
    for(i=0; i < mid.length; i++) {

        db.transaction(makeTx(mid[i]), errorCBQuery);

    }//end for

    function makeTx(val) {
        return function(tx) {
            tx.executeSql('SELECT * FROM Multimedia WHERE Mid=' + val + ' AND Type=' + 2, [], function(tx, results) {    
            //type=2 is audio. (type=1 is picture file etc) There is only one audiofile in the array.
                if(results.rows.length > 0) {
                    path = results.rows.item(0).Path; 
                    cb(path);
                }
            }, errorCB);
        };
    }

}//end getAudio()

There, I'm passing mid[i] into the makeTx function, which returns the function that will get passed into transaction. The function we return closes over the val argument for the call to makeTx that created it, which doesn't change.

That's a minimal rewrite of the code; you can probably take it further. For example, see missingno's comment on the question re parameterized statements.

Side note: It doesn't look like you declare i or path anywhere. If that's really true, you're falling prey to The Horror of Implicit Globals. Recommend declaring them.

edited Mar 26, 2012 at 13:13

answered Mar 26, 2012 at 12:04

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

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

4 Comments

TorK Over a year ago

@YaqubAhmad: can't upvote before I got 15 in reputation. But I answered "yes" at "useful post" :)

T.J. Crowder Over a year ago

@TorK: You can, however, accept answers (if you find them acceptable).

TorK Over a year ago

@T.J.Crowder: aaah so that's what the symbol meant ^^ done thanks again, guess I see you guys around ;)

Vinod Over a year ago

Hi crowder, can you please suggest me how to resolve my problem, the link is as follows stackoverflow.com/questions/23199541/…

Collectives™ on Stack Overflow

javascript for-loop in SQLite query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related