Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBD::Oracle 's DBI binding of arrays

by adrive (Scribe)
on Sep 21, 2007 at 02:37 UTC ( #640255=perlquestion: print w/ replies, xml ) Need Help??
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.

Comment on DBD::Oracle 's DBI binding of arrays
Download Code
Re: DBD::Oracle 's DBI binding of arrays
by duff (Vicar) 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 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 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. ++
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://640255]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (8)
As of 2014-12-18 06:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (43 votes), past polls