mORMot Open Source

oraclei

Member

Registered: 2016-06-06

Posts: 18

CreateAndFillPrepare and MySQL order by

I use CreateAndFillPrepare to query mysql, and I specified the order by clause like this:

resource := TResource.CreateAndFillPrepare(Client, 'order by convert(path using gbk),convert(operation using gbk)', []);

But the server report:

ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":2,"Message":"Error SQLITE_ERROR (1) [SELECT ID,path,operation,version FROM Resource order by convert(path using gbk),convert(operation using gbk)] using 3.13.0 - near \"using\": syntax error, extended_errcode=1"}

but I can run the sql  in mysql correctly:

SELECT ID,path,operation,version FROM Resource order by convert(path using gbk),convert(operation using gbk)

Is there a way to pass the complex order by clause to mysql ? Thanks a lot.

Last edited by oraclei (2016-10-11 12:20:00)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: CreateAndFillPrepare and MySQL order by

Please read the doc about SQlite3 virtual tables mechanism.

If you want such complex queries, you will need to run them directly on the SynDB connection to MySQL, and bypass the ORM.

oraclei

Member

Registered: 2016-06-06

Posts: 18

Re: CreateAndFillPrepare and MySQL order by

I create follow procedure:

procedure TableSort(table:TSQLTable; const FieldNames: array of RawUTF8);
var
    i,n: Integer;
    intptrs: array of PInteger;
    indexes: array of Integer;
    orders: array of boolean;
begin
    n := Length(fieldNames);
    SetLength(intptrs,n);
    SetLength(orders,n);
    SetLength(indexes,n);
    for i := Low(intptrs) to High(intptrs) do
    begin
        intptrs[i] := @indexes[i];
        orders[i] := True; //ASC
    end;
    table.FieldIndex(fieldNames,intptrs);
    table.SortFields(indexes,orders);
end;

and call it like this:

resource := TResource.CreateAndFillPrepare(Client, EmptyStr, []);
TableSort(resource.FillTable,['path','operation']);

It return result same as 'order by path,operation' but not sam as 'order by convert(path using gbk),convert(operation using gbk)';
Must  I write a interface-based service to implement my sorting ?

oraclei

Member

Registered: 2016-06-06

Posts: 18

Re: CreateAndFillPrepare and MySQL order by

@ab
I just find you answer, thank you.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: CreateAndFillPrepare and MySQL order by

I've added an optional CustomCompare[] parameter to TSQLTable.SortFields().
So that you may be able to use the sort function you expect.
See http://synopse.info/fossil/info/1c2ec3c144

Using the ORM then customize your TSQLTable is a good alternative, IMHO better than directly calling MySQL using the SynDB properties.
If you switch to another database, it will still continue to work.

If you always need to have a custom sort for all your text, you may overwrite the global SQLFieldTypeComp[sftUTF8Text] value, which points by default to AnsiIComp or UTF8IComp.