CODEWITHSUNDEEP
mysqldelphiparametersdelphi-xe
Simon
Simon

Reputation: 197

MySQL wrong output with IN clause and parameter

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.

Upvotes: 5

Views: 910

Answers (2)

David
David

Reputation: 1

If you have a variable number of parameters in the IN clause, you could build a temporary table in MYSQL (which only contains a column called ID), load the parameter values into the temporary table and then do something like this:

SELECT * FROM users u WHERE u.id IN (SELECT ID FROM TEMPTABLE);

As long the TEMPTABLE only contains the values you want to query, the table space scan is acceptable.

You can then have a variable number of values. You could also make the table permanent, and store the parameters. Add a column for the User, and each user can store their individual parameters.

Upvotes: 0

Ken White
Ken White

Reputation: 125708

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.)

Upvotes: 10

Related Questions