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

[dn.perl]

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.

 

[Martien Verbruggen]

On Sat, 18 Oct 2008 17:06:48 -0700 (PDT),

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