"optimize" command in sqlite fts3 on android makes "MATCH" doesn't work

Question

I am creating a fts4 table in android , I batch insert large number of rows then optimize as recommended in sqlite documentation

I use the following code in android

public void indexFts() {
      SQLiteDatabase db = getWritableDatabase();
      db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS pageTextSearch USING  fts4(content=\"\", page)");
 db.beginTransaction();
        try {
            SQLiteStatement populateFTS_Statement = db.compileStatement(INSERT INTO pageTextSearch(docid,page)VALUES (?, ?)); //pre-compiled sql statement


            while (allPagesCursor.moveToNext()) {
                populateFTS_Statement.clearBindings();
                populateFTS_Statement.bindLong(1, allPagesCursor.getLong(0));
                populateFTS_Statement.bindString(2, allPagesCursor.getString(1));
                populateFTS_Statement.executeInsert();

            }
            db.execsql( INSERT INTO pageTextSearch(pageTextSearch)VALUES('optimize'));

            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();

        }
}

now when I query the fts table with match I get 0 result whatever the query for example:

  db.rawQuery(SELECT docid FROM pageTextSearch WHERE page MATCH ?
                , new String[]{"a"});

will give an empty cursor

Answer 1

After trying a lot to spot the error (the transaction,the fts contentless table and the precompiles SQL) The problem actually is with using execsql, using rawQuery instead solved the problem I didn't find this behaviour documented in the android execsql documenationn see this question for discussion about using execsql to insert

so the working code is

 db.rawQuery( INSERT INTO pageTextSearch(pageTextSearch)VALUES('optimize'),null);