You can only put a single data value in each placeholder, no lists. Run
'perldoc DBI' and look in the sections "Placeholders and Bind Values" and
"bind_param" for examples and limits.
If all you are passing are numbers or strings, you can skip bind_param() and
include the bind variable values as the $sth->execute() argument list.
# Untested snippet
# $dbh -> {'RaiseErrors'} = 1; # Alternate error handling
my $ph = join ',', ('?') x scalar;
my $sth = $dbh -> prepare( join "\n",
"SELECT *",
" FROM table",
" WHERE attr1 IN ( $ph ) AND attr2 IN ( $ph )" )
or die "Prepare failed, $DBI::errstr";
$sth -> execute( @attr1, @attr2 ) or die "Execute failed, $DBI::errstr";
my @names = @{$sth -> {NAME}};
my @cols;
$sth -> bind_columns( \( @cols[0 .. $#names] ) );
while ( $sth -> fetch ) {
# Do something
}
die "Fetch failed, $DBI::errstr" if $DBI::err;
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "McLennan,Neil [PYR]" <[EMAIL PROTECTED]>
To: "Dbi-Users (E-mail)" <[EMAIL PROTECTED]>
Sent: Friday, March 09, 2001 3:38 PM
Subject: Bind variables in a variable list
> I have to create an SQL statement of the form
>
> select * from table where attr1 in ( 1,2,3.....n) and attr2 in ( 'ABC',
> 'DEF', ....., 'asdf');
>
> where the number of entries within the variable lists is driven by the
user
> interface. As a result the number of elements within the lists can go
from
> one to many. I would like to use bind variables to represent the
variables
> lists. I tried one method where ..
>
> $sql="select * from table where attr1 in ( ? ) and attr2 in ( ? );
> $sth->prepare($sql);
>
> $sth->bind_param(1, $string1, {TYPE=>SQL_VARCHAR2});
> $sth->bind_param(2, $string2, {TYPE=>SQL_VARCHAR2});
>
> No errors in binding and executing but no records were returned when there
> should have been some.
>
>
> A second technique was to build the SQL statement on the fly based on the
> values of attr1 and attr2 select by the user. The result was SQL
statements
> of the form
> $sql="select * from table where attr1 in (?, ?, ?, ....) and attr2 in (?,
> ?, ?, ?...)";
> I then bound in each value.
>
> This technique worked but seemed quite laborious.
>
> Is there a quick way is elegant of doing this kind of thing? Note that
in
> this instance attr1 is numeric and attr2 is character strings;
