Lazarus
Programming => Databases => Topic started by: bill0287 on December 04, 2013, 04:55:16 am
-
This code works okay:
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):
Filter := 'FieldComboBox.Text=' + QuotedStr(DBLookupcomboBox1.Text);
Anyone have any suggestions of what might work?
UPDATE:
Solution provided below.
-
Filter := FieldComboBox.Text + '=' + QuotedStr(DBLookupcomboBox1.Text);
-
That did it...thanks so much!
This forum is very helpful...
-
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?
-
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):
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.
-
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):
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...
-
Bug report
http://bugs.freepascal.org/view.php?id=25432
-
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 ;)
-
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.
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:
Filter := FieldComboBox.Text + '=' + AnsiQuotedSTR(DBLookupcomboBox1.Text,'"')
-
The only problem is that it fails on values that don't have any quotes in them.
Missing a final else, add:
else
QuoteStr:=UserLooksFor;
at the end of the if chain:
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.
-
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.
-
My previous post was just a disaster. Here is the code:
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;
-
Once I added the small change shown below, everything worked:
QuotedStr(DBLookupcomboBox1.Text),
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;
-
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);
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;
-
BigChimp
So once the above bug is fixed does that mean all this quote checking will be unnecessary ?
-
Well, you will still need to surround the field with quotes and double up any quotes inside, Engkin's solution should be sufficient:
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
-
So 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
-
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:
DBf1.Filter := 'LAST=' + QuotedStr(DBLookupcomboBox1.Text);
DBF1.Filtered:= true;
-
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
-
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.
-
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...
I am not sure I have the right trunk...how can I tell?
FPC has only one trunk, not more.
try 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
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.
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
-
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.
-
I 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)