Is there SQL parameter binding for arrays?

Question

Is there a standard way to bind arrays (of scalars) in a SQL query? I want to bind into an IN clause, like so:

SELECT * FROM junk WHERE junk.id IN (?); 

I happen to be using Perl::DBI which coerces parameters to scalars, so I end up with useless queries like:

SELECT * FROM junk WHERE junk.id IN ('ARRAY(0xdeadbeef)'); 

Clarification: I put the query in its own .sql file, so the string is already formed. Where the answers mention creating the query string dynamically I'd probably do a search and replace instead.

Edit: This question is kind of a duplicate of Parameterizing a SQL IN clause?. I originally thought that it should be closed as such, but it seems like it's accumulating some good Perl-specific info.

Answer 1

If you don't like the map there, you can use the 'x' operator:

my $params = join ', ' => ('?') x @foo; my $sql    = "SELECT * FROM table WHERE id IN ($params)"; my $sth    = $dbh->prepare( $sql ); $sth->execute( @foo ); 

The parentheses are needed around the '?' because that forces 'x' to be in list context.

Read "perldoc perlop" and search for 'Binary "x"' for more information (it's in the "Multiplicative Operators" section).