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

No, there is not.

Answer 2

Have a look at this question. I don't know much about perl but if you can join the array together so it's in one string you could probably get it to work. Or just use the most up voted answer in that question.

Answer 3

In python, I've always ended up doing something like:

query = 'select * from junk where junk.id in ('
for id in junkids:
  query = query + '?,'
query = query + ')'

cursor.execute(query, junkids)

...which essentially builds a query with one '?' for each element of the list.

(and if there's other parameters in there too, you need to make sure you line things up correctly when you execute the query)

[edit to make the code easier to understand for non-python people. There is a bug, where the query will have an extra comma after the last ?, which I will leave in because fixing it would just cloud the general idea]

Answer 4

I do something like:

my $dbh = DBI->connect( ... );
my @vals= ( 1,2,3,4,5 );
my $sql = 'SELECT * FROM table WHERE id IN (' . join( ',', map { '?' } @vals ) . ')';
my $sth = $dbh->prepare( $sql );
$sth->execute( @vals );

Answer 5

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 { DBI->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.

Answer 6

With plain DBI you'd have to build the SQL yourself, as suggested above. DBIx::Simple (a wrapper for DBI) does this for you automatically using the '??' notation:

$db->query("select * from foo where bar in (??)", @values);

Answer 7

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( @vals );

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).

Answer 8

I use DBIx::DWIW. It contains a function called InList(). This will create the part of the SQL that is needed for the list. However this only works if you have all your SQL in the program instead of outside in a separate file.

Answer 9

And yet another way to build SQL is to use something like SQL::Abstract....

use SQL::Abstract;
my $sql    = SQL::Abstract->new;
my $values = [ 1..3 ];
my $query  = $sql->select( 'table', '*', { id => { -in => $values } } );

say $query;   # => SELECT * FROM table WHERE ( id IN ( ?, ?, ? ) )

/I3az/