Bostone Bostone - 3 months ago 25
SQL Question

Android Sqlite IN, NOT IN syntax

I'm trying to run

NOT IN
select where
NOT IN
list is dynamic. Something like

SELECT id, type FROM CONTACTS where type NOT IN ('connect','answer')


In the code my futile attempts were:

db.query(TABLE, new String[] { "id", ""}, " type NOT IN (?)", "connect,answer",
null, null, null); // didn't work
db.query(TABLE, new String[] { "id", ""}, " type NOT IN (?)", "'connect','answer'",
null, null, null); // didn't work


My take on this is that
?
substitution treats my comma list as single argument. I did find a solution but it is rather ugly so I won't post it here until someone comes forward with something more elegant

Answer

You cannot place just one '?' instead of a list of values. As such, there is little to gain from trying to parametrize lists. One can, of course, create 2,4,16-value prepared statements ..." type NOT IN (?,?)", new String[]{ "connect","answer" },... but even on a server with remote RDBMS it has questionable value. Instead, do

db.query(TABLE, new String[] { "id", ""}, " type NOT IN ('connect','answer')", 
     null, null, null, null);
if the list is dynamic, you will have to escape the strings and put them into single quoted list.

Comments