Hi all,
I was bugging everyone in the CB about parts of this yesterday, and I still can't get it working to my liking, so I'll just post the entire sub, and see if anyone has any suggestions.

The problem:
I have a table (or actually two similar tables, but hopefully thats not relevant - see code), that looks like this:

mud_id  |  Name            |  <Other fields .. >
------------------------------------------------
  1      FinalFrontier        <IP, Port etc>
  2      Timewarp
  3      Timewarp-RENEGADE
  4      Kylere'srealm
  5      MG:test@blah

[download]
The mud_id is unique, and the names should also be unique, at least, I check before adding..
I have a sub which gets passed a string, which can be a complete mud name, or an abbreviation of one, and I'm trying to find either an exact match, or return a list of matches.

Code: 

sub getMudName
{
    # Get a name or all names matching or abbreviated by given name
    # Parameter: Object, Name
    my ($dbobj, $type, $name) = @_;

    my $dbh = $dbobj->{$databasehandle};
    $dbobj->{$errormsg} = '';

    my $table;
    if($type eq 'i3')
    {
        $table = 'Intermud3';
    }
    elsif($type eq 'i2')
    {
        $table = 'Intermud2';
    }
    else
    {
        $dbobj->{$errormsg} = "Oops, invalid type: $type\n";
        return undef;
    }

# Tyes fun version: select Name from intermud2 where Name = 'Timewarp'
+ or (0=(select count(*) from intermud2 where Name='Timewarp') and Nam
+e like 'Timewarp%')

#    my $stmS = "SELECT Name FROM $table WHERE Name like '$name%' orde
+r by ";
    my $stmS = "SELECT Name FROM $table WHERE Name like ? order by ";
    $stmS .= "length(Name)";
    $dbobj->debug("getMudName: $stmS\n");
    $stmS = $dbh->prepare($stmS);
#    my $res = $stmS->execute();
    my $res = $stmS->execute($name . '%');
    if(!$res)
    {
        $dbobj->{$errormsg} ="Can't select from $table: " .
            $dbh->errstr() . "\n";
        return undef;
    }
    my $ids = $dbh->selectcol_arrayref($stmS);
    $dbobj->debug(Dumper($ids));
    if(!$ids)
    {
        $dbobj->{$errormsg} = "No Name matching name: " .
            $stmS->err() . "\n";
        return undef;
    }
    if(lc($ids->[0]) eq lc($name))
    {
        return [$ids->[0]];
    }
    return $ids;
}

[download]
This worked wonderfully (see commented out bits) until I tried to make it work for mud names that have a quote in them, by using bound parameters.. If I pass this code 'final' or 'finalfrontier' then it finds nothing at all :(
Any ideas?

C.

20030225 Edit by Corion: Removed runaway bold tag

In reply to DBI, quoting and like - SQLite by castaway

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.