当前位置: 首页 > 面试题库 >

Is there SQL parameter binding for arrays?

锺离飞飙
2023-03-14
问题内容

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.



 类似资料:

相关阅读

相关文章

相关问答