Firedac, filters and case insensitivity

I have use other db components in delphi, but I am having a particular issue using firedac.
Using Delphi10 Seattle, Sql Server 2012.
I have a firedac table pointing to a dataset.
that dataset has 3 fields.


reg_id (integer)
id_css(varchar 8)
reg_value (varchar(250))

i have this table joined to another table using masterdetail, on the reg_ID
a particular record exists in the data set, and is shown in this query:

select * from dbregval where id_css = 'PSI99'
order by reg_id
(see pic1)
pic1.JPG
regID = 128000.
id_css = psi99
value string = '1'

in the program, I set the filter options to FOCaseInsensitve, foNoPartial
I have set this both at the component level and in code, before the table is opened.

when reg_id is 128006, a records is found (because id_CSS in db is PSI99)
(see file 128006.jpg)
128006.JPG
however, when reg_id is 128000, no record is returned in firedac
(dmCommon.tblUserReg.isEmpty is returning true)
(see file 128000.jpg)
128000.JPG
This is the sql as reported by firedac monitor, when I refresh the table:

SELECT A.*
FROM dbo.dbRegVal A
WHERE (A.reg_id = :reg_id) AND
(((A.reg_id = :FD__WN_reg_id) AND (A.id_css < :FD__WN_id_css)) OR ((A.reg_id < :FD__WN_reg_id))) AND
(id_css = 'PSI99')
ORDER BY A.reg_id DESC, A.id_css DESC

so, the question is : how do I use case insensitive filters, using firedac?
and why is this not working?  I'm new to firedac?
any idea what it is that I am missing?

(added test project source that demonstrates the issue)
fdTest.zip
Connie McBrideJust a simple programmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
An Integer does no have upper/lower cases

So I think it isn't that. Sorry.

var-is-integer.png
0
Geert GOracle dbaCommented:
by making the where clause case insensitive
and for performance reasons don't use the filter and use bind variables

the filter is a slow way of finding the right records.
but this seems the only way to do this with fd and a memory table

 i haven't installed tokio yet ...

but ... why have you got 2 conditions on id_css

AND (A.id_css < :FD__WN_id_css)
AND (id_css = 'PSI99')

to make it case insenstive you would do this:
AND uppercase(id_css) = uppercase(:search_value)
0
Connie McBrideJust a simple programmerAuthor Commented:
1) the master detail is linked on integer, the filter is on id_css.  the text that *I* put in is id_css = 'PSI99'.
2) the sql generated is generated BY FIREDAC, not by me.  Since in mssql studio, using the where clause of 'where id_css = 'PSI99' returns all records, regardless of case, I would assume that FIREDAC should also follow that.
0
Sinisa VukSoftware architectCommented:
I'm think that you new to database too. But, I run your demo and it works correctly with my Delphi 10.2.1. So must be a Firedac issue.
try following:
procedure TForm1.BitBtn1Click(Sender: TObject);
begin
   tblReg.Filter := 'reg_id = 128006';
   tblReg.Filtered := true;
   tblUserReg.Filter := 'Upper(id_css) = '+QuotedStr(UpperCase('PSI99'));
   tblUserReg.Filtered := true;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
   tblReg.Filter := 'reg_id = 128000';
   tblReg.Filtered := true;
   tblUserReg.Filter := 'Upper(id_css) = '+QuotedStr(UpperCase('PSI99'));
   tblUserReg.Filtered := true;

end;

Open in new window


I got one record on first button and one on second too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.