HowTo filtering using zeos component
lazarus / 16-Databases

----------------------------------------------------------------------
slack.dz · 2010-02-06 07:32

Hi
I'm using Sqlite-3 database with zeos component in my project, I am
using the following code to do filter :
Code: [Select]
procedure TForm1.SearchBtnClick(Sender: TObject);


begin
  Datasource1.DataSet.FilterOptions:=[foCaseInsensitive];
  Datasource1.DataSet.Filter:='fname=''' +  SearchEdit.Text +'*''';
  Datasource1.DataSet.Filtered:=true;



end;  

but it doesn't work perfectly, because it doesn't allow filtering
using uppercase letter, it accepts only lowercase and full field
value.
Please help me.

I'm sorry because I don't speak English very well

----------------------------------------------------------------------
zeljko · 2010-02-06 09:36

Quote from: slack.dz on February 06, 2010, 08:32:00 amHi
I'm using Sqlite-3 database with zeos component in my project, I am
using the following code to do filter :
Code: [Select]
procedure TForm1.SearchBtnClick(Sender: TObject);


begin
  Datasource1.DataSet.FilterOptions:=[foCaseInsensitive];
  Datasource1.DataSet.Filter:='fname=''' +  SearchEdit.Text +'*''';
  Datasource1.DataSet.Filtered:=true;



end;  

but it doesn't work perfectly, because it doesn't allow filtering
using uppercase letter, it accepts only lowercase and full field
value.
Please help me.

I'm sorry because I don't speak English very well

Is there sqlite func like postgres have so your statement would be
 Datasource1.DataSet.Filter:='lower(fname)=lower(''' + 
SearchEdit.Text +'*'')';
or
 Datasource1.DataSet.Filter:='lower(fname)= ''' + 
LCLProc.UTF8LowerCase(SearchEdit.Text) +'*''';

----------------------------------------------------------------------
zeljko · 2010-02-06 09:37

ups... I've just mentioned fname= '''+YourString+'*''.
In postgres it must be fname LIKE '''+YourString+'%'
Are you sure that it must be fname = ? (I don't use sqlite so don't
know how it uses LIKE)

----------------------------------------------------------------------
slack.dz · 2010-02-06 14:39

Thank you Zeljko for your reply.
But I don't  know why the FilterOptions doesn't work although I set it
to foCaseInsensitive.
The Filter function still distinguish Lowercase from uppercase.
And I can't do Filtering with Partial compare like fname='sm*' !!!
Please Help

----------------------------------------------------------------------
slack.dz · 2010-02-07 03:17

it woks perfectly, thank you very much zeljko
Example :
Code: [Select]     ZQuery1.Active:=false;
     ZQuery1.SQL.Clear;
     ZQuery1.SQL.Append('select * from pmtable where fname LIKE
'''+SearchEdit.Text+'%''');
     ZQuery1.Active:=true;
NB :
mptable : is database tablefname : is 'First Name' field
nameSearchEdit : is TEdit component

I hope it helps some one