On Wednesday, January 16, 2002, at 02:13 PM, Michael A Chase wrote:
> You need to start a little earlier. Build the SQL so it has the
> desired
> number of placeholders.
or visit CPAN to find a searchbuilder in the DBIx or SQL hiearchies.
>
> # 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.
>
>
>
