UTF sorting in sqlite databases

sandy771 · Post by **sandy771** » Wed 20 Oct 2010 08:30

Hi Alex

I am using Builder 2009 update 3
and UniDac Pro 3.00.0.11

AlexP · Post by **AlexP** » Wed 20 Oct 2010 10:28

hello,

I have created the new application in C++ Builder 2009 with UniDac Pro 3.00.0.11
and your code again works correctly.

I have tested it on the following query

SELECT * FROM test_table
order by test COLLATE SYSTEMNOCASE

where test is the NVARCHAR field

this is the code of my *.cpp file:

Code: Select all

//---------------------------------------------------------------------------

#include 
#pragma hdrstop

#include "Unit5.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "liteprovider120.lib"
#include "LiteClassesUni.hpp"
#include "LiteCallUni.hpp"
#pragma link "DBAccess"
#pragma link "MemDS"
#pragma link "Uni"
#pragma resource "*.dfm"
TForm5 *Form5;
//---------------------------------------------------------------------------
__fastcall TForm5::TForm5(TComponent* Owner)
	: TForm(Owner)
{
}

int __cdecl collation_callback(void* userdata, int l1, const void *s1, int l2, const void *s2)
{
WideString	w1, w2;

w1 = WideUpperCase(UTF8Decode((char*)s1));
w2 = WideUpperCase(UTF8Decode((char*)s2));
if(w1 > w2)
return 1;
else
if( w1 Open();
}
//---------------------------------------------------------------------------

note that you should register callback function before opening a query.

sandy771 · Post by **sandy771** » Thu 21 Oct 2010 12:35

Thanks Alex

All working now.

The problem was that I do not specify the database until run time so the following did the trick

UniConnection1->Database = SaveDialog1->FileName;
UniConnection1->Connected = true;
RegisterCollation(TUniUtils::GetCRConnection(UniConnection1), "SYSTEMNOCASE", SQLITE_UTF8, NULL, collation_callback);

executing RegisterCollation before connecting to the database generated the error.

sandy771 · Post by **sandy771** » Mon 27 Jun 2011 14:09

I have just upgraded to the lastest version of UniDac and my custom collation is now broken (my app fails to compile) with

[BCC32 Error] Unit1.cpp(2820): E2268 Call to undefined function 'RegisterCollation'

on line

RegisterCollation(TUniUtils::GetCRConnection(UniConnection1), "SYSTEMNOCASE", SQLITE_UTF8, NULL, collation_callback);

I have seen that this fucntion is now part of TLiteutils - but how do I access this?

AlexP · Post by **AlexP** » Wed 29 Jun 2011 09:55

Hello,

We've simplified the process of creating user collations, now you should use the following code to create them:

TLiteUtils::RegisterCollation(UniConnection1,SYSTEMNOCASE,collation_callback);

You can find more detailed information in the help file in the TLiteUtils.RegisterCollation Method topic.

sandy771 · Post by **sandy771** » Wed 07 Sep 2011 13:02

// RegisterCollation(TUniUtils::GetCRConnection(UniConnection1), "SYSTEMNOCASE",
SQLITE_UTF8, NULL, collation_callback);
TLiteUtils::RegisterCollation(UniConnection1,"SYSTEMNOCASE,collation_callback);

I have just changed my code from the commented out code above now that I have moved to Builder 2010 from 2009 (I am waiting for Xe3 for 64 bit

to your new format.

I get the following error messages

[BCC32 Error] Unit1.cpp(2735): E2034 Cannot convert 'int (*)(void *,int,const void *,int,const void *)' to 'int (_fastcall *)(UnicodeString,UnicodeString)'
[BCC32 Error] Unit1.cpp(2735): E2342 Type mismatch in parameter 'LiteCollation' (wanted 'int (_fastcall *)(UnicodeString,UnicodeString)', got 'int (*)(void *,int,const void *,int,const void *)')

AlexP · Post by **AlexP** » Wed 07 Sep 2011 14:21

Hello,

To resolve this issue, you need to change the collation_callback function in the following way:

Code: Select all

int __fastcall collation_callback(const String s1,const String s2)
{
WideString   w1, w2;

w1 = WideUpperCase(s1);
w2 = WideUpperCase(s2);
if(w1 > w2)
return 1;
else
if( w1 < w2)
return -1;
else
return 0;
}