Have never supplied a match function when using MATCH.
OK. So what do you expect MATCH to do (compared to, say, LIKE, assuming they are comparable)? I wondered whether the built-in MATCH was a dummy, thus explaining the OP getting no results.
I’m using MATCH because I have a Valentina database which doesn’t have any FTS search. It worked pretty much out of the box and I didn’t have to do anything special. Except loading the ICU dylib from Thomas Templemann and setting the correct tokenizer.
Probably i’m discovering the issue.
Cause i don’t populate my listbox neither with a simple
SELECT * FROM cNotifiche
I suspect that my cNotifiche is empty.
So i split these 2 query at the beginning (in SetupDB method).
Try
App.DBVirtual.ExecuteSQL(“CREATE VIRTUAL TABLE cNotifiche USING fts5(ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato);”)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End TryTry
App.DBVirtual.ExecuteSQL(“INSERT INTO cNotifiche SELECT ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato FROM Notifiche;”)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End Try
And now on the second query i receive this error:
Error DB: no such table: Notifiche
Probably i need to execute the query from my real DB.
But when execute the second query:
App.DB.ExecuteSQL(“INSERT INTO cNotifiche SELECT ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato FROM Notifiche;”)
I receive this error:
Error DB: table cNotifiche has 6 columns but 11 values were supplied
Why cNotifiche has 6 columns if we’ve create a table with 11 values ?
This is the doc i’ve read:
https://documentation.xojo.com/topics/databases/supported_engines/sqlite/full_text_searching.html
Any hints ?
New mornin’ (at least here in Italy) episode 
I’ve found using DB Browser for SQLite that my database has in it (an old) cNotifiche virtual table (with 6 column) so somewhere/someday my App creates that table …
Now i’ve deleted the virtual table and create again (using DB Browser for SQLite) the virtual table, add data from realDB Notifiche and search for a record … it works like a charm …
CREATE VIRTUAL TABLE cNotifiche USING fts5(ID, Cronologico, Destinatario, Via, Civico, Cap, Citta, Provincia, Raccomandata, UfficioPostale, Giorno, Mese, Anno, Parte, Procuratore, UfficioGiudiziario, Pratica, Atto, Ticket);
INSERT INTO cNotifiche SELECT ID, Cronologico, Destinatario, Via, Civico, Cap, Citta, Provincia, Raccomandata, UfficioPostale, Giorno, Mese, Anno, Parte, Procuratore, UfficioGiudiziario, Pratica, Atto, Ticket FROM Notifiche;
SELECT * FROM cNotifiche WHERE cNotifiche MATCH ‘8*’;
BUT when execute the last query in my App
using: App.DBVirtual.ExecuteSQL and the query
i continue to receive:
Error DB: no such table: cNotifiche
Make sure (in the debugger) that the path to your DB is really leading to the correct DB file.
1 Like
Is it normal that here i receive a Nil (in the debugger) but no error when execute it ?
Here we’re talking about DBVirtual not the DB ‘real’ one.
Anyway are you referring to my DB i suppose?
No, there must be a reason for this. 
Found it!
I’ve missed the
App.DBVirtual.DatabaseFile = dbFile
Now it works (finally, thanks to all of you!)
for what i’ve read now to sync the virtual DB i need to add/delete/modify every time i add/delete/modify the real database, is it correct ?
No way to setup a trigger that automagically does it ?