mORMot Open Source

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Select distinct with a text type field

Hi, im trying to use Select distinct command, but i gotta problem.

To select distinct a text type field, it must be a Localized Collation.

When i create my Database, im using RawUTF8, and it makes my text type field, COLLATE SYSTEMNOCASE.

So i saw the SetCustomCollationForAllRawUTF8 procedure, and i wanna know the right  way to use it, and if this method will help me.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Select distinct with a text type field

You just define the collation name for a RawUTF8 field, or for all RawUTF8, by those SetCustomCollation*() methods.
The documentation is pretty clear about here.
See also http://synopse.info/forum/viewtopic.php?id=1873

Collation should be set BEFORE the table creation, of course!

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Re: Select distinct with a text type field

I saw a documentation about TSQLFieldType that says:
   " sftAnsiText, /// UTF-8 encoded TEXT: force a SYSTEMNOCASE collation ... "

But if i check in TSQLRecordProperties.SQLFieldTypeToSQL documentation, it says that:
   " ' TEXT COLLATE NOCASE, ',        // sftAnsiText"

so i changed my field type from RawUTF8 to AnsiString.

and if i use this command - Modelo.Props[TCentralCompraLog].Props.SQLFieldTypeToSQL(3) - i got a ' TEXT COLLATE NOCASE' mensage.

but it still creating a SYSTEMNOCASE field, in database.

so i tryed to change SQLFieldType to sftUnknown, cus it says: 
   " sftUnknown,  /// a WinAnsi encoded TEXT: force a NOCASE collation ... "

But it didnt create the field.

I read that post, but i couldnt understand very well.

I tried that SetCustomCollation* command, but it wasnt work to me.

Can u please show me an example of change Collation of a text type field ?

PS: i tried to follow that Example 12

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Re: Select distinct with a text type field

Before my TSQLModel.Create i set all Collation to NOCASE, EX:
   TCentralCompraLog.RecordProps.SetCustomCollationForAllRawUTF8('NOCASE');
   Modelo := TSQLModel.Create([TCentralCompraLog]);

and if i check the Modelo.Tables[0].GetSQLCreate(Modelo), i got this:
   'CREATE TABLE CentralCompraLog(ID INTEGER PRIMARY KEY AUTOINCREMENT, CodigoEmpresa INTEGER, CodigoTipoInformacao INTEGER, TipoIntegracao TEXT COLLATE NOCASE, Mensagem TEXT COLLATE NOCASE, CodigoTipoMensagem INTEGER, DataHora TEXT COLLATE ISO8601, Parametro TEXT COLLATE NOCASE, JSON TEXT COLLATE NOCASE, Emai INTEGER);'

All my text type got NOCASE collation, but when i look my fields at SQLiteExpert Personal 3, they r all SYSTEMNOCASE.

have u ever saw it before?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Select distinct with a text type field

I suspect your table was already created, with the previous parameters.

Collations are set at CREATE TABLE statement.

Please delete the database file, to force the new collation.

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Re: Select distinct with a text type field

i always delete my database,

here is my project,

https://drive.google.com/file/d/0B-qtxz … sp=sharing

Last edited by Victor Zanella (2014-10-13 18:55:08)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Select distinct with a text type field

Your code is using global variables, so is very difficult to follow.
Consider using functions as factory, e.g. for the model, as we use to show in our own samples.

The problem is in TBaseDeDados.Create.
Your are using NOCASE collation in the INTERNAL in-memory database via SetCustomCollationForAllRawUTF8(), as expected.
But since you are defining an EXTERNAL SQLite3 database via VirtualTableExternalRegisterAll(), it would use the default collation on this external DB.

Collations are not customizable for an external DB yet.
If you want to customize the CREATE TABLE statement for external databases, you need to write it by hand, before running the application.
For your special case, you can force to use "regular" collations, by adding this line:

  ConnectionProperties := TSQLDBSQLite3ConnectionProperties.Create('.\BaseDeDados\CentralCompra.db3', '', '', '');
  ConnectionProperties.UseMormotCollations := false;

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Re: Select distinct with a text type field

well, its worked.

Thanks for quick answer.