Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

DBI placeholders and like statement

by jfroebe (Vicar)
on Jun 18, 2013 at 16:52 UTC ( #1039600=perlquestion: print w/ replies, xml ) Need Help??
jfroebe has asked for the wisdom of the Perl Monks concerning the following question:

AFAIK, you can't use a placeholder with a SQL like statement. A coworker says you can. Can anyone confirm or deny it?

$query = 'select name from my_table where name like ?%'; my $sth = $dbh->prepare($query); $sth->execute('Jo');

Basically he is convinced that you can. Nothing I've shown him has convinced him otherwise. Hopefully some prince or princess of the Perl Monks can break this stalemate

UPDATE:

poj pointed out that I was incorrect. The percent sign belongs in the execute() not the prepare query.

Jason L. Froebe

Blog, Tech Blog

Comment on DBI placeholders and like statement
Download Code
Re: DBI placeholders and like statement
by poj (Priest) on Jun 18, 2013 at 17:00 UTC
    This works on MySQL ;
    $query = 'select name from my_table where name like ?'; my $sth = $dbh->prepare($query); $sth->execute('Jo%');
    poj

      Don't I have egg on my face! :) I was putting the percent sign in the query itself.

      Thanks again!

      Jason L. Froebe

      Blog, Tech Blog

        Well, you could say:
        where name like ? || '%'
        But that still won't use an index on the column since the query optimizer doesn't know where any other wildcard will be, whereas hardcoding the entire argument might use an index:
        where name like 'abc%'

        Don't worry about it. First time I tried using like with placeholders I did the same thing. :)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1039600]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (3)
As of 2014-08-30 17:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (293 votes), past polls