#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    10
    Rep Power
    0

    MS Access and LIKE Operator


    Please .. I've been at this for 5 hours. No joke. I'm using Perl and DBI to operate an MS Access databse. All I'm trying to do is apply the damned LIKE operator to get some records and nothing is working at all.
    I want to pull results from a table where a field has some given word within the field. The word could be anywhere in the field, so I'm trying to get it with wildcard. What I get is errors. I just need to know how to make a LIKE work with DBI and MS Access. My head is killing me. Much much thanks in advance.

    This list of $test are all the formats I've tried

    $test = "*brian";
    $test = "*brian*";
    $test = "%brian";
    $test = "%brian%";
    $test = "'*brian'"; # single quote inside double quotes
    $test = "'*brian*'"; # single quote inside double quotes

    This list of SQL's are all the formats I've tried:

    $SQLstatement="SELECT * FROM Pictures1 WHERE PictureWhoIsInIt LIKE $test";

    $SQLstatement="SELECT * FROM Pictures1 WHERE PictureWhoIsInIt LIKE = $test";

    $SQLstatement="SELECT * FROM Pictures1 WHERE PictureWhoIsInIt LIKE = ?";
    $StatementHandle = $DbaseHandle->prepare($SQLstatement);
    $StatementHandle->execute($test)|| die "\nFIND PICTURE Failed. $DBI::errstr";

    $SQLstatement="SELECT * FROM Pictures1 WHERE PictureWhoIsInIt LIKE ?";
    $StatementHandle = $DbaseHandle->prepare($SQLstatement);
    $StatementHandle->execute($test)|| die "\nFIND PICTURE Failed. $DBI::errstr";
  2. #2
  3. Meow Black Belt
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2005
    Location
    Beaverton OR
    Posts
    932
    Rep Power
    526
    I am not good at Perl but AFAIK, it should be

    Code:
    $test = "%annie%"
    $sqlStatement = "SELECT * FROM Pictures1 WHERE PictureWhoIsInIt LIKE ?";
    $sth = $dbh->prepare($sqlStatement);
    ...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    10
    Rep Power
    0

    Nope..


    As mentioned in the above example.. that isn't working. Some searching here and on two other webs forums I see mention that for access the wildcard must be the asterisk.

    It's totally ticking me off. Its a simple function and its just not working. Even ripped apart to just itself, I get nothing when I should be seeing all kinds of results.

    Someone? Anyone? Many hours lost to this damn thing.. obsessive that I am, I have to see this damn thing work. I need to be able to searches for partial matches like that. Would it be anything at all to do with the mdb driver itself? Its on a Windows XP Home machine for testing purposes. The machine itself does not have MSAccess. But I am running another MSAccess database through it (no LIKE commands in the program yet) and those Select and Update commands work fine. PLease..please.. you know what its like when one stupid thing is holding up everything around you.. ideas?
  6. #4
  7. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    MS Access use * not %

    SELECT * FROM MyTable WHERE SomeField LIKE "*lint*";
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    10
    Rep Power
    0

    Talking Perl, Perl DBI, MS Access and LIKE operand solution


    I found the solution to this problem, and its a contradiction to what you see explained for how to use wildcards with MS Access databases. You do not use the asterisk as so many samples and examples state. You DO use a percent, and you do it in a specific way. See this thread posted below for the full description.

    http://forums.devshed.com/perl-programming-6/perl-ms-access-dbi-like-does-not-work-315901.html
  10. #6
  11. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    ^ Must be the Perl DBI implementation handles it that way. No wonder for the confusion, because access' own drivers insist on asterisks. If you actually run Access you'll find SELECT * FROM Whatever WHERE Something Like 'A%' does nothing but Like 'A*' works. This is why everyone tells you it's * not %, because in MS-Land it really is.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

IMN logo majestic logo threadwatch logo seochat tools logo