Need to tweak my FTS4 SQLite query
The query below doesn't work on Android and throws the following exception
Unable to use function MATCH in the requested context
It is a pretty complicated query so I'm looking into ways of simplifying it. For info, this query works on the same database on my computer with the sqlite-jdbc-3.715 driver
SELECT * FROM stop_times WHERE departure_time >= 1000-125 AND departure_time <= 1000+180 AND stop_id IN (SELECT _id FROM stop_sequences WHERE stop_ids MATCH '"642," OR "642," OR ",642"') AND _id IN (SELECT _id FROM trips WHERE service_id IN (SELECT _id FROM calendar WHERE wednesday=1)) ORDER BY departure_time ASC
I think my FTS table declaration is OK:
CREATE VIRTUAL TABLE stop_sequences USING fts4( _id INTEGER PRIMARY KEY, stop_ids TEXT );
Could an SQL guru help me out? Thanks
EDIT: I found out that even the simplest query such as
SELECT _id FROM stop_sequences WHERE stop_ids MATCH '"642";
fails with the same error. I already use MATCH statements on another database somewhere else in my code and it doesn't complain at all. Does the context mentionned in the error message has anything to do with Android's Context?
When you created this table, you specified USING fts4(), right? If not, you're full-text queries will not work at all.