how to query fts tables on android

Question

I am developing for android. I am using reference http://sqlite.org/fts3.html. I create a fts table as

final String SQL_CREATE_TABLE_SEARCHABLE =
    
        new StringBuilder("CREATE VIRTUAL TABLE ")
    
                .append(SearchableEntry.TABLE_NAME)
    
                .append(" USING fts4(")

                    .append(SearchableEntry.COLUMN_DOG_ID).append(", ")

                    .append(SearchableEntry.COLUMN_BREED).append(", ")
    
                .append(SearchableEntry.COLUMN_CATEGORY).append(",")

                     .append(SearchableEntry.COLUMN_DOG_NAME).append(",")

                    .append(SearchableEntry.COLUMN_DOCUMENTATION)
    
                .append(");").toString();

    sqLiteDatabase.execSQL(SQL_CREATE_TABLE_SEARCHABLE);

COLUMN_DOG_ID is actually Text. It is a string and not a number. In any case I create the table and then I add data to it; seemingly without any trouble. But when I try to query, I always hit a dead end: I get exceptions. Here is the query

private Cursor getSearchableByTokens(Uri uri, String[] projection) {

        DogLog.d(TAG,"getSearchableByTokens with uri %s",uri);

        String table = DogContractt.SearchableEntry.TABLE_NAME;

        String selection = table + " MATCH ? ";

        String queryText = DogContractt.SearchableEntry.getQueryTextFromUri(uri);

        String[] selectionArgs = {queryText};

        if (null == mOpenHelper) {
    
        mOpenHelper = new DogsDBHelper(getContext());

        }

        SQLiteDatabase readableDB = mOpenHelper.getReadableDatabase();

        Cursor cursor = readableDB.query(table, projection, selection, selectionArgs, null, null, null);

        if (null != cursor) {
            cursor.setNotificationUri(getContext().getContentResolver(), DogContractt.SearchableEntry.CONTENT_URI);

        }
    
    return cursor;
    
}

Error

FATAL EXCEPTION: main
Process: com.company.android, PID: 18696
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.company.android/com.company.android.activities.SearchableActivity}: java.lang.IllegalArgumentException: column '_id' does not exist
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3122)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3221)
at android.app.ActivityThread.access$1000(ActivityThread.java:198)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1676)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:145)
at android.app.ActivityThread.main(ActivityThread.java:6840)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1404)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1199)
 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist
at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
at android.database.CursorWrapper.getColumnIndexOrThrow(CursorWrapper.java:78)
at android.support.v4.widget.CursorAdapter.init(CursorAdapter.java:174)
at android.support.v4.widget.CursorAdapter.<init>(CursorAdapter.java:138)
at com.company.android.activities.SearchableActivity$2.<init>(SearchableActivity.java:0)
at com.company.android.activities.SearchableActivity.searchableResultsList(SearchableActivity.java:81)
at com.company.android.activities.SearchableActivity.searchContentProvider(SearchableActivity.java:73)
at com.company.android.activities.SearchableActivity.onCreate(SearchableActivity.java:59)
at android.app.Activity.performCreate(Activity.java:6535)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1120)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3075)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3221) 
at android.app.ActivityThread.access$1000(ActivityThread.java:198) 
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1676) 
at android.os.Handler.dispatchMessage(Handler.java:102) 
at android.os.Looper.loop(Looper.java:145) 
at android.app.ActivityThread.main(ActivityThread.java:6840) 
at java.lang.reflect.Method.invoke(Native Method) 
at java.lang.reflect.Method.invoke(Method.java:372) 
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1404) 
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1199) 

Answer 1

A cursor adapter requires a unique, numeric column named _id. This applys to every query result, whether from an FTS table or a normal table.

FTS tables have an internal docid column; just add it to the projection:

String[] projection = new String[] { "docid AS _id", ... };