http://www.perlmonks.org?node_id=640255

adrive has asked for the wisdom of the Perl Monks concerning the following question:

guys, is it even possible to bind an sql string with arrays? i need to do something like
my @userArray = qw(batman, robin, joker); my $sql = "select * from tablename where username IN ?"; $stmt=$dbConn->prepare($sql); $stmt->bind_param(1,@userArray); $stmt->execute();
but of course it didn't work. How would be the best way to do this? I don't want to pass my parameters directly into my sql strings.

Replies are listed 'Best First'.
Re: DBD::Oracle 's DBI binding of arrays
by runrig (Abbot) on Sep 21, 2007 at 05:06 UTC
    In addition to quote(), there's:
    my $sql = sprintf("select * from tablename where username IN (%s)", join ",", +("?") x @userArray); my $sth = $dbh->prepare($sql); $sth->execute(@userArray);
      Do also note that runrig is using bind variables (AKA placeholders). There are many advantages of using bind variables over dynamic sql in Oracle, including performance and security.

      Read more on bind variables, hard parse and soft parse in this (oracle-base.com) article.

      --
      Andreas
Re: DBD::Oracle 's DBI binding of arrays
by duff (Parson) on Sep 21, 2007 at 04:16 UTC

    You could use quote():

    my @userArray = qw(batman robin joker); my $set = join(",", map { $dbh->quote($_) } @userArray); my $sql = "select * from tablename where username IN ($set)"; # ...
Re: DBD::Oracle 's DBI binding of arrays
by adrive (Scribe) on Sep 21, 2007 at 06:49 UTC
    thanks :) it's all clear now guys.
Re: DBD::Oracle 's DBI binding of arrays
by adrive (Scribe) on Sep 21, 2007 at 06:16 UTC
    i've seen lots of map being used, but i don't really understand the concept even after reading the manuals. What does it actually do? let's say

    map { $dbh->quote($_) } @userArray

    taking each element out from @userArray and what does it return?

      map takes a list and returns a new list. The new list is created by gathering the results of the codeblock. The codeblock is invoked one per item in the original list:

      use strict; use Data::Dumper; my @items = qw(1 2 3 4 5 6 7); my @new_items; # Make a copy of @items in a very inefficient way @new_items = map { $_ } @items; # Make a new list 2 3 4 5 6 7 8 @new_items = map { $_+1 } @items; # Make a new list of strings: @new_items = map { ">$_<" } @items; # Make a new list with all items appearing twice: # 1 1 2 2 3 3 4 4 5 5 6 6 7 7 @new_items = map { $_ => $_ } @items; # Make a new list with all items quoted: map { $dbh->quote($_) } @userArray
        Beautifully concise explanation, Corion. ++