Page 1 of 1
Query, Filter on calculated field
Posted: 12.07.2018, 11:41
by vejrous
I am moving a project from different componets to ZeosLib and trying to filter query on calculated field. Is this suported?
Getting error: 'Column with index -1 is not accessible.'
Code: Select all
{ full number }
fldJobYearNumber.AsString := fldJobNumber.AsString + '/' + fldJobYear.AsString;
Code: Select all
aDataset.Filter := aFieldName + ' LIKE ' + QuotedStr('*' + aFilter + '*');
aDataset.FilterOptions := [foCaseInsensitive];
aDataset.Filtered := True;
Re: Query, Filter on calculated field
Posted: 12.07.2018, 12:47
by marsupilami
Hello vejrous,
we had a bugreport about this functionality. You need the latest revision of Zeos 7.2 from our Subversion repository on Sourceforge. See
https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/
If the problem still persists, please let me know.
Best regards,
Jan
Re: Query, Filter on calculated field
Posted: 12.07.2018, 13:17
by vejrous
I am using SVN at revision 4721:
http://svn.code.sf.net/p/zeoslib/code-0 ... .2-patches
If this is right svn path problem still persists.
Re: Query, Filter on calculated field
Posted: 19.07.2018, 09:39
by vejrous
Is there anything I can add to help solving this issue?
Re: Query, Filter on calculated field
Posted: 20.07.2018, 07:22
by marsupilami
Yes - you can
Having an example database script and example code to integrate into the test suites would be very much apreciated. This will be the next step for me to move this forward.
Re: Query, Filter on calculated field
Posted: 22.07.2018, 10:20
by vejrous
Hi,
I wrote SQL scripts to create and fill database and small Delphi application to show the problem.
DB connection parametres must be filled in code before connecting.
I found in the test cases that database "people" is used, but haven't found exact structure - I tried to ques as much as I can.
I haven't write DXUnit/FPTest before. If you can please let me know, when you create a test from attached application, I will look in the SVN and maybe can create whole test next time :-).
Hope this helps a little bit.
Re: Query, Filter on calculated field
Posted: 24.07.2018, 12:09
by marsupilami
Sooo - I created a test case. See revision 4775.
Re: Query, Filter on calculated field
Posted: 25.07.2018, 13:40
by vejrous
Thank You very much. I studied FPTest/DUnit2, writing my first test now :-)
Re: Query, Filter on calculated field
Posted: 25.07.2018, 18:41
by marsupilami
That's good to know. I can tell you that EgonHugeist is working on this. But it seems to be not simple...
Re: Query, Filter on calculated field
Posted: 26.07.2018, 05:18
by EgonHugeist
Hi,
Jan's implementation of the test did show another bug: adding a non persistent field, which isn't part of the select seems not to be supported by now. Fixing this bug was simple (not uploaded by now).
According filtering over an calculated Field:
In current state Zeos filters/sorts using the internal resultsets which are loads faster than using the TDataSet-Fields. Using such fields means running into the TDataSet bottleneck of ton's of events and notifications fired by moving the row position, and buffer copies from internals to TField.Data and back into the filter-stack.
If i change (add the fieldcalc condition) Jan's filter conditions to:
Code: Select all
Query.Filter := '(dep_name +'' ''+ dep_address) LIKE ' + QuotedStr('*Krasnodar*');
the test passes fine.
The question was if it is supported... Nope it isn't yet. We could add the support but the performance loss is high.
Do WE need to support that? I don't think so.
May i ask which components did you use before?
Solution 1: We do not support it, change the filter to emulate the calced field inside the filter. -> We just need to document it.
Solution 2: Change the code as described using the TField-values and live with the performance loss. Loads of code for filtering/sorting needs to be rewritten than. The code is untested and a breaking change.
Solution 3: Find a two way solution which increases the maintained code.
What you guy's thinking about?
I'd like to vote for point 1.
Re: Query, Filter on calculated field
Posted: 28.07.2018, 10:41
by marsupilami
Hello
EgonHugeist wrote:I'd like to vote for point 1.
Well -for me that definitly wil be my vote for Zeos 7.2 too. Everything else requires too much changes on the component layer to be considered a bug fix. This will go into the known problems section.
For Zeos 7.3 my vote is different. I would like to switch to the TField-approach. It is the more user friendly and more complete approach.
Best regards,
Jan