Passing parameter in Sql IN statement

Question

How can I pass a parameter in the IN SQL statement ?

cursor = readableDb.rawQuery("SELECT * FROM User where User.objectId NOT IN (@Par)", new String{"ID"} );

Is it the correct way to do it ?

@DerGolem Does SQLite support passing in a list to `IN` like that? I've never used it but all other databases that I've worked with do not. — D Stanley, Jun 09 '15 at 19:22
@DerGolem : You mean to say cursor = readableDb.rawQuery("SELECT * FROM User where objectId not IN(?)", new String[]{String.valueOf(User.objectId)}); ? — Amit Kumar Kannaujiya, Jun 09 '15 at 19:31
@DerGolem SQL Server and Access CANNOT use a parameter value in an `IN` clause- there are custom functions that can _parse_ a string into a temp table and use _that_ but it's not as simple as passing a comma delimited string and using that as the `IN` value. — D Stanley, Jun 09 '15 at 20:08
@DerGolem See [this question](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) as an example. — D Stanley, Jun 09 '15 at 20:09
@DStanley Yes, right. You and Iaalto made me realize that the result would be something like `SELECT * FROM User where objectId NOT IN ('1, 2, 3')`, which is **wrong**, indeed. — Phantômaxx, Jun 09 '15 at 20:10

score 1 · Accepted Answer · answered Jun 09 '15 at 19:33

1

Variables get bound with a single literal value. If you have many values, you need a separate ? placeholder for each.

Example:

cursor = readableDb.rawQuery("SELECT * FROM User where objectId NOT IN (?,?,?)",
    new String[] { "1", "2", "3" });

answered Jun 09 '15 at 19:33

laalto

So this is not valid? `cursor = readableDb.rawQuery("SELECT * FROM User where objectId NOT IN (?)", new String[] { "1, 2, 3" });`. I really thought it was. Every day I do learn something. – Phantômaxx Jun 09 '15 at 19:52
@DerGolem No unless there's an `objectId` literally `1, 2, 3` you don't want to select. Variable binding is not string processing, it's SQL grammar and variables are literals from the grammar point of view. – laalto Jun 09 '15 at 19:56
I thought that the **?** placeholder was replaced by whatever string I gave it in. But! I must admit that now I'm thinking that in the process it adds the **'** string qualifiers around the string, during the binding - so, I'd end up with something like `SELECT * FROM User where objectId NOT IN ('1, 2, 3')`, which is **obviously wrong** – Phantômaxx Jun 09 '15 at 20:08

1 Answers1