CODEWITHSUNDEEP
androidsqlite
dev_android
dev_android

Reputation: 8828

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?

Upvotes: 17

Views: 27329

Answers (3)

dev_android
dev_android

Reputation: 8828

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 + "%"}
);

Upvotes: 41

scottyab
scottyab

Reputation: 24039

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);

Upvotes: 3

ernazm
ernazm

Reputation: 9268

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.

Upvotes: 23

Related Questions