SQLite3 filter
lazarus / 16-Databases

----------------------------------------------------------------------
Robz · 2015-12-09 07:21

Hi,

How do I use a Lazarus SQLIte3 filter?
Is the Tquery.filter different then the Tquery.SQL.text filter?
So far I cannot get either to work. Yet, I have no problem using a
commercial SQLite explorer. Unfortunately, none of the commercial
SQLite explorers show the filter command specific details. I think the
problem is filter syntax; double quotes, single quotes, parenthesis, %
and other stuff like it.
I have searched Wiki, Google and many tutorials without success.
I am using a dbGrid, dbNavigator and created a class with TSqLite,
Transaction and TSQLite3Connection components. All components work.
Help, suggestions will be greatly appreciated,

----------------------------------------------------------------------
mangakissa · 2015-12-09 08:58

I don't see any problems
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.Filter:= 'name = ' +
QuotedStr(Edit1.text);SQLQuery1.filtered := true;          
  QuoteCREATE TABLE `nicknames` (
   `Name`   TEXT,
   `nickname`   TEXT,
   `age`   INTEGER
)

----------------------------------------------------------------------
rvk · 2015-12-09 09:42

Quote from: Robz on December 09, 2015, 08:21:56 amI think the problem
is filter syntax; double quotes, single quotes, parenthesis, % and
other stuff like it.
Also note that the TSQLQuery.Filter property doesn't work like the
WHERE clause of a SQL-statement. It is not advanced AT ALL. You cant
use LIKE-keywords and such. If you want to perform a WHERE-like
filtering you could adjust the SQL.Text (with a close/open) or use
ServerFilter (which does an internal close/open automatically).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.ServerFilter:= 'name like ' + QuotedStr('%' + Edit1.text +
'%');SQLQuery1.ServerFiltered := true;

----------------------------------------------------------------------
Robz · 2015-12-09 16:11

QuoteAlso note that the TSQLQuery.Filter property doesn't work like
the WHERE clause of a SQL-statement. It is not advanced AT ALL. You
cant use LIKE-keywords and such. If you want to perform a WHERE-like
filtering you could adjust the SQL.Text (with a close/open) or use
ServerFilter (which does an internal close/open automatically).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.ServerFilter:= 'name like ' + QuotedStr('%' + Edit1.text +
'%');SQLQuery1.ServerFiltered := true;
Thank you for your help. You found my problem! I did try and use the
"LIKE" command in the SQLite filter. I will try your "serverfilter"
SQL suggestion, next.

Just a note:
The "WHERE" command works with no problem when used to open the
SQLQuery table. I have found it useful to sort by two columns.

Thank you
Rob

----------------------------------------------------------------------
Robz · 2015-12-09 16:45

OOPS!

Its "ORDER BY" not "WHERE" for two or more fields.