(SOLVED) dbf.filter property question
lazarus / 16-Databases

----------------------------------------------------------------------
bill0287 · 2013-12-04 03:55

This code works okay:

Code: [Select]Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text);
Where NAME is a Field name in the dbf.

I am trying to use the value of a Combobox in place of a hardcoded
field value, but no expression syntax I have tried works. The
tutorials don't address this situation. Something like this (which
doesn't work):

Code: [Select]Filter := 'FieldComboBox.Text=' +
QuotedStr(DBLookupcomboBox1.Text);
Anyone have any suggestions of what might work?

UPDATE:

Solution provided below.

----------------------------------------------------------------------
engkin · 2013-12-04 06:08

Code: [Select]Filter := FieldComboBox.Text + '=' +
QuotedStr(DBLookupcomboBox1.Text);

----------------------------------------------------------------------
bill0287 · 2013-12-04 06:43

That did it...thanks so much!

This forum is very helpful...

----------------------------------------------------------------------
bill0287 · 2013-12-17 05:52

I noticed that this does not work if there is an apostrophe in the
field data... for example:

Tom's Bakery

The error is something like: Missing operator between "Tom" and "Tom".

Is there any way to modify this code so as to preserve the data with
the apostrophes?

----------------------------------------------------------------------
BigChimp · 2013-12-17 09:55

Congratulations bill, I think you just hit another TDBF bug

I suspect the problem may be located in dbf_prscore (code as from fpc
trunk, probably same on 2.6.x):
Code: [Select]procedure
TCustomExpressionParser.ParseString(AnExpression: string;
DestCollection: TExprCollection);
...
  procedure ReadWord(AnExpr: string);
...
        case AnExpr[I2] of
          '''', '"':
            begin
              isConstant := true;
              constChar := AnExpr[I2];
              Inc(I2);
              while (I2 <= Len) and (AnExpr[I2] <> constChar) do
                Inc(I2);
              if I2 <= Len then
                Inc(I2);
            end; 
which seems to just scan for opening and closing ' or " and does not
take e.g. escaped '' or "" (or perhaps \' or \" or whatever escape
mechanisms you want) into account to allow filter strings that contain
the offending quote character...

Couldn't find any mention in the docs what the preferred escaping
mechanism should be... nor any mention of strings to be surrounded by
single/double quotes actually. Ah well, it's open source.

I'll write up a test case if not already present in
http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/tests/
and see what can be done. Will post bug tracker item when I have one.

----------------------------------------------------------------------
BigChimp · 2013-12-17 10:07

Quote from: bill0287 on December 17, 2013, 06:52:18 amI noticed that
this does not work if there is an apostrophe in the field data... for
example:

Tom's Bakery
Oh, a workaround if you only expect EITHER ' or " in your string (but
warns you if both are there), something like (air code, uncompiled,
untested, may shrink your dog, probably messed up some ;s etc):
Code: [Select]var
  QuoteStr: string;
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
//quick and dirty:
if (pos('"',UserLooksFor)>0) and pos('"',UserLooksFor)>0) then
begin
  ShowMessage('cannot filter on both single and double quotes. sorry.
please fix tdbf code.');
  exit;
end
else if pos('"',UserLooksFor)>0 then {look for single quote}
  QuoteStr:='"' {double quote} //edit: argh, I had originally put in a
single quote. sorry
else if pos('"',UserLooksFor)>0 then {look for double quote}
  QuoteStr:='''' {single quote};

Filter := FieldComboBox.Text + '=' +
AnsiQuotedStr(UserLooksFor,QuoteStr);

Edit: argh, code was obviously wrong... sorry, getting tired here I
suppose...

----------------------------------------------------------------------
BigChimp · 2013-12-17 13:06

Bug report
http://bugs.freepascal.org/view.php?id=25432

----------------------------------------------------------------------
BigChimp · 2013-12-17 16:33

Updated post with code. There was a stupid error in it. Probably more
of them. Let's just say I'm really glad FPC catches a lot of errors at
compile time

----------------------------------------------------------------------
bill0287 · 2013-12-17 20:06

With a few minor tweaks I was able to get this to work for strings
with either a singlequote, or doublequote present...the error trap
also worked for when both type were present.

Code: [Select]var
  QuoteStr: char; //edit2: changed to char
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
  //quick and dirty:
  if (pos('"',UserLooksFor)>0) and (pos('''',UserLooksFor)>0) then
//edit2: fixed parens and quotes in second pos statement
    begin
       ShowMessage('cannot filter on both single and double quotes.
sorry. please fix tdbf code.');
       exit;
     end
   else if pos('''',UserLooksFor)>0 then {look for single quote}
//edit2: fixed quotes
     QuoteStr:='"' {double quote} //edit: argh, I had originally put
in a single quote. sorry
   else if pos('"',UserLooksFor)>0 then {look for double quote}
     QuoteStr:='''' {single quote};
   DBf1.Filter := 'LAST=' +
AnsiQuotedStr(DBLookupcomboBox1.Text,QuoteStr);
   DBF1.Filtered:= true; 
The only problem is that it fails on values that don't have any quotes
in them.

On a very simple level, this statement works for data where there is
only a singlequote present:
Code: [Select]Filter := FieldComboBox.Text + '=' +
AnsiQuotedSTR(DBLookupcomboBox1.Text,'"')

----------------------------------------------------------------------
engkin · 2013-12-17 20:37

QuoteThe only problem is that it fails on values that don't have any
quotes in them.

Missing a final else, add:

Code: [Select]   else
     QuoteStr:=UserLooksFor;

at the end of the if chain:

Code: [Select]var
  QuoteStr: char; //edit2: changed to char
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
  //quick and dirty:
  if (pos('"',UserLooksFor)>0) and (pos('''',UserLooksFor)>0) then
//edit2: fixed parens and quotes in second pos statement
    begin
       ShowMessage('cannot filter on both single and double quotes.
sorry. please fix tdbf code.');
       exit;
     end
   else if pos('''',UserLooksFor)>0 then {look for single quote}
//edit2: fixed quotes
     QuoteStr:='"' {double quote} //edit: argh, I had originally put
in a single quote. sorry
   else if pos('"',UserLooksFor)>0 then {look for double quote}
     QuoteStr:='''' {single quote}
   else
     QuoteStr:=UserLooksFor;

   DBf1.Filter := 'LAST=' +
AnsiQuotedStr(DBLookupcomboBox1.Text,QuoteStr);
   DBF1.Filtered:= true;

That should make it work.

----------------------------------------------------------------------
bill0287 · 2013-12-17 21:52

It doesn't like that statement. Got error:

Error: Incompatible types: got "AnsiString" expected "Char"

I changed the variable QuoteStr to CHAR so it would pass the
AnsiQuotedSTR function, so I don't know if that was the right thing to
do.

Attached is my small test project if that helps.

----------------------------------------------------------------------
engkin · 2013-12-17 22:13

My previous post was just a disaster. Here is the code:

Code: [Select]procedure TForm1.FilterBtnClick(Sender: TObject);
var
  QuoteStr: char; //edit2: changed to char
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
  //quick and dirty:
  if (pos('"',UserLooksFor)>0) and (pos('''',UserLooksFor)>0) then
//edit2: fixed parens and quotes in second pos statement
    begin
       ShowMessage('cannot filter on both single and double quotes.
sorry. please fix tdbf code.');
       exit;
     end
   else if pos('''',UserLooksFor)>0 then {look for single quote}
//edit2: fixed quotes
     QuoteStr :='"' {double quote} //edit: argh, I had originally put
in a single quote. sorry
   else if pos('"',UserLooksFor)>0 then {look for double quote}
     QuoteStr :='''' {single quote}
   else
     QuoteStr := #0;

   if QuoteStr <> #0 then
     DBf1.Filter := 'LAST=' +
AnsiQuotedStr(DBLookupcomboBox1.Text,QuoteStr)
   else
     DBf1.Filter := 'LAST=' + DBLookupcomboBox1.Text;
   DBF1.Filtered:= true;
end;

----------------------------------------------------------------------
bill0287 · 2013-12-18 00:03

Once I added the small change shown below, everything worked:

QuotedStr(DBLookupcomboBox1.Text),

Code: [Select]procedure TForm1.FilterBtnClick(Sender: TObject);
var
  QuoteStr: char; //edit2: changed to char
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
  //quick and dirty:
  if (pos('"',UserLooksFor)>0) and (pos('''',UserLooksFor)>0) then
//edit2: fixed parens and quotes in second pos statement
    begin
       ShowMessage('cannot filter on both single and double quotes.
sorry. please fix tdbf code.');
       exit;
     end
   else if pos('''',UserLooksFor)>0 then {look for single quote}
//edit2: fixed quotes
     QuoteStr :='"' {double quote} //edit: argh, I had originally put
in a single quote. sorry
   else if pos('"',UserLooksFor)>0 then {look for double quote}
     QuoteStr :='''' {single quote}
   else
     QuoteStr := #0;

   if QuoteStr <> #0 then
     DBf1.Filter := 'LAST=' +
AnsiQuotedStr(DBLookupcomboBox1.Text,QuoteStr)
   else
     DBf1.Filter := 'LAST=' + QuotedStr(DBLookupcomboBox1.Text);
//edit: had to add QuotedStr
   DBF1.Filtered:= true;
end;

----------------------------------------------------------------------
BigChimp · 2013-12-19 11:36

Nice - thanks for fixing that code.

I'm a bit hesitant to throw another of my untested snippets out, but
it's the thought that counts

Quoting the string in the expression, so you could also rewrite it as:
procedure TForm1.FilterBtnClick(Sender: TObject);
Code: [Select]var
  QuoteStr: char; //edit2: changed to char
  UserLooksFor: string;
begin
  UserLooksFor:=DBLookupcomboBox1.Text;
  //quick and dirty:
  if (pos('"',UserLooksFor)>0) and (pos('''',UserLooksFor)>0) then
  begin
     ShowMessage('cannot filter on both single and double quotes.
sorry. please fix tdbf code.');
     exit;
   end
   else if pos('''',UserLooksFor)>0 then {look for single quote}
     QuoteStr :='"' {double quote}
   else {no single quotes in expression; safe to use single quotes}
     QuoteStr :='''' {single quote};

   DBf1.Filter := 'LAST=' +
AnsiQuotedStr(DBLookupcomboBox1.Text,QuoteStr);
   DBF1.Filtered:= true;
end;

----------------------------------------------------------------------
bill0287 · 2013-12-19 16:00

BigChimp

So once the above bug is fixed does that mean all this quote checking
will be unnecessary ?

----------------------------------------------------------------------
BigChimp · 2013-12-19 16:15

Well, you will still need to surround the field with quotes and double
up any quotes inside, Engkin's solution should be sufficient:
Code: [Select]Filter := FieldComboBox.Text + '=' +
QuotedStr(DBLookupcomboBox1.Text);
... or you can force your users not to use ' characters in their
database records, see how they like that

Also note that that fix is for current FPC trunk and will not appear
in a 2.6.x release unless it is backported. Probably too late for
FPC2.6.4

----------------------------------------------------------------------
BigChimp · 2013-12-19 16:51

Quote from: bill0287 on December 19, 2013, 05:00:24 pmSo once the
above bug is fixed does that mean all this quote checking will be
unnecessary ?
Your wish is my command as long as you're using FPC trunk
http://svn.freepascal.org/cgi-
bin/viewvc.cgi?view=revision&revision=26253

----------------------------------------------------------------------
bill0287 · 2013-12-19 19:42

I used fpcup and downloaded the latest trunk. quick test showed it
worked for:

1. No quotes
2. Double quotes

Did not work for

1. single quotes

Unless I misunderstood what you meant...which is very possible.

attached project I used, with this line of code:

Code: [Select]  DBf1.Filter := 'LAST=' +
QuotedStr(DBLookupcomboBox1.Text);
  DBF1.Filtered:= true;

----------------------------------------------------------------------
BigChimp · 2013-12-20 11:59

Laz trunk r43509 FPC x64 r26253 Windows
Works just fine for me:
1. I add a surname 'Tiny' Thompson
2. The combobox lets me filter on that
3. It shows 'Tiny' Thompson
??!?
Perhaps you could explain the problem again, as I might be the one
misunderstanding...

Attached zip with
- dbf containing friend thompson
- fix for having dbf in current application directory instead of
hardwired to a certain directory

----------------------------------------------------------------------
bill0287 · 2013-12-20 16:34

This file didn't work for me, but I highly doubt if it's you...I'm
sure it's me. 

I am not sure I have the right trunk...how can I tell?

I only have fpcup...so if I run fpcup_update will that give me the the
latest trunk or not?  If so, I can download again and test.

----------------------------------------------------------------------
BigChimp · 2013-12-21 08:35

Ah, I'm going to get rid of some frustrations here... Don't hesitate
to post if you hit problems though... though perhaps best on the fpcup
thread in 3rd party announcements or a new thread as this is no longer
about dbf filtering...

Quote from: bill0287 on December 20, 2013, 05:34:19 pmI am not sure I
have the right trunk...how can I tell?
FPC has only one trunk, not more.
try Code: [Select]svn info c:\development\fpc. If it spits info about
an url other than trunk (e.g. the fixes 2.6 branch) it's not trunk.

See also http://svnbook.red-bean.com/en/1.7/svn-book.html

Quote from: bill0287 on December 20, 2013, 05:34:19 pmI only have
fpcup...so if I run fpcup_update will that give me the the latest
trunk or not?  If so, I can download again and test.
It depends on how you had set it up. It won't magically give you
something else suddenly (or if it does please report a bug). By all
means, please *don't* read fpcup -h, the fpcup readme, wiki pages
(https://bitbucket.org/reiniero/fpcup/wiki/Home,
http://wiki.lazarus.freepascal.org/fpcup), fpcup.html, Readme.txt....
</sarcasm>


Easiest to explain (but by heart, air code warning etc):
1. remove entire c:\development dir+subdirs
2. remove fpcup_update, lazarus_fpcup shortcuts from your desktop
3. fpcup --fpcurl=trunk --fpcdir=c:\development\fpctrunk
rem yes, see fpcup -h | more and fpcup.ini for more details about what
this does. While real men use command line arguments like above, I
prefer ini files - see the Lazarus fpcup wiki page for examples but...
I digress.
4. Coffee/tea/beverage of choice.
5. Try again

----------------------------------------------------------------------
bill0287 · 2013-12-21 19:42

This worked and the test worked as well. Thanks for the instructions.
I downloaded TortoiseShell SVN to keep laz/fpc up to date now - at
least I think that's what it will do for me!

I think this filtering thread is done.

----------------------------------------------------------------------
BigChimp · 2013-12-24 12:00

Quote from: bill0287 on December 21, 2013, 08:42:56 pmI downloaded
TortoiseShell SVN to keep laz/fpc up to date now - at least I think
that's what it will do for me!
It isn't needed when using fpcup. Look in
c:\development\fpcbootstrap\svn to see the svn client fpcup downloaded
for you.

However, TortoiseSVN is very handy and the installer probably adds its
svn.exe to the PATH which is useful (see fpcup readme.txt)