Recent

Author Topic: (SOLVED) dbf.filter property question  (Read 16919 times)

bill0287

  • Full Member
  • ***
  • Posts: 146
(SOLVED) dbf.filter property question
« on: December 04, 2013, 04:55:16 am »
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.
« Last Edit: December 21, 2013, 08:47:10 pm by bill0287 »

engkin

  • Hero Member
  • *****
  • Posts: 2692
Re: dbf.filter property question
« Reply #1 on: December 04, 2013, 07:08:02 am »
Code: [Select]
Filter := FieldComboBox.Text + '=' + QuotedStr(DBLookupcomboBox1.Text);

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: dbf.filter property question
« Reply #2 on: December 04, 2013, 07:43:51 am »
That did it...thanks so much!

This forum is very helpful...

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: (SOLVED) dbf.filter property question
« Reply #3 on: December 17, 2013, 06:52:18 am »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (SOLVED - almost) dbf.filter property question
« Reply #4 on: December 17, 2013, 10:55:53 am »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (SOLVED) dbf.filter property question
« Reply #5 on: December 17, 2013, 11:07:59 am »
I 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...
« Last Edit: December 17, 2013, 05:32:49 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (SOLVED - almost) dbf.filter property question
« Reply #6 on: December 17, 2013, 02:06:11 pm »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (SOLVED - almost) dbf.filter property question
« Reply #7 on: December 17, 2013, 05:33:44 pm »
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 ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: (SOLVED - almost) dbf.filter property question
« Reply #8 on: December 17, 2013, 09:06:36 pm »
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,'"')
« Last Edit: December 17, 2013, 09:13:04 pm by bill0287 »

engkin

  • Hero Member
  • *****
  • Posts: 2692
Re: (SOLVED - almost) dbf.filter property question
« Reply #9 on: December 17, 2013, 09:37:00 pm »
Quote
The 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

  • Full Member
  • ***
  • Posts: 146
Re: (SOLVED - almost) dbf.filter property question
« Reply #10 on: December 17, 2013, 10:52:35 pm »
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

  • Hero Member
  • *****
  • Posts: 2692
Re: (SOLVED - almost) dbf.filter property question
« Reply #11 on: December 17, 2013, 11:13:02 pm »
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

  • Full Member
  • ***
  • Posts: 146
Re: (SOLVED - almost) dbf.filter property question
« Reply #12 on: December 18, 2013, 01:03:08 am »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (SOLVED - almost) dbf.filter property question
« Reply #13 on: December 19, 2013, 12:36:55 pm »
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;
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: (SOLVED - almost) dbf.filter property question
« Reply #14 on: December 19, 2013, 05:00:24 pm »
BigChimp

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

 

TinyPortal © 2005-2018