Re: DBI - I can't use bind variables with "like"?

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

Comment on Re: DBI - I can't use bind variables with "like"? Download Code

Replies are listed 'Best First'.
Re^2: DBI - I can't use bind variables with "like"? by runrig (Abbot) on Jun 30, 2006 at 21:38 UTC
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]

Replies are listed 'Best First'.

Re^2: DBI - I can't use bind variables with "like"?
by runrig (Abbot) on Jun 30, 2006 at 21:38 UTC

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]