[yunqa.de] DiSqlite3 - custom uppercase function problem

From: Clyde England <clyde@xxxxxxxx>
To: yunqa@xxxxxxxxxxxxx
Date: Fri, 8 Jan 2010 21:10:06 +0800

As the standard Sqlite upper() function does not support non ascii
characters I have created a custom function for this:

procedure Upper_Function_Callback(Context: sqlite3_context; nArgs: Integer;
Args: PPointerArray);
var
  Arg1, r: AnsiString;
begin
  { Retrieve the arguments as strings. }
  arg1 := sqlite3_value_str16(args[0]);

  r := ansiuppercase(Arg1);
  { Return the result. }
  sqlite3_result_text(Context, Pointer(r), Length(r), SQLITE_TRANSIENT);
end;

This all works very well and my custom uppercase function returns the
expected results. For example ö gets converted to Ö

However, I have run into a curious equality problem when using this function
when comparing literals in a query. Note: I am using Database.prepare16() to
prepare all my queries.

If I compare the results of the following two queries in Delphi code, as
expected they are equal:

Query 1  - Select CustomUpper('ö')
Query 2 -  Select 'Ö'

However, the following query returns 0 (false)

select CustomUpper('ö') = 'Ö'

Curiously the following query returns 1 (true)

select CustomUpper('ö') = CustomUpper('Ö')

Obviously I am missing somthing here, but what is it?

Thanks
Clyde

[yunqa.de] DiSqlite3 - custom uppercase function problem

Other related posts: