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
– rmaddy, Commented Jun 14, 2016 at 5:36
You need to remove double quote form ("0,1,3"), it must be (0,1,3) — Nirav D
– Nirav D, Commented Jun 14, 2016 at 5:51

CL. · Accepted Answer · 2016-06-14 08:49:53Z

1

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 %@.)

answered Jun 14, 2016 at 8:49

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Using 'in' clause for integer in Sqlite

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related