[SOLVED] Zeos SQL querry problem
lazarus / 16-Databases

----------------------------------------------------------------------
samto · 2012-09-06 10:46

Hi all,

I am a newbie in using Zeos components.  I need to make a simple
database filtering, using sql querries.

Here is a sample syntax:

ZQuery1.SQL.Clear;
ZQuery1.SQL.Text := Edit1.text;
ZQuery1.Open;

I tried also ZQuery1.ExecSQL procedure but without success ... and
without error message. Simply - it does nothing.  There is probably
some variable not corectly setted, but I dont know which one ... I
tried many combinations.

Can you help me, or better, can you provide me some simple example?

Thanks for a help and have a nice day.

----------------------------------------------------------------------
IndianaJones · 2012-09-06 12:22

Here is a simple filtering mechanism, change <database field> to your
database field that you want to be filtered.
Regards.

Code: [Select]      filter_title:=Edit1.Text;
      ZTable1.DisableControls;
      ZTable1.Filtered:=False;
      ZTable1.Filter:='<database field> LIKE
'+QuotedStr('*'+filter_title+'*');
      ZTable1.Filtered:=True;
      ZTable1.EnableControls;

----------------------------------------------------------------------
goodname · 2012-09-06 13:13

Be careful about using QuotedStr to escape SQL strings. If the string
contains \' and the database engine uses backslash escape the string
passed will be \'' and will fail.

'a single quote \' in a string' ---> 'a single quote \'' in a string'
DB engine will escape to  'a single quote ' in a string'   which is a
parsing error.

MSSQL does not recognize backslash quoting and it can be turned off in
PostgreSQL. AFAIK all other DB engines use backslash escaping.

----------------------------------------------------------------------
IndianaJones · 2012-09-06 19:48

Single punctuation or double matter, as @godname says, but mostly you
can try it yourself.
@Thanks goodname for additional information.
Regards.

----------------------------------------------------------------------
samto · 2012-09-07 05:56

Thank you for your help, it works well (unfortunatelly very slow, but
it is another problem)

----------------------------------------------------------------------
taazz · 2012-09-07 05:59

Quote from: goodname on September 06, 2012, 03:13:59 pmAFAIK all other
DB engines use backslash escaping.

Does a double backslash cancel the escaping procedure on those that
do?

----------------------------------------------------------------------
goodname · 2012-09-07 13:02

Quote from: taazz on September 07, 2012, 07:59:05 amQuote from:
goodname on September 06, 2012, 03:13:59 pmAFAIK all other DB engines
use backslash escaping.

Does a double backslash cancel the escaping procedure on those that
do?

I don't know. The best option here is to just try various string
escape combinations to see what works and what throws errors.

----------------------------------------------------------------------
BigChimp · 2012-09-08 12:50

Quote from: goodname on September 06, 2012, 03:13:59 pmBe careful
about using QuotedStr to escape SQL strings. If the string contains \'
and the database engine uses backslash escape the string passed will
be \'' and will fail.

'a single quote \' in a string' ---> 'a single quote \'' in a string'
DB engine will escape to  'a single quote ' in a string'   which is a
parsing error.

MSSQL does not recognize backslash quoting and it can be turned off in
PostgreSQL. AFAIK all other DB engines use backslash escaping.
Trying to insert quotes by escaping with backslash fails on Firebird
2.5