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

Dear Monks,

I am using DBI to communicate with MySQL database, and I am also using placeholders in a function like this:

 my $selectdata = $dbh->prepare("SELECT * FROM data WHERE content = ?");

However, when I execute this statement, it comes up with case-insensitive match (i.e. it selects all rows where content is "Perl", "PERL" and "perl" when I supply "Perl" to the execute() method)

Is there an easy way to enable case-sensitive matches? I have read through DBI documentation, but I only seem to find a chapter of how to get information about case-sensitivity of the DBI driver itself and not on statement-to-statement basis.

I am sorry if this is a dumb question, please push me into the right direction

Replies are listed 'Best First'.
Re: DBI case-insensitive placeholders
by dbwiz (Curate) on Oct 27, 2003 at 14:41 UTC

    It is not a DBI issue. It's MySQL that has case insensitive matching.

    However, you can force an exact match using the "BINARY" keyword.

    There is no need to change the column definition, if what you need is just a case sensitive match.

    my $sth = $dbh->prepare("SELECT * FROM data WHERE content = BINARY ? "
+);
$sth->execute("Perl");
# This will find "Perl", not "PERL" or "perl"

    [download]
[reply]
[d/l]
Re: DBI case-insensitive placeholders
by hardburn (Abbot) on Oct 27, 2003 at 14:32 UTC

    I don't think the problem is in your code, but in your database schema. You're probably searching on a column that doesn't keep case. In MySQL, CHAR columns need to be delcared BINARY to be case-sensitive.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    :(){ :|:&};:

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]

      Hmm... I'll try to change the column type -- it is currently set to VARCHAR(40). I have never had this problem with PHP code though -- it always worked case-sensitively (Of course, I was building queries by hand: SELECT * FROM data WHERE content='$content')

[reply]
[d/l]

Back to Seekers of Perl Wisdom