(SOLVED) dbf.filter property question

[bill0287]

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]

Code: [Select]

Filter := FieldComboBox.Text + '=' + QuotedStr(DBLookupcomboBox1.Text);


[bill0287]

That did it...thanks so much!

This forum is very helpful...

[bill0287]

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]

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]

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...

[BigChimp]

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

[BigChimp]

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]

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]

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]

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]

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]

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]

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]

BigChimp

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