Using 'in' clause for integer in Sqlite

Question

I have an Integer column (response) in one of the tables. I have to use In clause to get the results. But that is not working. This is what I am doing :

NSString *responseString = @"";
    if(filter.isAttending && filter.isDeclined && filter.isNotResponded)
        responseString = @"0,1,3";
    else if(filter.isAttending && filter.isDeclined && !filter.isNotResponded)
        responseString = @"0,1";
    else if(filter.isAttending && !filter.isDeclined && filter.isNotResponded)
        responseString = @"0,3";
    else if(!filter.isAttending && filter.isDeclined && filter.isNotResponded)
        responseString = @"1,3";
    else if(filter.isAttending && !filter.isDeclined && !filter.isNotResponded)
        responseString = @"0";
    else if(!filter.isAttending && !filter.isDeclined && filter.isNotResponded)
        responseString = @"3";
    else if(!filter.isAttending && filter.isDeclined && !filter.isNotResponded)
        responseString = @"1";
    else if(!filter.isAttending && !filter.isDeclined && !filter.isNotResponded)
        responseString = @"-1";  


const char *sql  = sql = "SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId = Activity.ActivityId Where Invitation.Response in (?) AND Activity.IsDeleted = ? AND Activity.IsAdmin = ? AND Activity.StartDate <= DateTime('now')";

This is how I am binding the values :

sqlite3_bind_text(statement, 1, [responseString UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(statement, 2, isDeleted);
sqlite3_bind_int(statement, 3, [isAdmin intValue]);

I get no results from this. What I doubt is, the query becomes :

SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId =   Activity.ActivityId Where Invitation.Response in ("0,1,3") AND Activity.IsDeleted = 1 AND Activity.IsAdmin = 251697 AND Activity.StartDate >= DateTime('now')

Note that it is ("0,1,3") and not (0,1,3).

However when I write this query in Sqlite manager, it gives me correct results :

SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId =   Activity.ActivityId Where Invitation.Response in (0,1,3) AND Activity.IsDeleted = 1 AND Activity.IsAdmin = 251697 AND Activity.StartDate >= DateTime('now')

Update your question with how you bind the value for the `in` clause. — rmaddy, Jun 14 '16 at 05:36
You need to remove double quote form ("0,1,3"), it must be (0,1,3) — Nirav D, Jun 14 '16 at 05:51
Possible duplicate of [SQLite: bind list of values to "WHERE col IN ( :PRM )"](http://stackoverflow.com/questions/4788724/sqlite-bind-list-of-values-to-where-col-in-prm) — Murphy, Jun 14 '16 at 13:26

score 1 · Answer 1 · answered Jun 14 '16 at 08:49

Your doubt is justified; sqlite3_bind_text() binds a single text value.

To bind multiple integer values, you have to insert the apropriate number of parameter markers into the SQL string (... IN (?,?,?)), and call sqlite3_bind_int() for each of them.

(It might be easier to put the numbers directly into the SQL query, with %@.)

Using 'in' clause for integer in Sqlite

1 Answers1