<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7650.28">
<TITLE>RE: SPUG: BD::mysql::st execute failed:</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Generally speaking,<BR>
If you use placeholders, the driver will use db bind variables and the db will be able to cache the statement and avoid reparsing on each execution.<BR>
If you don't use placeholders, particularly in a loop, you'll pollute the db's statement cache.<BR>
<BR>
On the other hand, the statement analyzer will determine the execution plan BEFORE the vars are bound, so<BR>
it won't have the benefit of knowing the value and will assume the worst.<BR>
This becomes an issue with statements like:<BR>
<BR>
where foo_col LIKE ?<BR>
<BR>
(if the bind-var is 'foo%', for example, an index on foo_col will likely be very useful, but if the bind-var is '%foo', for example, the index will be useless)<BR>
<BR>
YMMV depending on the db, db version and driver.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: spug-list-bounces+aeh=akc.org@pm.org on behalf of Peter Darley<BR>
Sent: Wed 9/27/2006 11:20 AM<BR>
To: Jacinta Richardson; spug-list@pm.org<BR>
Cc: luis medrano<BR>
Subject: Re: SPUG: BD::mysql::st execute failed:<BR>
<BR>
Jacinta,<BR>
<BR>
I don't agree. Anything that hides the actual SQL statement is bad bad<BR>
bad. If there wasn't an alternative it would be worth putting up with the<BR>
place holders, but since you can get the same protection without hiding the<BR>
exact query, I don't think it's a very good practice. Things like<BR>
SQL::Abstract make me cringe. :)<BR>
<BR>
Thanks,<BR>
Peter<BR>
<BR>
-----Original Message-----<BR>
From: spug-list-bounces+pdarley=kinesis-cem.com@pm.org<BR>
[<A HREF="mailto:spug-list-bounces+pdarley=kinesis-cem.com@pm.org">mailto:spug-list-bounces+pdarley=kinesis-cem.com@pm.org</A>]On Behalf Of<BR>
Jacinta Richardson<BR>
Sent: Saturday, September 23, 2006 6:45 PM<BR>
To: spug-list@pm.org<BR>
Cc: luis medrano<BR>
Subject: Re: SPUG: BD::mysql::st execute failed:<BR>
<BR>
<BR>
Peter Darley wrote:<BR>
<BR>
> $Query = "INSERT INTO HTML_Pages (Page_ID, Type, Entity, Name, Title,<BR>
> Content) VALUES (" . Quote($Args{PageID}) . ", " . Quote($Args{Type}) . ",<BR>
"<BR>
> . Quote($Args{Entity}) . ", " . Quote($Args{Name}) . ", " .<BR>
> Quote($Args{Title}) . ", " . Quote($Args{Content}) . ")";<BR>
><BR>
> $Neo::DB::Shopper->do($Query) || die "Bad Query!\n\t$Query";<BR>
<BR>
It's still a better solution to use placeholders:<BR>
<BR>
my @values = ($post_author, $post_date, $post_date_gmt, "@post_content",<BR>
$post_title, $post_status, $comment_status, $ping_status, $post_name,<BR>
$post_modified, $post_modified_gmt, $guid);<BR>
<BR>
my $sth1 = $dbh->prepare("INSERT INTO wp_posts (post_author, post_date,<BR>
post_date_gmt, post_content, post_title, post_status,<BR>
comment_status, ping_status, post_name, post_modified,<BR>
post_modified_gmt, guid)<BR>
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")<BR>
or die $dbh->errstr;<BR>
<BR>
my $rv1 = $sth->execute(@values);<BR>
<BR>
<BR>
As it says in the documentation, using place holders allows the database<BR>
engine<BR>
to be more efficient as well as ensuring that your values are properly<BR>
quoted<BR>
for whichever database engine you are using (and if your underlying database<BR>
engine changes, then that's handled too!<BR>
<BR>
You may also find it useful to look into SQL::Abstract as it makes these<BR>
large<BR>
inserts and updates much easier. Of course there's always<BR>
Class::DBI/DBIx::Class if you want to take it a step further than that too.<BR>
<BR>
my %fieldvars = (<BR>
post_author => $post_author,<BR>
post_date => $post_date,<BR>
...<BR>
guid => $guid,<BR>
);<BR>
<BR>
use SQL::Abstract;<BR>
<BR>
my $sql = SQL::Abstract->new;<BR>
my($stmt, @bind) = $sql->insert("wp_posts", \%fieldvals);<BR>
<BR>
my $sth = $dbh->prepare($stmt);<BR>
$sth->execute(@bind);<BR>
<BR>
All the best,<BR>
<BR>
Jacinta<BR>
<BR>
--<BR>
("`-''-/").___..--''"`-._ | Jacinta Richardson |<BR>
`6_ 6 ) `-. ( ).`-.__.`) | Perl Training Australia |<BR>
(_Y_.)' ._ ) `._ `. ``-..-' | +61 3 9354 6001 |<BR>
_..`--'_..-_/ /--'_.' ,' | contact@perltraining.com.au |<BR>
(il),-'' (li),' ((!.-' | www.perltraining.com.au |<BR>
_____________________________________________________________<BR>
Seattle Perl Users Group Mailing List<BR>
POST TO: spug-list@pm.org<BR>
SUBSCRIPTION: <A HREF="http://mail.pm.org/mailman/listinfo/spug-list">http://mail.pm.org/mailman/listinfo/spug-list</A><BR>
MEETINGS: 3rd Tuesdays<BR>
WEB PAGE: <A HREF="http://seattleperl.org/">http://seattleperl.org/</A><BR>
<BR>
_____________________________________________________________<BR>
Seattle Perl Users Group Mailing List <BR>
POST TO: spug-list@pm.org<BR>
SUBSCRIPTION: <A HREF="http://mail.pm.org/mailman/listinfo/spug-list">http://mail.pm.org/mailman/listinfo/spug-list</A><BR>
MEETINGS: 3rd Tuesdays<BR>
WEB PAGE: <A HREF="http://seattleperl.org/">http://seattleperl.org/</A><BR>
<BR>
<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>