CODEWITHSUNDEEP
delphisortingdelphi-xe5firebird2.5firedac
cytrinox
cytrinox

Reputation: 1946

Descending sort in TFDTable results in duplicate rows

When using TFDTable in LDW mode, descending sort of fields results in duplicate rows. For example:

TFDTable.TableName is set to a VIEW name (not a tablename)

TFDTable.UpdateOptions.KeyFields is set to the primary key in the VIEW base table.

TFDTable.IndexFieldNames is set to 'PERS_NAME:A'.

shows a correct resultset without any duplicates. If I change IndexFieldnames to 'PERS_NAME:D', every record returned by the view is showed up two or tree times.

How to avoid these duplicates? It only happens when sorting the table in descending order, regardless of which field I use for sorting.

UPDATE: You can find a sample project to reproduce the problem on github: https://github.com/cytrinox/firedac-sort-issue or download the project via https://github.com/cytrinox/firedac-sort-issue/archive/master.zip

Upvotes: 5

Views: 3313

Answers (3)

Hector Adolfo Stivalet
Hector Adolfo Stivalet

Reputation: 11

This answer may be a little late but I was having the same problem yesterday and it could help somebody else in the future.

The problem is related by internal sorting of string type indexes.

The way we solve this was change this setting in the connection:

FDConnection.FormatOptions.SortLocale:= 0;

Best Regards

Upvotes: 1

Jacek Krawczyk
Jacek Krawczyk

Reputation: 2184

I had the same problem with the FireDAC and PostgreSQL. I decided to stay with the TFDTable, but I created a simple view instead.

For each table that you need to display, please create a view using simple SQL code:

CREATE VIEW my_view AS 
SELECT * FROM my_table;

Then you can select the my_view from the TFDTable.TableName list. The problem will not appeared.

Upvotes: 0

Bimmer_R
Bimmer_R

Reputation: 588

This problem will persist until we get other ways of handling TFDTable in LDW mode.

Definition of LDW - XE6

Using LDW with indexes based on anything other than numeric (ie. integers) will get you into all kinds of problems - if you don't obey by the rules.

I too spend hours trying to get LDW to work with indexed VARCHAR fields - but having my SQL statement use COLLATE (or collate the characterset of the field directly) would render my application useless. Meaning it would crash with the same error you described.

Here is the information I was given from the author of FireDAC.

There are 1001 way to make LDW failing due to concept. There is no sense and no ability to make client side sorting to be synchronized with server side sorting collation in all cases. IOW, when you are sorting TFDTable on a string field, it may fail in more cases when anyone can imagine. Two ways:

  • you are understanding why that happens and try to adjust FireDAC client side sorting, like described. I cannot provide all options - too many.
  • you are not understanding - then better do not sort TFDTable on string columns or even do not use TFDTable.

The paragraph above you can consider as a contract when you are allowed to use TFDTable and when not.

Personaly I would love to see LDW running without the need for internal sorting. If we could disable sorting and let it handle data pagination - that would be nice.

edit:

Come to think of it - try and use TFDQuery in stead of TFDTable. LDW is not supported by TFDQuery, and then you should be good to go.

Hope this helps you.

Upvotes: 2

Related Questions