FTS4 sqlite MATCH query not working

0
votes

I already read from here FTS4 sqlite MATCH not working and here Full text search example in Android but It can't solve my problem.

I have a external database and I use sqlite 3 version 3.13.0 with 2 table like this.

enter image description here

And I create a virtual table:

CREATE VIRTUAL TABLE tb_bank_fts USING fts4 (content="tb_bank",address, typename)

Virtual table create successfull and I can use select.

enter image description here

But I can't use "match" query with fts4 table, alway return null with no error.

enter image description here

Half month ago I can query fts4 table with "match" but now i can't do that.I don't know why. I try using SQlite Manager addon in Firefox but same problem.

1
androidsqlite
How did you insert data into the FTS table? – CL.
@CL. I use this code CREATE VIRTUAL TABLE fts_table_name USING fts4 (content="original_table_name", colum1, colum2 ...). All record from original table will insert to virtual table with columns you choose. Sorry for my bad english! – Ken Kem

1 Answers

3
votes

The documentation says:

The FTS4 module never writes to the content table, and writing to the content table does not affect the full-text index. It is the responsibility of the user to ensure that the content table and the full-text index are consistent.

So when you've inserted data into the base table, you also have to insert it into the FTS table:

INSERT INTO tb_bank_fts(docid, address, typename)
SELECT rowid, address, typename FROM tb_bank;

or simply:

INSERT INTO tb_bank_fts(tb_bank_fts) VALUES('rebuild');