4

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

cytrinox
  • 1,846
  • 5
  • 25
  • 46
  • How do we reproduce this problem? Can you post an [MVCE](http://stackoverflow.com/help/mcve) so we can see what you're describing? – Ken White Dec 25 '14 at 01:10
  • Ken White: I've added a sample project to reproduce the problem. – cytrinox Dec 29 '14 at 09:52
  • Unfortunately, I can't get to github from behind our corporate proxy (which is just one of the several reasons your code should be here in your question instead of elsewhere). – Ken White Dec 30 '14 at 18:20
  • Even this is reduced to the minimal requirements to reproduce the problem, you need a database, sample data, a SQL VIEW, some components on the form etc.. I don't see an option to post all this files here on SO. It may be easier if you download the code from a location where you have uncensored access to the internet. – cytrinox Dec 30 '14 at 22:43
  • Easier for *you*, perhaps. Not much use to future readers of this site, though, when the external files are not available for some reason, or for me going through the extra work. It's not *my problem*, after all. The [help/on-topic] clearly states that questions asking for debugging help should provide the necessary code *here, in the question itself*. – Ken White Dec 30 '14 at 22:48
  • I've posted the files as github project to keep the availibility for SO. If SO wants everything on their page, they should implement an attachment function... I don't see any sense in posting dozends of lines from DFM files, project files etc. you need to c&p into your IDE. To reproduce the problem you can just use the the properties I've mention in my post earlier, but you have to build your own database and DFM files then. – cytrinox Dec 30 '14 at 22:55
  • There's no necessity for attachments. See [How to Create a Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve). You can provide a script for the DB and view, and copy/paste the necessary DFM as text into a code block. Github does not make the information available here. If you're not willing to exert the effort to write the question in a way that complies with the guidelines, you may have difficulty getting answers (even by trying to use a bounty to avoid doing so). Others manage to provide the information here. – Ken White Dec 30 '14 at 23:06
  • Any FDTable component connected to any view in some of your projects should be enough the reproduce the problem with the three properties I've mentioned. Even more, I've provided a fully working example project with everything someone needs. If you can't work with these many information, then don't try to answer the question. Thats ok. But I don't post any large, useless DFM files here just because you have requested it. A full working project is much more helpful for someone who wants to reproduce the problem. – cytrinox Dec 30 '14 at 23:21
  • If the problem is *that simple to reproduce*, then you shouldn't need to post a *large, useless DFM file*, right? Code here to allow future users to see it is more helpful to others, which is what this site is meant to do. We're not here to solve your immediate problem for you; this site is to collect knowledge that can be used by many people, and if it solves one specific problem in the process that's a great side benefit. No worries, though. I'll move on, and you can wait and hope someone helps you. Good luck. – Ken White Dec 30 '14 at 23:25
  • Yes, it is simple to reproduce with just the information from the first post. But instead of trying it, you have requested more details and I've provided a full working project for you. Thats all... – cytrinox Dec 30 '14 at 23:29
  • For about the 10th time, what I've requested is **more information here, in the question itself**, which is what is required by this site's guidelines. That's all. It's not simple to reproduce with just the information from the first post, because nobody here has your database, sample data, or view. But as I said, I'll move on, and once again good luck. – Ken White Dec 30 '14 at 23:32
  • I've tried it with different Firebird-2.5 databases, different encodings and primary key types (int, char etc.) I'm sure you can reproduce this issue with every database you could find on your host. Thats why I have not provided any more information in the first post because it *is* easy to reproduce. – cytrinox Dec 30 '14 at 23:38
  • I'm not that experienced with delphi, will using TFDTable.Sort not resolve the problem? – Bayeni Jan 06 '15 at 10:14
  • TFDTable has no Sort property. Sorting in FireDAC is done via IndexFields property. – cytrinox Jan 07 '15 at 08:36

3 Answers3

2

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.

Bimmer_R
  • 588
  • 9
  • 18
  • I accept that different client/server sortings can cause errors, but the problem also happens on just example varchar fields containing A, B, C or D as string. That's why I insist that it's very easy to reproduce and has no relation to differences between sorting algorithms. – cytrinox Mar 24 '15 at 14:55
1

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

Hector Adolfo Stivalet
  • 11
  • 1
  • 6
0

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.

Jacek Krawczyk
  • 2,083
  • 1
  • 19
  • 25