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.
You specify “this is the SQL for a query with one parameter” – that won’t
work when you want many parameters. It’s a pain to deal with, of course. Two
other variations to what was suggested already:
1) Use DBI->quote instead of place holders.
my $sql = "select foo from bar where baz in ("
. join(",", map { $dbh->quote($_) } @bazs)
. ")";
my $data = $dbh->selectall_arrayref($sql);
2) Use an ORM to do this sort of low level stuff for you. DBIx::Class or
Rose::DB::Object, for example.