Wrong Sort by Using IndexFieldNames Property

[Andidreas]

Hey everyone...

i'm programming in Delphi and i'm using the UniDac Component for the connect to a SQLite File Database.

I select my Datas via a TUniQuery and Display the Result in a TDBGrid.
In the OnTitleClick Function of the TDBGrid i run a Sort of the TUniQuery with the IndexFieldName property.

Today i faced the following Issue...
In my SQL Statement i have an Group By and an SUM Function of a Integer Field.
When i now try to Sort my Results in the TUniQuery via the Field for which i'm using the SUM Function, the sequence is not correct!

For example i have the following Sequence
100
200
78
98

Has someone any idea why this happend?

[AlexP]

We cannot reproduce the problem. Please provide the table creating script and the SQL query you are using.

[Andidreas]

SQL Create Script:

Code: Select all

Create Table If Not Exists DATCOM
(COMMON varchar(2), COM_NO varchar(4), COM_KZ varchar(1), COMSTT int)

SQL Statement:

Code: Select all

Select COMMON, COM_NO, COM_KZ, SUM(COMSTT) as "Test" From DATCOM
Group By COMMON, COM_NO, COM_KZ

If i add "Order by Test" in a SQLite Browser, the Sort is Correct!

If i add in the OnTitleClick Event of my TDBGrid the Sort via IndexFieldNames, then the Sort is not correct!

If you need more Information, pls let me know...

[AlexP]

Hello,

We have checked sorting on your table - and when using both ASC and DESC sorting orders by the "Test" column, the results are correct,

Code: Select all

  if ASC then
    UniQuery1.IndexFieldNames := '"Test" ASC'
  else
    UniQuery1.IndexFieldNames := '"Test" DESC';

Please specify your UniDAC version (we tested on the latest UniDAC version 5.2.5 and Delphi XE), and send a sample demonstrating the problem, including the DB file, to alexp*devart*com

[Andidreas]

We're using Delphi XE and the UniDac Version 4.2.7

Is it possible for you to test your source again with this UniDac Version?
Or do you know if ther was a Bugfix who maybe relates to my Problem?

If you can not answer my question w/o a example, i can provide a example tomorrow...

[AlexP]

I still cannot reproduce the problem even on the UniDAC version you have specified. The following code sorts data correctly:

Code: Select all

var
    bol: boolean;

procedure TForm8.Button1Click(Sender: TObject);
begin
  bol := not bol;
  if bol then
    UniQuery1.IndexFieldNames := '"Test" ASC'
  else
    UniQuery1.IndexFieldNames := '"Test" DESC';
end;

procedure TForm8.FormCreate(Sender: TObject);
begin
  UniConnection1.Database := ':memory:';
  UniConnection1.Connect;
  UniConnection1.ExecSQL('Create Table If Not Exists DATCOM (COMMON varchar(2), COM_NO varchar(4), COM_KZ varchar(1), COMSTT int)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''12'',''1234'',''1'', 1)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''12'',''1234'',''1'', 2)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''13'',''1235'',''2'', 3)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''13'',''1235'',''2'', 4)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''14'',''1236'',''3'', 5)',[]);
  UniConnection1.ExecSQL('Insert into DATCOM VALUES(''14'',''1236'',''3'', 6)',[]);
  UniQuery1.Open;
end;

[Andidreas]

Sorry for my late reply...
Now i have an example application with DB for you!
Do you have an E-Mail Adress for me, to which i can send the example?

[AlexP]

You can send your example to alexp*devart*com .

[Andidreas]

I found a workaround for my Problem.

When I change the DataType for the Sort Field to "ftInteger" (by using the DataMapType.AddFieldNameRule Methode) the Sort works fine!

But I have still the problem that i can not assign Fields to the IndexFieldNames where have a blank in their Names! Please check!

[AlexP]

As I wrote earlier, we couldn't reproduce the problem and haven't received your sample.
If you are using fields with spaces, then, when using IndexFieldNames, the field name must be quoted

[Andidreas]

I allready tried the quotes!

I sent the example once again!

[AlexP]

Unfortunately, your letters come with no attachments. Please upload your sample to any file sharing service and send me the link to it.