I am surprised that the parameter is even being handled properly as the first value. I would expect it to expand to something along the lines of: ... in ("1,2,3,4,5,6"). In the past, I have done something like splitting the string into an array, building the parameter list using the x (repetition) operator ("... in (" . join(",", ("?")x$size) . ")..."), and then passing the array to execute. I also think that DBIx::PreQL handles an arrayref properly for building this type of query.
| [reply] [Watch: Dir/Any] [d/l] [select] |
A couple ways I've implemented the actual text to make it a little cleaner. All very subjective. Qualified by I nearly always format my statements with a here-doc:
my $sql = <<EOSQL;
SELECT something
FROM atable
WHERE data IN (?)
EOSQL
- Using baby cart
my $sql = <<EOSQL;
SELECT something
FROM atable
WHERE data IN (@{[join ',', ('?') x $size]})
EOSQL
- Using $"
my $sql = do {
my @holders = ('?') x $size;
local $" = ',';
<<EOSQL;
SELECT something
FROM atable
WHERE data IN (@holders)
EOSQL
};
- Using sprintf
my $sql = sprintf <<EOSQL, join ',', ('?') x $size;
SELECT something
FROM atable
WHERE data IN (%s)
EOSQL
I've generally been using #2 recently.
#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.
| [reply] [Watch: Dir/Any] [d/l] [select] |
Don't do that. Dynamic SQL is bad news.
Every RDBMS has this issue, because stored procedures are not able to use a dynamic list. The way to work this out changes by RDBMS, but all of them are ways it split the values in SQL itself.
Anyway, a simple answer would be to use a recursive CTE to split the values into separate records. Once that is done, IN() or EXISTS can SELECT from the entire CTE.
Here's the basic idea. I quickly looked at mysql functions to write this, but have not tested it at all. Hopefully it shows the basic idea:
WITH RECURSIVE split_csv(x, rest)
AS ( SELECT SUBSTRING_INDEX(?, ',', 1), SUBSTRING(?, FROM INST
+R(?, ',') + 1)
UNION ALL SELECT SUBSTRING_INDEX(rest, ',', 1), SUBSTRING(rest, FROM I
+NSTR(?, ',') + 1)
FROM split_csv)
SELECT something FROM atable WHERE data in (SELECT x FROM split_csv);
| [reply] [Watch: Dir/Any] [d/l] |
| [reply] [Watch: Dir/Any] |