• Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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)
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)
however, when reg_id is 128000, no record is returned in firedac
(dmCommon.tblUserReg.isEmpty is returning true)
(see file 128000.jpg)
This is the sql as reported by firedac monitor, when I refresh the table:

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)
Connie McBride
Connie McBride
An Integer does no have upper/lower cases

So I think it isn't that. Sorry.

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)
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.
Sinisa VukCommented:
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);
   tblReg.Filter := 'reg_id = 128006';
   tblReg.Filtered := true;
   tblUserReg.Filter := 'Upper(id_css) = '+QuotedStr(UpperCase('PSI99'));
   tblUserReg.Filtered := true;

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


Open in new window

I got one record on first button and one on second too.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now