MySQL is very datatype dependent. Let's say you have the following table
with a million rows, indexed properly, and you do the following in Perl.create table foo ( bar1 Int ,bar2 Int );
my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" +); $sth->execute( 2 );
You are going to be very unpleasantly surprised by how long it takes. Then, if you're like me, you'll debug it by going into the mysql commandline and executing the following statement:
SELECT bar2 FROM foo WHERE bar1 = 2;
That query is going to execute very quickly. Up to 30 times quicker. And, you're going to be left scratching your head. I mean, the two queries are equivalent, right?
Actually, they're not. Using DBI->trace(2), you'll notice something slightly different about them. What DBD::mysql is actually passing to the database is
SELECT bar2 FROM foo WHERE bar1 = '2';
Those quotes make all the difference in the world. MySQL is great, but doesn't do heterogenous comparisons very well. This is one of the few places where I'll agree that other RDBMSes, like Oracle, beat MySQL out. And, it's such an easy mistake to make, too!
I have a solution, but it's not the best one. Let's transform our code a little.
Now, DBI will bind the parameter as if it was an integer, not a varchar.my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" +); $sth->bind_param( 1, 2, SQL_INTEGER ); $sth->execute();
The better solution, imho, would be for DBD::mysql to go ahead and look up the column type and bind appropriately. However, I am aware that there are potential secury concerns, so it may be appropriate for an additional option to be passed, maybe "mysql_discover_bind_type", that would always default to 0. Then, those of us who want it would have to explicitly turn it on, but those who don't know enough would turn it off.
------
We are the carpenters and bricklayers of the Information Age.
Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose
I shouldn't have to say this, but any code, unless otherwise stated, is untested
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBD::mysql and binding placeholders
by mpeppler (Vicar) on Sep 22, 2004 at 16:26 UTC | |
by theguvnor (Chaplain) on Sep 22, 2004 at 17:03 UTC | |
by htoug (Deacon) on Sep 23, 2004 at 13:53 UTC | |
Re: DBD::mysql and binding placeholders
by tantarbobus (Hermit) on Sep 23, 2004 at 02:36 UTC | |
by steve (Deacon) on Aug 02, 2008 at 11:30 UTC | |
Re: DBD::mysql and binding placeholders
by Anonymous Monk on Sep 22, 2004 at 18:07 UTC | |
by dragonchild (Archbishop) on Sep 22, 2004 at 18:40 UTC | |
by runrig (Abbot) on Sep 22, 2004 at 18:21 UTC | |
by wigs (Beadle) on Sep 23, 2004 at 04:01 UTC |