mORMot Open Source

array81

Member

From: Italy

Registered: 2010-07-23

Posts: 411

Record Collate

Hi,

When I try to use this SQL:

SELECT Projects.ID, Projects.Title, Projects.RecordCreated, Projects.RecordModified FROM Projects INNER JOIN ProjectsUsers ON Projects.ID = ProjectsUsers.IDProject INNER JOIN Users ON ProjectsUsers.IDUser = Users.ID , Contacts INNER JOIN ProjectsCustomers ON Projects.ID = ProjectsCustomers.IDProject AND ProjectsCustomers.IDCustomer = Contacts.ID WHERE (Projects.Title='aaaa')

I receive this error:

no such collation sequence: SYSTEMNOCASE

I think because there is COLLATE of my text record.

  TSQLProjects = class(TSQLRecord)
  private
    fRecordCreated: TCreateTime;
    fRecordModified: TModTime;
    fTitle: RawUTF8;
    fKind: RawUTF8;
    fFiles: RawUTF8;
    fDateFrom: TDateTime;
    fDateTo: TDateTime;
    fStatus: Integer;
    fNote: RawUTF8;
  published
    property RecordCreated: TCreateTime read fRecordCreated write fRecordCreated;
    property RecordModified: TModTime read fRecordModified write fRecordModified;
    property Title: RawUTF8 read fTitle write fTitle;
    property Kind: RawUTF8 read fKind write fKind;
    property Files: RawUTF8 read fFiles write fFiles;
    property DateFrom: TDateTime read fDateFrom write fDateFrom;
    property DateTo: TDateTime read fDateTo write fDateTo;
    property Status: Integer read fStatus write fStatus;
    property Note: RawUTF8 read fNote write fNote;
  end;

Is there a way to get COLLATE for each record (text record) of my table?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Record Collate

Where do you run this SQL query?

I guess this is outside of the framework.

In this case, this collation is not known by this external tool.

You should better use our SynDBExplorer tool to access the SQLite3 database file - it will know all collations and functions defined by default by our framework.

array81

Member

From: Italy

Registered: 2010-07-23

Posts: 411

Re: Record Collate

The problem is this, if I run sql on my application by this code:

TabellaRisultati := Database.ExecuteList([], sql);

I have 0 result, same result with your SynDBExplorer, but inside database there are 2 record of Projects table with Title field="aaaa".
So I have try to run sql with 2 SQLite editor and I have received this error:

no such collation sequence: SYSTEMNOCASE

Any idea? Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Record Collate

This collation is created in

function TSQLDataBase.DBOpen: integer;
 (...)
  // the SQLite3 standard NOCASE collation is used for AnsiString and is very fast
  // our custom fast UTF-8 case insensitive compare, using NormToUpper[] for all 8 bits values
  sqlite3_create_collation(DB,'SYSTEMNOCASE',SQLITE_UTF8,nil,Utf8SQLCompNoCase);
 (...)

So the only reason why it may be not existing should be of a "manual" DB opening, without TSQLDataBase.DBOpen.

array81

Member

From: Italy

Registered: 2010-07-23

Posts: 411

Re: Record Collate

I use this code to create or open my database:

Database := TSQLRestClientDB.Create(Model, nil, Filename, TSQLRestServerDB);

DatabaseServer := TSQLRestClientDB(Database).Server;
DatabaseServer.CreateMissingTables(0);
DatabaseServer.OnUpdateEvent := DatabaseAggiornatoEvento;
Server := TSQLite3HttpServer.Create(IntToStr(Port), DatabaseServer);

With this code I didn't have problem without now. Is not right?

array81

Member

From: Italy

Registered: 2010-07-23

Posts: 411

Re: Record Collate

OK I think I have found the problem. SQL is incorrect.