CODEWITHSUNDEEP
iosobjective-csqlitein-clause
Nitish
Nitish

Reputation: 14123

Using 'in' clause for integer in Sqlite

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')

Upvotes: 0

Views: 292

Answers (1)

CL.
CL.

Reputation: 180162

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

Upvotes: 1

Related Questions