Using Perl bind_param with SQL IN statement

Question

Possible Duplicate:
Is there SQL parameter binding for arrays?

I was wondering if there is anyway to use bind_param with SQL IN statements. According to perl documentation bind_param_array cannot be used as well. Has anyone come across the same situation?

http://search.cpan.org/perldoc?DBI#bind_param_array

Answer 1

Not if you're wanting an arbitrary number of placeholders, no. You can use it with an IN, as in

where foo in ( ?, ?, ? )

but then you must have exactly three binds.

Answer 2

No you can't do this easily. One option is to use $dbh->quote, eg.

my @values = (1,2,3,4);
my $sql = "SELECT * from my_table WHERE ID IN (";
$sql .= join(',', map { $dbh->quote($_) } @values)
$sql .= ')';

Or you can create the necessary placeholders and pass the array in as bind parameters, eg.

my @values = (1,2,3,4);
my $sql = "SELECT * from my_table WHERE ID IN (";
$sql .= join(',', map { '?' } @values);
$sql .= ')';

my $sth = $dbh->prepare($sql);
$sth->execute(@values);

Neither is exceptionally pretty.