Devart Forums

Posted: **Tue 01 Jan 2019 22:33**

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

Posted: **Thu 03 Jan 2019 14:03**

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;

Devart Forums

Problem when WHERE with IN operator

Problem when WHERE with IN operator

Re: Problem when WHERE with IN operator