SQLite query error with MATCH

Question

I'm using the following query to perform a search in SQLite.

public MatrixCursor listViewCursor(String query) {
    open();
    Cursor dcursor = null;
    MatrixCursor cursor = new MatrixCursor(listviewColumns);
    Object[] listviewarray = null;
    String[] selectionArgs = new String[] { query };
    System.out.println("Selectionarg = "+selectionArgs.toString());
    if (query == null) {
        try {// this works
            dcursor = database.query(LA_DATABASE_TABLE, listviewColumns,
                    null, null, null, null, LA_TEST_NAME);
        } catch (Exception e) {
            e.printStackTrace();
        }
    } else
        try {// this don't work!!!
            dcursor = database.query(LA_DATABASE_TABLE, listviewColumns,
                    LA_TEST_NAME + " MATCH ?", new String[] { query }, null, null, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    System.out.println("dcursor count = "+dcursor.getCount());
    listviewarray = new Object[dcursor.getColumnCount()];
    if (dcursor.moveToFirst()) {
        do {

            for (int i = 0; i < dcursor.getColumnCount(); i++) {
                listviewarray[i] = dcursor.getString(i);
            }
            cursor.addRow(listviewarray);
            System.out.println(dcursor.getString(1));
        } while (dcursor.moveToNext());
    } else
        return null;
    close();
    return cursor;

}

However I'm getting the following error.

0

1-01 04:36:27.376: I/SqliteDatabaseCpp(3171): sqlite returned: error code = 1, msg = statement aborts at 7: [SELECT _id, suggest_text_1, test_value_1, test_value_2, test_type_1 FROM la_table WHERE suggest_text_1 MATCH ?] unable to use function MATCH in the requested context, db=/data/data/com.assistant.lab.royale/databases/la_db
01-01 04:36:27.376: E/SQLiteQuery(3171): exception: SQL logic error or missing database; query: SELECT _id, suggest_text_1, test_value_1, test_value_2, test_type_1 FROM la_table WHERE suggest_text_1 MATCH ?
01-01 04:36:27.376: D/AndroidRuntime(3171): Shutting down VM
01-01 04:36:27.376: W/dalvikvm(3171): threadid=1: thread exiting with uncaught exception (group=0x409c01f8)
01-01 04:36:27.465: E/AndroidRuntime(3171): FATAL EXCEPTION: main
01-01 04:36:27.465: E/AndroidRuntime(3171): android.database.sqlite.SQLiteException: SQL logic error or missing database
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteQuery.nativeFillWindow(Native Method)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:86)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:164)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:156)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at com.assistant.lab.royale.DataSource.listViewCursor(DataSource.java:52)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at com.assistant.lab.royale.LabAssistant.filterListView(LabAssistant.java:32)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at com.assistant.lab.royale.LabAssistant.onQueryTextChange(LabAssistant.java:54)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.widget.SearchView.onTextChanged(SearchView.java:1091)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.widget.SearchView.access$2000(SearchView.java:90)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.widget.SearchView$11.onTextChanged(SearchView.java:1548)
01-01 04:36:27.465: E/AndroidRuntime(3171):     at android.widget.TextView.sendOnTextChanged(TextView.java:7634)

Why is this happening

EDIT :

I fixed it using dcursor = database.query(LA_DATABASE_TABLE, listviewColumns , LA_TEST_NAME + " LIKE '%"+query+"%'",null, null, null, null); but I'd like to know why the above code didn't work. Seems like ? is not getting replaced properly.

Answer 1

See the Introduction to FTS3 and FTS4 from here http://sqlite.org/fts3.html

and make sure you created a virtual table like this

 CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);

not an ordinary like this

CREATE TABLE enrondata2(content TEXT);