Params and operator Like

[iveres]

Hi!
Now I migrate from sdac to ibdac (returning to Firebird at new employee).
In dynamic building query sql on some condition I need to use AddWhere method that adds some condition and one param.
And It works.

But, it doesn't work with param.

Usually I use:
someqry.AddWhere ( 'somefield = :pMyValue');
...
someqry.ParamByName('pMyValue').Value = '10';

and RecordSet returns some record by condition.

But, if I use:

someqry.AddWhere ( 'somefield Like '':pMyValue%''');
...
someqry.ParamByName('pMyValue').Value = '10';

I got error...


Working solution is:

someqry.AddWhere ( 'somefield Like :pMyValue');
...
someqry.ParamByName('pMyValue').AsString = '10%';

and then Recordset returning couple records expected by this like condition.

But, I wanna % at same place as Like - in AddWhere string, not in param.

How to get "where statment" in FinalSql as: somefield Like ':pMyValue%' and ParamByName('pMyValue').Value = somevaluestring;

Do I need "mannulay" adding param and how to do this?

Thanks!

[AndreyZ]

Hello,

Using parameters (and macros), you should specify the '%' character in the parameter value.
If you want to use the '%' character in the SQL statement itself, you should replace it manually. Here is an example:

Code: Select all

begin
  someqry.SQL.Text := 'query text';
  someqry.AddWhere('somefield like ''$somefield%''');
  someqry.SQL.Text := StringReplace(someqry.SQL.Text, '$somefield', '10', [rfReplaceAll, rfIgnoreCase]);
  someqry.Open;
end;

[iveres]

Thanks!

[AndreyZ]

If any other questions come up, please contact us.