DBGrid sorting by TitleClick - unicode
lazarus / 16-Databases

----------------------------------------------------------------------
DDDsa · 2015-03-05 09:55

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 · 2015-03-07 07:53

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 · 2015-03-07 08:53

Quote from: LacaK on March 07, 2015, 08:53:15 amEDIT: 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 · 2015-03-10 05:48

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 · 2015-03-10 06:51

Quote from: DDDsa on March 10, 2015, 06:48:38 amAdding "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 · 2015-03-10 06:53

Got it!
Thanx again

----------------------------------------------------------------------
totya · 2016-09-30 21:07

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM MYTABLE WHERE MYDATAFIELD LIKE "%searchedutf8text%" ORDER BY
MYDATAFIELD; 
This is possible anyhow, for example with CreateCollation? Because the
LIKE operator support only the ASCII chars.

Thanks!

----------------------------------------------------------------------
LacaK · 2016-10-01 18:44

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 · 2016-10-01 18:46

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.