Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBI parameterized database query with comma seperated list

by mg9176 (Initiate)
on Dec 18, 2015 at 18:06 UTC ( #1150706=perlquestion: print w/replies, xml ) Need Help??
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 (Prior) on Dec 18, 2015 at 18:51 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1150706]
Approved by toolic
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2017-12-14 23:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What programming language do you hate the most?




















    Results (414 votes). Check out past polls.

    Notices?