On 08/05/2011 05:44 PM, Martin Gill wrote:
> On 5 August 2011 10:55, Dan Kennedy<danielk1...@gmail.com> wrote:
>>
>> On 08/05/2011 02:58 PM, Martin Gill wrote:
>>>
>>> CREATE VIRTUAL TABLE full_text USING FTS3 (id,text)
>>> INSERT INTO full_text VALUES ('1', 'I have a foréign character.')
>>
>> SQLite does not recognize upper/lower-case mappings for non-ASCII
>> characters. That is why querying for "HAVE" works but "FORÉIGN"
>> does not. If you need upper/lower case mapping for non-ASCII characters,
>> you will need to use the ICU extension.
>>
>
> That may be part of the problem. System.Data.SQlite isn't compiled with ICU,
> so
> I cannot check that quickly. I do though get exactly the same odd
> behaviour using
> the porter tokenizer.
>
>>> SELECT id, text FROM full_text WHERE NOT(text = @p1) AND text MATCH @p2
>>>
>>> p1 = "foréign"
>>> p2 = "FORÉIGN"
>>>
>>> Not using parametrized SQL also returns a correct result.
>>
>> I don't see how either the query above or a version that uses SQL
>> literals could be returning any rows.. Not with the "CREATE VIRTUAL
>> TABLE" as it stands above. Unless you specify the ICU tokenizer (or
>> some other tokenizer that understands non-ASCII upper/lower case),
>> "FORÉIGN" should not match the row in table "full_text".
>>
> SQLiteParameter param1 = command.CreateParameter();
> param1.Value = "FORÉIGN";
> param1.ParameterName = "p1";
> param1.DbType = System.Data.DbType.String;
> command.Parameters.Add(param1);
>
> SQLiteParameter param2 = command.CreateParameter();
> param2.Value = "foréign";
> param2.ParameterName = "p2";
> param2.DbType = System.Data.DbType.String;
> command.Parameters.Add(param2);
p2 is lower-case here, so it matches. If the upper-case value were
bound to p2, it would not.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
