On Mon, Jan 18, 2010 at 12:04 PM, Fred Morris <span dir="ltr"><<a href="mailto:m3047@m3047.net">m3047@m3047.net</a>></span> wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
Another variant of that which is useful is as part of, for instance,<br>
enforcing a "no quotes in SQL literals" rule:<br>
<br>
my ($name, $address, $city) =<br>
map $dbh->quote($_), @{$self}{qw/ name address city /};<br>
<br>
my $sql .= "VALUES ($name, $address, $city)";<br>
<br></blockquote><div><br>If you have a rule 'no quote sin sql literals' rule, you're violating the spirit by simply interpolating them in, don't you think? Just because they're not in the literal doesn't mean they're not in the string that goes to the server. It sounds like a poorly stated concept formed into a rule.<br>
<br>Regardless of that, among the reasons not to do this is the fact that the database will try to remember your queries for optimization (queries repeat frequently) when you do the dbh->quote method, the sql is different every single time. If you use binding instead, something like $dbh->do('... VALUES (?, ?, ?)', undef, $name, $address, $city); to the database this query looks the same regardless of what name, address, and city may be. If its new sql, then the db has to recompile it and recreate an execution plan - incurring significant overhead that could have been avoided entirely had you bound the variables. <br>
<br>I recently worked a project where a set of non-binding queries in rapid succession would kill the mysql database reliably. rewrote the queries, and it is much happier now.<br><br>David<br><br></div></div>-- <br>"If only I could get rid of hunger by rubbing my belly" - Diogenes<br>