Problem when WHERE with IN operator

[decisao]

Hello!

I am in trouble when use IN operator and parameters, as follows:

SELECT
id,
name
FROM
table
WHERE
id IN (:parameter)

Passing 1,2,3 as string parameter. In any case, it returns only the fist ID (in this case 1).

Can be tested with MyDAC 9.0.2 and 8.7.27 using the Data Editor in IDE (I am using Lazarus 1.8.4).

If you can provide a solution for this problem, please help me.

att,
Eliéser

[ViktorV]

This behavior of MyDAC is correct, since in this case, the query SELECT id, name FROM table WHERE id IN ('1, 2, 3') will be created, during execution of which MySQL server will return only one row. You can check this by executing a corresponding query using the standard means, for example, MySQL Workbench.
To solve the task, you can use the following code:

Code: Select all

  MyQuery.SQL.Text := 'SELECT id, name FROM table WHERE id IN (:parameter1, :parameter2, :parameter3)';
  MyQuery.ParamByName('parameter1').AsInteger := 1;
  MyQuery.ParamByName('parameter2').AsInteger := 2;
  MyQuery.ParamByName('parameter3').AsInteger := 3;
  MyQuery.Open;