Is there SQL parameter binding for arrays?

19

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.

sql
perl
arrays
binding
parameters
asked on Stack Overflow Feb 4, 2009 by cdleary • edited May 23, 2017 by Community

8 Answers

13

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

answered on Stack Overflow Feb 5, 2009 by Ovid • edited Mar 30, 2016 by Ovid
12

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.

answered on Stack Overflow Feb 5, 2009 by Ask Bjørn Hansen • edited Feb 22, 2013 by Ask Bjørn Hansen
9

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 );
answered on Stack Overflow Feb 5, 2009 by JDrago
6

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 ( ?, ?, ? ) )
answered on Stack Overflow Feb 5, 2009 by draegtun • edited Apr 21, 2017 by AndyG
4

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);
answered on Stack Overflow Feb 5, 2009 by 8jean
2

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]

answered on Stack Overflow Feb 4, 2009 by John Fouhy • edited Feb 5, 2009 by John Fouhy
0

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.

answered on Stack Overflow Feb 5, 2009 by Peter Stuifzand
0

Use

SELECT * FROM junk WHERE junk.id = ANY (?);

instead

answered on Stack Overflow May 31, 2017 by Martin Janota

User contributions licensed under CC BY-SA 3.0