websql use select in to get rows from an array

Question

in websql we can request a certain row like this:

tx.executeSql('SELECT * FROM tblSettings where id = ?', [id], function(tx, rs){
    // do stuff with the resultset.
},
function errorHandler(tx, e){
    // do something upon error.
    console.warn('SQL Error: ', e);
});

however, I know regular SQL and figured i should be able to request

var arr = [1, 2, 3];
tx.executeSql('SELECT * FROM tblSettings where id in (?)', [arr], function(tx, rs){
    // do stuff with the resultset.
},
function errorHandler(tx, e){
    // do something upon error.
    console.warn('SQL Error: ', e);
});

but that gives us no results, the result is always empty. if i would remove the [arr] into arr, then the sql would get a variable amount of parameters, so i figured it should be [arr]. otherwise it would require us to add a dynamic amount of question marks (as many as there are id's in the array).

so can anyone see what i'm doing wrong?

Sander · Accepted Answer · 2012-04-06 09:13:29Z

aparently, there is no other solution, than to manually add a question mark for every item in your array.

this is actually in the specs on w3.org

var q = "";
for each (var i in labels)
  q += (q == "" ? "" : ", ") + "?";

// later to be used as such:
t.executeSql('SELECT id FROM docs WHERE label IN (' + q + ')', labels, function (t, d) {
  // do stuff with result...
});

more info here: http://www.w3.org/TR/webdatabase/#introduction (at the end of the introduction)

however, at the moment i created a helper function that creates such a string for me might be better than the above, might not, i haven't done any performance testing.

this is what i use now

var createParamString = function(arr){
  return _(arr).map(function(){ return "?"; }).join(',');
}

// when called like this:
createparamString([1,2,3,4,5]);  // >> returns   ?,?,?,?,?

this however makes use of the underscore.js library we have in our project.

Tebe · Accepted Answer · 2014-10-07 20:55:35Z

Good answer. It was interesting to read an explanation in the official documentation.

I see this question was answered in 2012. I tried it in Google 37 exactly as it is recommened and this is what I got.

Data on input: (I outlined them with the black pencil)

enter image description here

Chrome complains:

enter image description here

So it accepts as many question signs as many input parameters are given. (Let us pay attention that although array is passed it's treated as one parameter)

Eventually I came up to this solution:

    var activeItemIds = [1,2,3];
    var q = ""; 
    for (var i=0; i< activeItemIds.length; i++) {
        q += '"' + activeItemIds[i] + '", ';
    }        
     q= q.substring(0, q.length - 2); 

     var query = 'SELECT "id" FROM "products" WHERE "id"  IN (' + q + ')';
    _db.transaction(function (tx) {
        tx.executeSql(query, [], function (tx, results1) {
            console.log(results1);
            debugger;

        }, function (a, b) {
            console.warn(a);
            console.warn(b);
        })
    })

Collectives™ on Stack Overflow

websql use select in to get rows from an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related