http://qs321.pair.com?node_id=667081

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

In my code I would like to do something like this: 

my @ids = (1,2,3,4);
my $sth = $dbh->prepare('select * from a_table where id in (?)');
$sth->execute(@ids);

[download]
and have it return me four rows for the four IDs I passed in. I realize that it couldn't work exactly like that because passing multiple arguments to execute provides values to multiple placeholders, but is there any way of achieving it? I use Oracle and occasionally MySQL.

The only alternative I know of is to load the IDs into some temporary table and use a sub-select. But I don't like the idea of having to insert a bunch of physical records just to run a query. What alternatives do I have? Thanks,

Update: I realized that the join ',', ('?') x @ids technique may be applicable here, but I would prefer to keep a single static SQL statement if I can.

Replies are listed 'Best First'.
Re: Can a DBI Placeholder accept multiple values?
by runrig (Abbot) on Feb 08, 2008 at 22:06 UTC
    If you're going to prepare/execute this many times, and you only have (up to) a few values, it might be worthwhile to use the join(",", ("?") x $n) technique, and use prepare_cached. If you might have up to many (100?) values, then forget the placeholders and prepare_cached, and use the quote() method on the values if necessary to create dynamic SQL every time.
[reply]
[d/l]
Re: Can a DBI Placeholder accept multiple values?
by perrin (Chancellor) on Feb 08, 2008 at 22:30 UTC
    You either have to repeat the statement once for each parameter or modify the SQL to have the right number of bind values. There are tools to help with this kind of thing if you find yourself doing it often, e.g. SQL::Interpolate.
[reply]
Re: Can a DBI Placeholder accept multiple values?
by igelkott (Priest) on Feb 09, 2008 at 23:53 UTC

    Don't think this can be done with standard DBI. Every substitution gets quoted so if you use $var = join',', @ids; and execute with this $var, you'll get ... where id in ('1,2,3,4') and the query will obviously fail. Can't trick DBI by setting the { TYPE => SQL_INTEGER } attribute since it'll recognize it as a string and quote it anyway (and complain about the error).

    As an alternative, DBIx-Simple has a "??" placeholder which is replaced with a list of as many question marks as values. Btw, I've only started looking at this module and haven't checked the speed in production.

    So, for example, your code fragment might look like: 

    my @ids = (1,2,3,4);
my $query = 'select * from a_table where id in (??)';
my $result = $dbh->query($q, @ids);

    [download]
    The prepare is taken care of internally and rows can be fetched in various ways.
[reply]
[d/l]
[select]
      You would need to replace (?) with the number of array elements e.g $val = " "; for( $j = 0; $j < @id;$j++) $val .= "?,"; } $val = substr($val,0,-1); $query = $dbh->prepare("REPLACE INTO $tbl ($col) VALUES ($val)"); and then $query->execute(@id); works fine (tested with DBI:mysql)
[reply]
        You would need to replace (?) with the number of array elements

        Yes, the number of (comma-delimited) "?" must equal the number of values to be inserted but the $val .= "?,"; you wrote here would of course end up with an extra comma. There's certainly other ways make this string but that should otherwise work fine.

        As noted, the special "??" placeholder of DBIx-Simple eliminates the need to make the need to make this string of "?,?,?..." (as well as other simplifications).

[reply]
[d/l]
Re: Can a DBI Placeholder accept multiple values?
by assemble (Friar) on Feb 09, 2008 at 00:54 UTC
    I think you may be looking for execute_array. I've never used it, but it looks like it might do what you want it to do.
[reply]

      No, this is not correct; DBI documentation says that execute_array Execute the prepared statement once for each parameter tuple.

      Ciao, Valerio

[reply]
[d/l]

Back to Seekers of Perl Wisdom