MySQL wrong output with IN clause and parameter

Question

I'm having a hard time with a query in MySQL.

I'm working with Delphi XE and I'm sending a query with some parameter to MySQL. Here's an example for the query:

SELECT * FROM users u WHERE u.id IN (:idUsers);

The ':idUsers' is the variable that will receive the parameter I send with Delphi, which is a string containing that is formatted like this, ex.: 1,2,3

The problem is that with this string, I receive only the first user (id = 1). From what I can see, its just like MySQL is adding some quote ('') at the beginning and at the end of the string I send, like if it was '1,2,3' instead of 1,2,3. I've tried this select :

SELECT * FROM users u WHERE u.id IN ('1,2,3'); 

and it does indeed return only the first user..

I had a function in MSSQL that was splitting the string I sended and returning a temporary table, but we recently switched to MySQL, and from what I read, MySQL doesn't allow returning a table.

Anyone has a clue on how to solve this problem? I've been scrapping the Web for an answer and haven't found one for this specific problem.

Thanks for the help.

Answer 1

Parameters don't work like that; they have no idea that what you're trying to provide is a comma-separated list of values. If you set ParamByName('WhatEver').AsString or Value, it thinks you mean a quoted string that contains everything that belongs in that parameter. So it's doing exactly what it appears to - it's passing IN ('1, 2, 3'), instead of the intended IN (1, 2, 3).

You'll need to either go back to parsing it out yourself and using a temp table, or build the WHERE clause dynamically and concatenating it before executing the query. (Actually, you could build the query dynamically, creating a new parameter for each of the items in the IN clause, and then loop through and assign values to each of those dynamically created parameters, but that gets very ugly very quickly.)