selectionArgs in ContentResolver.query(...) can be a subquery?

Question

I'm working with SQLite on Android.
I used ContentProvider to query data from db. And now, I have a problem when try to use subquery via ContentResolver

String selection = "cat_id NOT IN ?"
String[] selectionArgs = new String[]{"(SELECT Categories.id FROM Categories)"}
cursor = mResolver.query(getContentUri(), getListColumns(),
                    selection, selectionArgs, orderBy);

And this is the error:

08-06 10:32:36.070: E/AndroidRuntime(2151): Caused by: android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: SELECT * FROM TRANSACTIONS WHERE cat_id NOT IN ? ORDER BY time_created ASC, id ASC`

My question is "Can I use selectionArgs be a Subquery?"
My purpose is "get the list of transactions where cat_id is NOT IN a Category table".
Who can help me?

Commerce

Answer 1

Don't forget here that ? is actually a placeholder for a value. Which will be binded later by android.

As such, you would not put a query to be replaced by ?. As the point of using the ? is to ensure that sql code is not injected by user parameters.

String selection = "cat_id NOT IN ?"
String[] selectionArgs = new String[]{"(SELECT Categories.id FROM Categories)"}
cursor = mResolver.query(getContentUri(), getListColumns(), selection, selectionArgs, orderBy);

Is equilivant to you writing something like

String selection = "cat_id NOT IN '(SELECT Categories.id FROM Categories)'"

Where the query you want run is actually being though of as a value, meaning that NOT IN '(some value)' is not valid sql.

I suggest that you just remove the ? and replace it with the query you have in your where arguments which will fix it.

You would use the ? placeholders like this (if you knew what it didn't have to be in).

String selection = "cat_id NOT IN (?, ?)"
String[] selectionArgs = new String[]{"value1", "value2"}
cursor = mResolver.query(getContentUri(), getListColumns(), selection, selectionArgs, orderBy);

Edit: Try

String selection = "cat_id NOT IN (SELECT Categories.id FROM Categories)"
cursor = mResolver.query(getContentUri(), getListColumns(), selection, null, orderBy);

Cornell

Thanks you so much for your answer. I cannot define exactly the items NOT IN (?,?...). I think the sql query "Select * from TRANSACTIONS where cat_id NOT IN (Select CATEGORIES.id from CATEGORIES)" is Okay. But I don't know how to make this query by using ContentResolver? – Commerce

have you tried to remove the ? and just have the full code in there? you also then want to pass null through for the selectionArgs – Cornell

I just have the same problem using content provider update, but I can't understand what is inside (?, ?). What is "value1" and "value2" ? Thanks!! – Sharondasharos

@m3n0R the ? are placeholders for values. E.g. the first ? is for value1. It essentially becomes cat_id not in ('value1', 'value2') based off example above – Cornell

Yes the first code snippet doesn't work. You can put the inner select as a prepared stmt. You must write a static statement. – Appraisal

ARG, "cannot put the inner selecrt ..." – Appraisal

Answer 2

I give you an example that should work (in update ContentResolver method in my case):

String selection =  DatabaseContract.EventTable.Column.ID_EVENT + " IN (SELECT DISTINCT "
    + DatabaseContract.CountryEventTable.Column.EVENT_ID + " FROM "
    + DatabaseContract.CountryEventTable.TABLE_NAME + " WHERE "
    + DatabaseContract.CountryEventTable.Column.COUNTRY_CODE + "=?)";

String [] selectionArgs = new String[]{
    data[0],
};

Sharondasharos