using Filter on ZTTable

[chuckie]

maybe a stupid question, but...

I'm working on an application D7+MySQL+ZeosLib 6.1.5

A DBGrid is connected by a DataSet to a ZTCustomers: with a double click on, you open the form with all data of the single customer (thanks to ZTCustomers.MasterFields :=ID) .
If you would to look for a customer, you should write, into an Edit, NAME Like 'ACME%', in order to find all the customers with the name starting like ACME.
So, pressing a botton, the program copies the string into ZTCustomers.Filter and then set Filtered:= True.
It doesn't work because ZTTable.Filter doesn't recognize % character.
I don't want to use ZQueries.
suggestions?

[mdaems]

Maybe you should replace all '%' to the right filter wildcard during the copy towards ZTCustomers.Filter?

What's your problem with TZQuery? I was thinking TZTable was derived from tzquery, so I would think it doesn't matter as, strictly spoken,you ARE using a TZQuery.

Mark

[Michael]

Hi,

the solution to this problem is quite easy: Just take "?" (for a single character) or "*" (for multiple charcters) they will work! It is not like with SQL because it's just a kind of JDBC .

[syntax="delphi"]p_name like 'Be*'
[/syntax]

And if you you need to compare case insensitively you may use function UPPER or LOWER:

[syntax="delphi"]lower(p_name) like 'an*'
upper(p_name) like 'AL*'[/syntax]

Regards!

[orax]

I had almost the same issue but my profile was Delphi 7, sqlite, ZeosLib. Although the ZeosLib is the only reasonable component set to use with sqlite in a Delphi Application, I think it does not support wildcards ('*' for example). Notice that we don't have FilterOptions listed in the Properties Tab.

I was trying to filter a set of records retrieved from a TZQuery (indeed very fast with sqlite) but I failed. So I had to put a more complicated search mechanism like the one below:

[syntax="delphi"]procedure Split(const Delimiter: Char; Input: string; const Strings: TStrings);
begin
Assert(Assigned(Strings));
Strings.Clear;
Strings.Delimiter:=Delimiter;
Strings.DelimitedText:=Input;
end;

procedure TForm1.Button5Click(Sender: TObject);
var
A: TStringList;
i: Integer;
begin
Edit2.Text:=Trim(Edit2.Text);
if FileExists('tomas.db') then //check if database file exists;
begin
if (Edit2.Text<>'Some words with spaces between them...') and (filter_cat<>'') and (Edit2.Text<>'') then
begin
A:=TStringList.Create;
Split(' ', Edit2.Text, A); //split the space delimited string into array;
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select * from tickets where '+filter_cat+' like ''%'+A[0]+'%''');
for i:=0 to A.Count-1 do
begin
ZQuery1.SQL.Add('and '+filter_cat+' like ''%'+A+'%''');
end;
ZQuery1.Open;
A.Free;
end
else if (Edit2.Text<>'Some words with spaces between them...') and (Edit2.Text<>'') and (filter_cat<>'dest') and (filter_cat<>'firm') then
begin
A:=TStringList.Create;
Split(' ', Edit2.Text, A);
filter_cat:='dest';
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select * from tickets where '+filter_cat+' like ''%'+A[0]+'%''');
for i:=0 to A.Count-1 do
begin
ZQuery1.SQL.Add('and '+filter_cat+' like ''%'+A+'%''');
end;
ZQuery1.Open;
A.Free;
end
end
else
end;[/syntax]

[orax]

I am sorry, may be I am in a big mistake about filter options of ZeosLib, I do apologize now.

[Michael]

[orax]

I used to filter my query like this:

param = 'any_string*'

while it shoud be

param LIKE 'any_string*'

OK, I am stupid.

[orax]

Although, the code I stated earlier did a better work than just filtering the query results, because it gave the user the ability to make an AND conditional search - and that way filtering is almost not needed. Only in my specific case of course.

Thanks to all the developers of ZeosLib and I wish to you luck and money - for creating the magnificent ZeosLib! Make it shareware! It costs at least 300 euro... May be more, when you get to support sqlite3...

Best regards!

[Michael]

Hi orax,

thank you for all that but if it's up to me, ZeosLib will never ever cost anything. It will stay under LGPL for all time.

Btw: The SQLite 3 support is already in CVS . I'm currently testing it internally and preparing the next CVS-Release of 6.5.1 alpha. Hope to get it going before Xmas. At the moment I'm involved in other project work.

Regards!

[orax]

You are the best, man!