Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
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 (Monsignor) 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 examining the Monastery: (3)
As of 2018-07-21 12:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?















    Results (448 votes). Check out past polls.

    Notices?