Skip to Content
avatar image
Former Member

SQL Like sentence only works 1 time on ADS12

Hi all,

on ADS12 doesn't works the SQL "Like" sentence, well to be correct, only works the first time you invoke it.

From Delphi XE8,

I have an EditText, a DbGrid and a Button to search in the Country.ADT table from ADTDemoData in a Remote Server.

The button OnClick procedure is that:

procedure TForm5.Button1Click(Sender: TObject);

begin

  AdsQuery1.Close;

  AdsQuery1.SQL.Clear;

  AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE NAME LIKE :paramvalue');

  AdsQuery1.Params[0].Value  := '%'+Edit1.Text+'%';

  AdsQuery1.Open;

end;

When I press the button "Search", looking for countries with "en" in their name, the DbGrid shows "Argentina" and "Venezuela". CORRECT!!!!😊

But, if I repeat the search, looking for countries with "ol" in their name, the DbGrid shows again "Argentina" and "Venezuela".

In fact the search won't works anymore.😔

I test it in my old server with ADS7 and works fine.

I test it with .DBF files and doesn't works.

Why the SELECT LIKE sentence only works 1 time?

Does it changed something in the way I have to invoke the SELECT LIKE sentence?

I need help.

Thanks a lot

Cheers from Barcelona

Eugeni.

Captura.PNG (27.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 04, 2015 at 10:26 AM

    Hi Eugeni,

    The issue seems to be in a way we handle parameters with LIKE clause in SQL query in ADS 12. It works fine till ADS 11.1.


    We will fix this in an upcoming SP, as soon as possible.

    Meanwhile, can you please consider using a workaround?

    begin

      AdsQuery1.Close;

      AdsQuery1.SQL.Clear;

      AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE NAME LIKE :paramvalue');

    AdsQuery1.SQL.Text := 'SELECT * FROM COUNTRY WHERE NAME LIKE  ''%' + Edit1.Text + '%'' ';

      AdsQuery1.Params[0].Value  := '%'+Edit1.Text+'%';

      AdsQuery1.Open;

    end;

    Let me know if this works.

    Thanks,

    Parag

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Eugeni, thanks for your answer. I'm awaiting  SP. Meanwhile in this case, for this project I'm doing, I ships the project with the latest ADS 11 dll and everithing works well, and when get the Service pack I will replace for the 12 dlls.

      Joachim Dürr

      Says using the edit1.text without the parameter will create vulnerabilities.


      So I fixed in this case with the old dlls to ship

  • Nov 02, 2015 at 04:01 PM

    Eugen,

    I forward your request to the engineering, so they will check this behavior.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 04, 2015 at 07:03 AM

    Is there an FTS index on Name?

    If so, maybe "ol"  is a noise word and cannot be searched for. In that case you might not see the exception maybe?

    Did you try longer words?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Stephan,

      in your example table COUNTRY.ADT there are only a PRIMARY index. None FTS index.

      If I use that table in LOCAL mode, not in REMOTE SERVER, it works fine.

      I try with longer words with the same result, doesn't works. It has nothing to do with the searched word.

      I try with other table and different table types, like .DBF with the same result, doesn't works.

      I try with my old ADS7 server and works perfectly, in fact, all my software worked fine until I upgraded to ADS12.

      Did you test my sample code? It works with your ADS12 server?

      Thanks

      Eugeni