Search code examples
iossqlitefull-text-searchfmdb

Uknown error finalizing or resetting statement in sqlite

I'm trying to get our current database to use FTS. I recompiled the project with FTS3 and FTS4 support on. I know this part works since I've tried some of the examples off of the sqlite pages and they work.

e.g.

CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);

What I want to do is add a Virtual table to a .db that is already saved to the documents directory of our app. It is writeable.

My question is multi part. First, I tried my query in Firefox's add-on SQLite manager. My query is this:

CREATE VIRTUAL TABLE VirtualTestTable USING fts4(content=Chromosomes, ID Integer Primary Key Autoincrement, rsID, ChrID, Position, Strand, GeneSymbol)

Once I run this in sqlite manager, it creates a few different tables (VirtualTestTable, VirtualTestTable_docsize, VirtualTestTable_segdir, VirtualTestTable_segments, VirtualTestTable_stat).

So I assume sqlite manager supports FTS4. My table is also populated with the data that was original in my Chromosomes Table. But when I try to match anything in the table, I always get zero results. So my first question is, does sqlite manager support FTS queries?

My other question, which is more important to me, is if my query works in sqlite manager, I try this query in my Xcode project with different variations of 

content='Chromosomes'
content=\"Chromosomes\"
content=Chromosomes

I always get an error with my query. I'm using FMDB and using the executeUpdate: message. My error is:

Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR
Unknown error finalizing or resetting statement (1: unrecognized parameter: content=Chromosomes)

Does anyone know why I'm getting this error? I don't think it's a missing database as when I try to just create an empty Virtual table on my fmdatabase, it runs fine. Thanks.

Solution

  • FTS queries work just fine in SQL Manager:

    CREATE VIRTUAL TABLE t USING FTS4(x);
INSERT INTO t VALUES('blah');
INSERT INTO t VALUES('blubb');
SELECT * FROM t WHERE x MATCH 'bl*';

    The error message "unrecognized parameter: content=" is typical for SQLite versions that do not support contentless/external content tables, i.e., ones older than 3.7.11.