DBGrid sorting by TitleClick - unicode

[DDDsa]

Hello! I'm using Lazarus 1.2.6 and sqlite DB.
I've used the approach of sorting a DataSet by DBGridTitleClick described in the wiki article, but it works only with latin characters.

For example the sorting for russian alphabet should be like:
а
б
в
г
д
е
ж
з
и

But here is what I get:
pic

While the english letters are sorting well:
pic
Is there any way to fix this?

BTW: If i add "ORDER BY xxx" in the SQL.Text  I get the right sorting, so the problem is not in sqlite I suppose

[LacaK]

I assume you use UTF-8 encoding in your SQLite3 database.

Then you can use this method: TSQLite3Connection.CreateCollation

SQLite3DB.CreateCollation('UTF8', 0, nil, nil);

Then you must use in your SQL:
  ORDER BY a COLLATE UTF8

http://www.sqlite.org/datatype3.html#collation
http://www.sqlite.org/c3ref/create_collation.html

EDIT: ah now I catch, that you are not using SQL order by, but local indexes of TSQLQuery. Then IMO it is problem, because local sorting expects strings encoded in ANSI CP and I assume you have strings in UTF-8.

[taazz]

EDIT: ah now I catch, that you are not using SQL order by, but local indexes of TSQLQuery. Then IMO it is problem, because local sorting expects strings encoded in ANSI CP and I assume you have strings in UTF-8.

Although this should answered his question adding indexies in a query creates local indexies and sort depends on ascii table. I have to add that even if the local indexes where utf8 aware the compare is a simple ordinal comparison algorithm which probably has nothing to do with the collation comparison the sql server does.

[DDDsa]

So there's no hope to do it the easy way?
Adding "ORDER BY" to sqlite query by DBGirdTitleClick is ugly but seems to be the only way to reach the right sorting?..

LacaK, taazz, Thanks for your help anyway!

[LacaK]

Adding "ORDER BY" to sqlite query by DBGirdTitleClick is ugly but seems to be the only way to reach the right sorting?..

ATM it is IMO only solution to add ORDER BY with COLLATE clause

[DDDsa]

Got it!
Thanx again

[totya]

Hi!

My problem is similar. I want to search in SQLite3 database:  in UTF8 texts but case insensitive, like as this command:

Code: Pascal  [Select][+][-]

1. SELECT * FROM MYTABLE WHERE MYDATAFIELD LIKE "%searchedutf8text%" ORDER BY MYDATAFIELD;
2.  

This is possible anyhow, for example with CreateCollation? Because the LIKE operator support only the ASCII chars.

Thanks!

[LacaK]

By default SQLite LIKE is case-insensitive for ASCII characters, but case-sensitive for characters beyond ASCII.
And yes you must create own collation using wither TSQLite3Connection.CreateCollation or directly using SQLite3 API sqlite3_create_collation() https://www.sqlite.org/c3ref/create_collation.html

[totya]

Hi!

Thanks for the information, can you show me examples for the TSQLite3Connection.CreateCollation /or SQLite3 API sqlite3_create_collation()/ from Lazarus?

Edit:
I create new topic for this different problem.