http://qs1969.pair.com?node_id=1150706

mg9176 has asked for the wisdom of the Perl Monks concerning the following question:

I'm having a bit of trouble with a parameterized query - mysql as the datasource.

my $sql = 'SELECT something FROM atable WHERE data in (?);'; my $sth = $dbh->prepare($sql); my $rv = $sth->execute($data);

That all works fine and dandy, however if $data is something like

my $data = "1,2,3,4,5,6";

only the first value is passed into the query. Thoughts on a remedy?

Replies are listed 'Best First'.
Re: DBI parameterized database query with comma seperated list
by MidLifeXis (Monsignor) on Dec 18, 2015 at 18:21 UTC

    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.

    --MidLifeXis

      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
      1. Using baby cart
        my $sql = <<EOSQL; SELECT something FROM atable WHERE data IN (@{[join ',', ('?') x $size]}) EOSQL
      2. Using $"
        my $sql = do { my @holders = ('?') x $size; local $" = ','; <<EOSQL; SELECT something FROM atable WHERE data IN (@holders) EOSQL };
      3. 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.

        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);
Re: DBI parameterized database query with comma seperated list
by poj (Abbot) on Dec 18, 2015 at 18:51 UTC