0

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.

Community
  • 1
  • 1
Ken Kem
  • 465
  • 1
  • 5
  • 13
  • How did you insert data into the FTS table? – CL. Jul 11 '16 at 09:20
  • @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 Jul 11 '16 at 10:08

1 Answers1

3

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');
CL.
  • 165,803
  • 15
  • 203
  • 239