SQLite rawQuery + String.format = "The statement has 0 parameters."?

Question

Am trying to exec an sqlite rawquery in android. I downloaded the DB from my device and tried-and-errored using a "sqlite query browser" windows program until i got what i wanted. The problem is now that i have the string i need i cant seem to get parameters into it and i suspect it to be just a string escape flaw of some kind.

The query i want to exec is

SELECT lis.name from listItemSuggestion lis LEFT JOIN listItem li ON lis.name = li.name WHERE li.name is NULL AND lis.name LIKE '%a%'

where the a in the '%a%' needs to be a variable parameter. What i made of that in code is this:

getReadableDatabase().rawQuery(
        String.format("SELECT lis.%s from %s lis LEFT JOIN %s li ON lis.%s = li.%s WHERE li.%s is NULL AND lis.%s LIKE '%%?%%';",
            ListItemSuggestion.COLUMN_NAME,
            ListItemSuggestion.TABLE_NAME,
            ListItem.TABLE_NAME,
            ListItemSuggestion.COLUMN_NAME,
            ListItem.COLUMN_NAME,
            ListItem.COLUMN_NAME,
            ListItemSuggestion.COLUMN_NAME
        ),
        new String[]{filter}
    );

The contract classes are:

import android.provider.BaseColumns;

public final class ListItem {

private ListItem(){}

public static class ListItemEntry implements BaseColumns
{
    public static final String TABLE_NAME   = "listItem";
    public static final String COLUMN_DONE  = "done";
    public static final String COLUMN_NAME  = "name";
    public static final String COLUMN_COUNT = "count";
    public static final String COLUMN_TIMESTAMP = "timestamp";
}

}

import android.provider.BaseColumns;

public final class ListItemSuggestion {

private ListItemSuggestion(){}

public static class ListItemSuggestionEntry implements BaseColumns
{
    public static final String TABLE_NAME   = "listItemSuggestion";
    public static final String COLUMN_NAME  = "name";
}
}

And the error i get is:

Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters. (Meaning its not finding the "?" in the query, right? though there is one..., btw "filter" is a method argument)

BTW: The purpose of that query is to provide suggestions for an input where listitems are dynamic ( = on a list/table and they can be deleted from at any time) while every item also silenty creates a suggestion item (in another table) which is not deleted when the actual item is. The where clause should fetch suggestions which match the current text input state and exclude items that are already on the list.

Answer 1

Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters. (Meaning its not finding the "?" in the query, right?

Not exactly.

though there is one..., btw "filter" is a method argument)

The problem is that the question mark is inside a quoted string. That makes it plain text, not a parameter placeholder. This variation seems to be what you were looking for:

getReadableDatabase().rawQuery(
        String.format("SELECT lis.%s from %s lis LEFT JOIN %s li ON lis.%s = li.%s WHERE li.%s is NULL AND lis.%s LIKE ?;",
            ListItemSuggestion.COLUMN_NAME,
            ListItemSuggestion.TABLE_NAME,
            ListItem.TABLE_NAME,
            ListItemSuggestion.COLUMN_NAME,
            ListItem.COLUMN_NAME,
            ListItem.COLUMN_NAME,
            ListItemSuggestion.COLUMN_NAME
        ),
        new String[]{ "%" + filter + "%" }
    );