DBI module: can't pass argument to "in (?)" clause

Discussion in 'Perl Misc' started by dn.perl@gmail.com, Oct 19, 2008.

  1. Guest

    I have a table t1: State, Capital.
    Entries are ('CA', 'Sacramento'), ('MA', 'Boston') and ('TX',
    'Austin').

    Here is a block which returns nothing :
    my $my_list = " 'CA', 'MA' " ;
    my $sth = $dbh->prepare("select capital from t1 where state in (?)");
    $sth->execute($my_list) ;

    If I replace it with the following, I get the expected results:
    my $my_list = " 'CA', 'MA' " ;
    my $sth = $dbh->prepare("select capital from t1 where state in
    ($my_list)");
    $sth->execute() ;

    What could be the problem?
    Thanks in advance.
     
    , Oct 19, 2008
    #1
    1. Advertising

  2. On Sat, 18 Oct 2008 17:06:48 -0700 (PDT),
    <> wrote:
    >
    > I have a table t1: State, Capital.
    > Entries are ('CA', 'Sacramento'), ('MA', 'Boston') and ('TX',
    > 'Austin').
    >
    > Here is a block which returns nothing :
    > my $my_list = " 'CA', 'MA' " ;
    > my $sth = $dbh->prepare("select capital from t1 where state in (?)");
    > $sth->execute($my_list) ;
    >
    > If I replace it with the following, I get the expected results:
    > my $my_list = " 'CA', 'MA' " ;
    > my $sth = $dbh->prepare("select capital from t1 where state in
    > ($my_list)");
    > $sth->execute() ;
    >
    > What could be the problem?



    From the manual for DBI:

    Also, placeholders can only represent single scalar values. For examâ€
    ple, the following statement won’t work as expected for more than one
    value:

    "SELECT name, age FROM people WHERE name IN (?)" # wrong
    "SELECT name, age FROM people WHERE name IN (?,?)" # two names



    You could do something like:

    my @states = qw/CA MA/;

    my $sth = $dbh->prepare('select capital from t1 where state in (' .
    join(',', (?) x @states) . ')');

    $sth->execute(@states);

    I'm sure there are probably modules in the DBIx namespace somewhere that
    have convenience methods for this.

    Martien
    --
    |
    Martien Verbruggen | Useful Statistic: 75% of the people make up
    | 3/4 of the population.
    |
     
    Martien Verbruggen, Oct 19, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    542
    Juha Laiho
    Jul 22, 2004
  2. Mike
    Replies:
    1
    Views:
    4,230
    Bryce
    Oct 6, 2004
  3. Ben Kial
    Replies:
    1
    Views:
    697
    Eric Enright
    Nov 15, 2004
  4. Nebur
    Replies:
    5
    Views:
    337
    Gabriel Genellina
    May 30, 2007
  5. Tim Haynes
    Replies:
    3
    Views:
    152
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page