3

Why when i write a query with fulltext Search syntax like:

SELECT * FROM TABLENAME WHERE TABLENAME MATCH 'ColumnA:word1 OR ColumnB:word2'

The query result always return 0 rows?

I'm using VBnet and the latest Ado.net provider from sqlite.org

The problem is when i try the query with and external tool, it works well (with sqlite 3.7.9).

Any clues? Thanks in advance

Petr Abdulin
  • 33,883
  • 9
  • 62
  • 96
popa big
  • 31
  • 1
  • 2

1 Answers1

4

Although System.Data.Sqlite is compiled using SQLITE_ENABLE_FTS3 and supports FTS, it doesn't compiled with SQLITE_ENABLE_FTS3_PARENTHESIS which enables Enhanced Query Syntax. And you are using Enhanced Query Syntax in your query, thus it didn't works as you expect.

You need to either modify you query, or do a custom build (follow build procedures) of System.Data.Sqlite with SQLITE_ENABLE_FTS3_PARENTHESIS defined.

Petr Abdulin
  • 33,883
  • 9
  • 62
  • 96
  • Thanks for your answer but i cannot build the mixed assembly version, i have visual studio 2008 pro. I get the error: Error 9 fatal error LNK1181: cannot open input file 'c:\Documents.obj' SQLite.Interop.Static.2008 SQLite.Interop.Static.2008 – popa big Apr 03 '12 at 09:03
  • follow build procedures: http://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki – Petr Abdulin Apr 03 '12 at 09:29
  • Thanks petr!!I can compil but even if i change the macro by this i still hav mis interpretation of my enhanced query: I added the code : – popa big Apr 03 '12 at 09:37
  • Can't help you there. Just be very careful, and be sure you modified build correctly in all requred places. – Petr Abdulin Apr 03 '12 at 10:15
  • Thanks anyway, i can't add a vote since it's my first question. take care man!! – popa big Apr 03 '12 at 10:19
  • I'm a Java developer, was always thinking that Java is the worst mess but .NET seems to top it ;) Anyway, I have no idea and time to recompile stuff I don't know, so what about this "modify you query"? I just want to use a simple performant full text search, 'CREATE VIRTUAL TABLE IF NOT EXISTS fullText USING fts4 (mediaID TEXT PRIMARY KEY, fullText TEXT)' I would be able to do anywhere in SQLite, can you give me a hint how to do it using .NET? – F___ThisToxicCommunityHere Nov 07 '22 at 11:45