Android SQLiteException: bind or column index out of range problem

In android I am using the following statement.

model = dataHelper.rawQuery("SELECT _id, engword, lower(engword) as letter FROM word WHERE letter >= 'a' AND letter < '{' AND engword LIKE '%" + filterText + "%'", new String[ {"_id","engword", "lower(engword) as letter"});

It is throwing android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x132330

What is the problem in my code?

Answers


The right statement is :

model = dataHelper.rawQuery("
    SELECT _id, engword, lower(engword) as letter
    FROM word W
    HERE letter >= 'a'
    AND letter < '{'
    AND engword LIKE ? ORDER BY engword ASC
    ",
    new String[] {"%" + filterText + "%"}
);

You provided 3 parameters but you have no ? in your query. Pass null instead of string array as the 2nd argument to the rawQuery or replace _id, engword and lower(engword) as letter in your select string by ?

1)

model = dataHelper.rawQuery("SELECT ?, ?, ? FROM word WHERE letter >= 'a' AND letter < '{' AND engword LIKE '%" + filterText + "%'",new String[] {"_id","engword", "lower(engword) as letter"});

2)

model = dataHelper.rawQuery("SELECT _id, engword, lower(engword) as letter FROM word WHERE letter >= 'a' AND letter < '{' AND engword LIKE '%" + filterText + "%'", null);

Edit: As @Ewoks pointed out, the option (1) is incorrect, since prepared statements can get parameters (?s) only in WHERE clause.


If anyone is like me trying (and failing) to get this working with getContentResolver().query here how I managed it:

*Updated thanks to comments from @CL and @Wolfram Rittmeyer, as they said this is the same as for rawQuery *

Correct way:

  public static String SELECTION_LIKE_EMP_NAME = Columns.EMPLOYEE_NAME
            + " like ?";            

  Cursor c = context.getContentResolver().query(contentUri,
                PROJECTION, SELECTION_LIKE_EMP_NAME, new String[] { "%" + query + "%" }, null);

Previous answer that was open to SQL injection attack:

public static String SELECTION_LIKE_EMP_NAME = Columns.EMPLOYEE_NAME
            + " like '%?%'";

String selection = SELECTION_LIKE_EMP_NAME.replace("?", query);

Cursor c = context.getContentResolver().query(contentUri,
            PROJECTION, selection, null, null);

Need Your Help

Create dummies from column with multiple values in pandas

python pandas dummy-data categorical-data

I am looking for for a pythonic way to handle the following problem.

How to run an async task for every x mins in android?

android android-asynctask

how to run the async task at specific time? (I want to run it every 2 mins)