I must be missing something obvious here but I can’t find a way to do a case-insensitive table filtering. I’m using DA with AnyDac and a SQLite database. RO SDK version is 6.0.51.881 - AnyDac version is 4.0.7 build 1723.
First try: I thought I could use the UpperCase macro but I’m getting an exception in class EDAEvaluationException with message ‘Syntax error’. Why ?
So I thought I’d use DynamicWhere, which I find very annoying to use: close table, long and hard to read syntax, open table, get the desired record info, close the table again, clear the DynamicWhere, and finally open it again… Oo
But that time I have another error from AnyDac saying “exception class EADException with message ‘[AnyDAC][Phys]-327. Escape function [UPPERCASE] is not supported’.”
with tbl_keywords do
begin
Close;
DynamicWhere.Clear;
DynamicWhere.Expression :=
DynamicWhere.NewBinaryExpression(
DynamicWhere.NewBinaryExpression(
DynamicWhere.NewField('', fld_keywordsidparent),
DynamicWhere.NewConstant(aCurrentKeyword.idparent, datString),
dboEqual),
DynamicWhere.NewBinaryExpression(
DynamicWhere.NewMacro(format('UpperCase(%s)', [fld_keywordscaption])),
DynamicWhere.NewConstant(UpperCase(aCurrentKeyword.caption), datString),
dboEqual),
dboAnd
);
Open();
end;
So which method should I use to accomplish such a task ?
Thanks for any help!
Sorry for delay. Looks like macro support is broken for AnyDac SQLite driver. This was logged as 47514. Meanwhile you can use OnUnknownMacroVariable event handle of your service data module to fix this:
function TNewService.DataAbstractServiceUnknownMacroVariable(Sender: TObject;
const Name, OrgName: string; var Value: string): Boolean;
begin
if SameText(Name, ‘UpperCase’) then Value := ‘UPPER’;
end;
Thanks Slavad, I’ll try that and let you know. But what about the DAEvaluationException when I use the Macro in the filter ? Can I do that instead as I find it more readable ?
Can you let us know if this happens again? I changed the settings for this but can't find a clear cause.
I didn’t receive anything for both of your answers (mh and ck). I’ve checked my e-mail setting and spam folder, this is not the problem.
I’ll keep checking this thread manually for the issue I reported and let you know if an answer is posted without e-mail notification.
I’ve contacted AnyDac (excellent) support and they say that I needed to use UCASE instead of UpperCase in DynamicWhere macro => Now this part is working and the OnUnknownMacroVariable is fired correctly. This looks like a bug in DA where the OnUnknownMacroVariable is not always fired…
But this is really slow as I need to do this operation many times to get the next record in a table. So if there is no way of using a macro in the filter, would it be possible to case-insensitive sort a TDAMemDataTable ?
Thanks, this helps.
Can I make a feature request and ask that Macros are handled in the filter properties for memory data tables ? This would be of great help.
@mh@ck: Still not received any e-mail from replies on that thread. Also I find the home page with all discussions a little too colorful with too much information and tags too “catchy” on the eye.
@mh @ck: Still not received any e-mail from replies on that thread. Also I find the home page with all discussions a little too colorful with too much information and tags too "catchy" on the eye.
Previous mails, or new replies you should have gotten an email for?
Thanks, this helps.
Can I make a feature request and ask that Macros are handled in the filter properties for memory data tables ? This would be of great help.
