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

I have a query similar to this 

SELECT someFields FROM mytable WHERE myqualifier LIKE ?

[download]

the variable being put into the substitution is entered by the user and chomped. What is the best way to accomplish this query and have the like work correctly. I want the wildcard on the right side of the substitution like 

soandso like "test*"

[download]
this is in MsAccess so it doesn't use the % operator for like.

Does anyone have any idea what I should do to accomplish this?

Replies are listed 'Best First'.
Re: variable substitution using Like in DBI
by BlueBlazerRegular (Friar) on Sep 11, 2003 at 21:02 UTC
    Instead of using '*' (like Access says you should), try the '%'. My tests don't work when using '*', but do when using the '%'. My guess is that DBI is converting it during the prepare step.

    Other than that, my code looks just like jasonk and menolly's.

    If it makes any difference, I'm using Access 97 via DBD::ODBC.

    Good luck.

[reply]
      My guess is that DBI is converting it during the prepare step.

      It's probably the ODBC driver doing the conversion. DBI shouldn't be converting anything, as it and DBD::ODBC wouldn't know anything about what needs to be converted specifically for Access.

[reply]
        Awesome .. thanks that fixed it. Shoulda thought of that though.. thanks guys for all your help.
[reply]
Re: variable substitution using Like in DBI
by jasonk (Parson) on Sep 11, 2003 at 19:13 UTC 
    my $sth = $dbh->prepare("SELECT someFields FROM mytable WHERE myqualif
+ier LIKE ?");
$sth->execute("test*");

    [download]
    We're not surrounded, we're in a target-rich environment!
[reply]
[d/l]
      More specifically, 
      my $input = param('input'); #or however you're getting it
chomp $input;
#any other processing to deal with unwanted characters

my $sth = $dbh->prepare("SELECT someFields FROM mytable WHERE myqualif
+ier LIKE ?");
$sth->execute("$input*");

      [download]
[reply]
[d/l]

        That is exactly what I tried but to no avail. It doesn't return any results. I'm assuming (note: assuming) that its a qwirk with Access or ODBC that I'm just not accounting for. Anymore ideas are VERY much welcome.. thanks guys.

        edit FYI my query will run correctly with that syntax but does not produce the same results as running it interactively. I get no results in script, and the appropriate number in the interactive

[reply]

Back to Seekers of Perl Wisdom