DBI case-insensitive placeholders

[Anonymous Monk]

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

[hardburn]

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.

[Anonymous Monk]

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

[dbwiz]

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"