Sqlite Android db is not updating or deleting for FTS?

Question

Hey so I am using FTS3 with Sqlite for faster searches for my android app, however, when I edit a word or delete one, it occurs fine on my real db, but on the FTS one, nothing happens.

When I search for the name pre-edit/update or post-delete it still shows up meaning it is there in the db...

Not sure what is wrong or if I have to go about it in a different way, here is my code:

/*
     * deleting all words from dictionary
     */
    public void deleteAllWords(DatabaseWords db_delete) {
        Log.d("enter delete in dbhelper",
                "enter delete in dbhelper "
                        + String.valueOf(db_delete.get_id()));
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_WORDS, KEY_DICTIONARYID + "=?",
                new String[] { String.valueOf(db_delete.get_dictionaryId()) });
    }

    /*
     * deleting all words FTS from dictionary
     */
    public void deleteAllWords_fts(DatabaseWordsFTS db_delete) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(
                TABLE_WORDS_FTS,
                KEY_DICTIONARY_ID_FTS + "=?",
                new String[] { String.valueOf(db_delete.get_dictionary_id_fts()) });
    }

    /*
     * 
     * 
     * /* deleting a dictionary
     */
    public void deleteDictionary(DatabaseDictionary dictionary) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_DICTIONARY, KEY_ID + "=?",
                new String[] { String.valueOf(dictionary.get_id()) });
    }

    /*
     * deleting a word
     */
    public void deleteWord(DatabaseWords db_delete) {
        Log.d("enter delete in dbhelper",
                "enter delete in dbhelper "
                        + String.valueOf(db_delete.get_id()));
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_WORDS, KEY_ID + "=?",
                new String[] { String.valueOf(db_delete.get_id()) });
    }

    /*
     * deleting a word FTS
     */
    public void deleteWord_fts(DatabaseWordsFTS db_delete) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_WORDS_FTS, KEY_ID + "=?",
                new String[] { String.valueOf(db_delete.get_id()) });
    }

/*
     * updating a word
     */
    public int updateWord(DatabaseWords word) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_DICTIONARYID, word.get_dictionaryId());
        values.put(KEY_WORD1, word.get_word1());
        values.put(KEY_WORD2, word.get_word2());
        values.put(KEY_WORD3, word.get_word3());
        values.put(KEY_WORD4, word.get_word4());

        // updating row
        return db.update(TABLE_WORDS, values, KEY_ID + "=?",
                new String[] { String.valueOf(word.get_id()) });

    }

    /*
     * updating a word FTS
     */
    public int updateWord_fts(DatabaseWordsFTS word_fts) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_DICTIONARY_ID_FTS, word_fts.get_dictionary_id_fts());
        values.put(KEY_WORD1_FTS, word_fts.get_word1_fts());
        values.put(KEY_WORD2_FTS, word_fts.get_word2_fts());
        values.put(KEY_WORD3_FTS, word_fts.get_word3_fts());
        values.put(KEY_WORD4_FTS, word_fts.get_word4_fts());

        // updating row
        return db.update(TABLE_WORDS_FTS, values, KEY_ID + "=?",
                new String[] { String.valueOf(word_fts.get_id()) });

    }

I left the regular one on top which works, the one with FTS is the FTS one.

Here I added the code for the update which is in my activity file

// store in db
            // creating word
            DatabaseWords db_update = new DatabaseWords(word_id, word_d_id,
                    w_1_s, w_2_s, w_3_s, w_4_s);

            DatabaseWordsFTS db_w_fts = new DatabaseWordsFTS(word_id,
                    word_d_id, w_1_s, w_2_s, w_3_s, w_4_s);
            // insert into db create method in database helper
            db.updateWord(db_update);

            db.updateWord_fts(db_w_fts);

The word updates but the fts one does not?

Answer 1

You cannot query/update/delete rows in a FTS table using a numeric value in the the where clause. It works only with text. But can cast it to a number:

SELECT * FROM fts_table WHERE CAST(id AS NUMERIC) = 69