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');

[download]

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

Replies are listed 'Best First'.
Re: DBI placeholders and like statement
by poj (Abbot) 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%');

    [download]
    poj
[reply]
[d/l]

      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

[reply]
        Well, you could say:
        where name like ? || '%'

        [download]
        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%'

        [download]
[reply]
[d/l]
[select]

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

[reply]
      I was having the same problem using sqlite on a Mac. And this syntax works as well. 
          $sth=$dbh->prepare(qq{SELECT title,books.author,notes,books.titlei
+d,pubyear,books.authorid
    FROM books
    WHERE notes like ?
    ORDER BY lower(title)})
    or die "Couldn't prepare your statement: " . $dbh->errstr;
    $sth->execute("%$Notes%") or die "Couldn't execute your statement:
+ " . $sth->errstr;

      [download]
[reply]
[d/l]

        It is so odd that I was having difficulty with this just yesterday. I must have been missing something obvious, because this works for me:

        use warnings;
use strict;

use Test::Thing;

my $Test = Test::Thing->new;

my $q = qq{
    SELECT x
    FROM y.z
    WHERE a LIKE ?
    LIMIT 1
};

my ($result) = $Test->db->dbh->selectrow_array($q, undef, "1111%");

        [download]

        Details masked, and normally query calls are hidden in the backend. However, that test succeeded.

        I can't even imagine what I was trying yesterday to have it fail. Again, it is so very interesting that this specific issue was brought up here after 10+ years.

[reply]
[d/l]

Back to Seekers of Perl Wisdom