You need to start a little earlier. Build the SQL so it has the desired
number of placeholders.
# The default error message is more thorough that your die()
$dbh -> {RaiseErrors} = 1;
# Assuming @names has the correct number of values
my ( @where, @vals );
foreach ( @names ) {
push @where, "name = ?";
push @vals, "%$_%";
}
my $sql = "SELECT * FROM my_table";
$sql .= "WHERE " . join( " AND ", @where ) if @where;
my $sth = $dbh -> prepare( $sql );
$sth -> execute( @vals );
# This will generate a brand new SQL statement every time.
# In Oracle, the parser will recognize when you use the identical text
# and use the previously parsed statement.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Tony Vassilev" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 16, 2002 04:54
Subject: arbitrary number of bindings & LIKE statement
> I'm a newbie trying to get a grip on the bind_param function:
>
> If I'm trying to execute an SQL <LIKE> statement with several
placeholders,
> the following method of binding works (I get the proper data returned):
>
> my $sth = $dbh->prepare($sql) || die "couldn't prepate";
> $sth->bind_param(1, "%$names[0]%");
> $sth->bind_param(2, "%$names[1]%");
> $sth->execute() || die "error couldn't execute";
>
> That method of course requires that I know the number of bindings
ocurring.
> For an arbitrary number, I've been trying this:
>
> my $sth = $dbh->prepare($sql) || die "couldn't prepate";
> foreach $x (0..$#names) {
> $sth->bind_param($x+1, "%$names[$x]%");
> }
> $sth->execute || die "error couldn't execute";
>
> And yet, fetching the results of this one returns only comparisons with
the
> last binding. So, in a case with two names, only data mathing LIKE
> %$names[1]% comes back while the first one is not compared.
>
> I don't understand... I mean, aren't those two methods effectively asking
> for the same thing? I've been fooling around for a while trying to find a
> way to execute a LIKE statement with an arbitrary number of placeholders
but
> keep running into stumbling blocks with the DBI syntax.
>
> Any help would be great! I imagine this is a very simple problem.
