Problem with SQLite prepared statement

General
1

I’ve got a simple SQLite database:

CREATE VIRTUAL TABLE bodyindex USING fts4(content='', messagebody);

which I use for searching. Searching itself works fine with the following SQL:

SELECT docid FROM bodyindex WHERE messagebody MATCH '*" + SearchString + "*'

But the prepared statement doesn’t give any result:

PreparedForSearch = SQLiteIndexDB.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH '*?*'") PreparedForSearch.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) dim data as RecordSet = PreparedForSearch.SQLSelect(SearchString)

data has 0 records for all searches. What am I doing wrong here? Xojo 2017r1, macOS El Capitan.

2

Don’t the asterisks have to be in the bind rather than the statement?

3

Sounds reasonable…

Conceptually, if the ? is replaced by a quoted string (I know that’s not quite what happens, but…)

You would get a result like

whereas if you put it in the bind:

PreparedForSearch = SQLiteIndexDB.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH ?) PreparedForSearch.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) dim data as RecordSet = PreparedForSearch.SQLSelect('*' + searchstring +'*')

it should end up as

[quote]
FROM bodyindex WHERE messagebody MATCH 'banana"[/quote]

4

Thanks, Jeff. That was it.