Cody Pendant has asked for the wisdom of the Perl Monks concerning the following question:

I was trying to do a search-engine type SQL query like this: 
foreach my $word (@words) {
    my $sth = $dbh->prepare(
        "SELECT * FROM  my_table
         WHERE 
           description LIKE '%?%' 
        OR shortdescription LIKE '%?%' 
        OR name LIKE '%?%'"
      ) || die "Error: " . $dbh->errstr;

    $sth->execute($word,$word,$word) || die "Error: " . $dbh->errstr;
 # do some stuff
}

[download]
But it dies with the error "Called with 3 bind variables when 0 are needed". How come? What should I do instead?

($_='kkvvttuu bbooppuuiiffss qqffssmm iibbddllffss')
=~y~b-v~a-z~s; print

Replies are listed 'Best First'.
Re: DBI - I can't use bind variables with "like"?
by jZed (Prior) on Jun 30, 2006 at 00:58 UTC
    A placeholder is a question mark, not a string that has percent signs and question marks in it. Put just question marks in the prepare() SQL and put the percent signs in the execute().
[reply]
Re: DBI - I can't use bind variables with "like"?
by lestrrat (Deacon) on Jun 30, 2006 at 00:18 UTC

    Untested, but have you tried

    my $sth = $dbh->prepare(<<EOSQL);
  SELECT * FROM my_table WHERE description LIKE ?
EOSQL
$sth->execute('%' . $description . '%');

    [download]
[reply]
[d/l]
Re: DBI - I can't use bind variables with "like"?
by grinder (Bishop) on Jun 30, 2006 at 20:53 UTC

    Do your query planner a favour and forget about like. You're better off using instr, and you can feed it a placeholder. I would rewrite your statement as

        my $sth = $dbh->prepare(
        "SELECT * FROM  my_table
         WHERE
           instr(description, :1) > 0
        OR instr(shortdescription, :1) > 0
        OR instr(name, :1) > 0"
      ) || die "Error: " . $dbh->errstr;
      $sth->execute($word) || die "Error: " . $dbh->errstr;

    [download]

    Use numbered placeholders instead, that way you don't have to build up a riculously long list of redundant args for execute(). SQL Server, Oracle and Pg all permit numbered placeholders. If your database doesn't, well then you have no choice but to use ?. Your database might also not implement instr(), but I expect there would be a function with a different name that serves the same purpose.

    • another intruder with the mooring in the heart of the Perl

[reply]
[d/l]
      I don't see how the query plan would be any better with instr() instead of LIKE. Neither would be able to take advantage of any index (except maybe a complete scan of the index, which is not much better than a sequential scan of the table). Let me know if I'm mistaken :-)

      Also, you can somewhat get around typing repeated placeholder arguments as long as the corresponding ?'s are consecutive by something like:$sth->execute( ($var) x 3 )

      Update: Also, if the database doesn't determine the query plan at prepare() time, then it might be able to use an index on a 'LIKE ?' if the argument doesn't begin with a wild card (but that is not the case with the OP).

[reply]
[d/l]
Re: DBI - I can't use bind variables with "like"?
by jdtoronto (Prior) on Jun 30, 2006 at 09:59 UTC
    To expand on the other answers. The placeholder is just the ? character, you can't use something like the '%?%' because the DBI interprets that to be a literal query string. To build the query string use the ? in the placeholder and replace it with your concatenated string in the execute.

    You might also want to look at the database section of the tutorials on this site.

    jdtoronto

[reply]
Re: DBI - I can't use bind variables with "like"?
by eric256 (Parson) on Jun 30, 2006 at 14:47 UTC

    You could also change your SQL to where description LIKE '%' || ? || '%' then your calling code can stay the same.


    ___________
    Eric Hodges
[reply]
[d/l]
Re: DBI - I can't use bind variables with "like"?
by Mandrake (Chaplain) on Jun 30, 2006 at 05:12 UTC
    This can be changed as : 
    foreach my $word (@words) {
    my $sth = $dbh->prepare(
        "SELECT * FROM  my_table
         WHERE 
           description LIKE '?' 
        OR shortdescription LIKE '?' 
        OR name LIKE '?'"
      ) || die "Error: " . $dbh->errstr;

    #append and prepend percentage signs
    $word ="%".$word."%" ;

    $sth->execute($word,$word,$word) || die "Error: " . $dbh->errstr;
 # do some stuff
}

    [download]
    Append and prepend % sign to each element.
    I believe this should work. (Code is untested!!)
    Thanks..
[reply]
[d/l]

      Your code wouldn't work because you have quoted the placeholders : LIKE '?'

      Instead you should do as recommended in the reply by lestrrat and leave them unquoted : LIKE ?

[reply]
[d/l]
[select]
Re: DBI - I can't use bind variables with "like"?
by devinb (Novice) on Jul 03, 2006 at 15:27 UTC 
    foreach my $word (@words) {
    my $sth = $dbh->prepare(
        "SELECT * FROM  my_table
         WHERE 
           description LIKE '%?%' 
        OR shortdescription LIKE '%?%' 
        OR name LIKE '%?%'"
      ) || die "Error: " . $dbh->errstr;

    $sth->execute($word,$word,$word) || die "Error: " . $dbh->errstr;
 # do some stuff
}

    [download]


    You are getting the error:

    "Called with 3 bind variables when 0 are needed"

    Because the DBI Doesn't think you have any bind variables to be filled because your ? ( Question Marks / Bind Variable Placeholders ) are within single quotes.

    I believe that when using bind variables with the DBI you need to leave the ? ( question mark ) unquoted, or by itself.

    Since you're doing a LIKE you can prepend, and append the percent signs to your variable $word.

    Like this: 
    # Add percent sign for LIKE value in DB Search
$word .= "\%$word\%";

    [download]


    Then modify your SQL Statement to look like this: 
        my $sth = $dbh->prepare(
        "SELECT * FROM  my_table
         WHERE 
           description LIKE ? 
        OR shortdescription LIKE ?
        OR name LIKE ?"
      ) || die "Error: " . $dbh->errstr;

    [download]


    Definitely hope that helps! Good luck!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom