Search through SQLite database on Android Search

Question

Method causing error inside my ContentProvider

public static Cursor getSuggestions(String query) {
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    db.beginTransaction();
    try {
        String selection = Formula.FORMULA_NAME + " LIKE %?%";
        String[] selectionArgs = { query + "*" };
        Cursor cursor = dbHelper.getReadableDatabase().query(
                FORMULA_TABLE_NAME,
                new String[] { BaseColumns._ID,
                        SearchManager.SUGGEST_COLUMN_TEXT_1, 
                        BaseColumns._ID + " AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID 
                        }, 
                        selection,
                selectionArgs, null, null, null);
        db.setTransactionSuccessful();
        return cursor;
    } catch (SQLiteException e) {
    } finally {
        db.endTransaction();
    }
    throw new SQLException("Failed to begin transaction");
}

Database creation:

db.execSQL("CREATE TABLE " + FORMULA_TABLE_NAME + " (" +
                BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                SearchManager.SUGGEST_COLUMN_TEXT_1 + " TEXT," +
                Formula.CATEGORY + " TEXT" +
                ");");

Constants used:

    public static final String FORMULA_NAME = SearchManager.SUGGEST_COLUMN_TEXT_1;
    public static final String CATEGORY = "category";

The problem is that in my method, the transaction is unsuccessful because it throws the error: throw new SQLException("Failed to begin transaction"); What I'm trying to do is to search through the database as part of a search. When the user activates the search box, then I have it set up so that this method should be returning a cursor with the suspected items based on their name. Through debugging, I deduced that the problem was with the method of search inside my Content Provider. Any solutions or thoughts?

Sam · Accepted Answer · 2012-04-14 18:32:30Z

1

I'm guessing the line throw new SQLException("Failed to begin transaction"); is meant to be inside your catch block.

What if we simplify everything to:

public static Cursor getSuggestions(String query) {
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    String selection = Formula.FORMULA_NAME + " LIKE %?%";
    String[] selectionArgs = { query + "*" };
    Cursor cursor = db.query(
            FORMULA_TABLE_NAME,
            new String[] { BaseColumns._ID,
                    SearchManager.SUGGEST_COLUMN_TEXT_1, 
                    BaseColumns._ID + " AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID 
                    }, 
            selection,
            selectionArgs, null, null, null);
    return cursor;
}

answered Apr 14, 2012 at 18:32

Sam

87k21 gold badges185 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohit Deshpande Over a year ago

I get this: 04-14 15:29:18.264: W/SuggestionsAdapter(624): android.database.sqlite.SQLiteException: near "%": syntax error: , while compiling: SELECT _id, suggest_text_1, _id AS suggest_intent_data_id FROM formula WHERE suggest_text_1 LIKE %?% Which implies that my selection statement is wrong

Sam Over a year ago

Indeed, I didn't notice that. The ? is used by itself, it is simply a symbol to "cut & paste" a string from selectionArgs. You want Formula.FORMULA_NAME + " LIKE ?". The SQL wildcard % means "one or more irrelevant characters", this belongs in selectionArgs, for example { query + "%" }.

Collectives™ on Stack Overflow

Search through SQLite database on Android Search

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related