<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: SPUG: BD::mysql::st execute failed:</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1561" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=437003415-27092006><FONT face=Arial color=#0000ff
size=2>Adrian,</FONT></SPAN></DIV>
<DIV><SPAN class=437003415-27092006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=437003415-27092006> <FONT face=Arial
color=#0000ff size=2>That's a good point. I find that with our database
the query planning doesn't take much of the execute time, unless it's a huge
query, which probably would suffer more from poor planning than from having to
plan multiple times. I also find that I don't do queries in loops, so my
experience may not be typical.</FONT></SPAN></DIV>
<DIV><SPAN class=437003415-27092006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=437003415-27092006><FONT face=Arial color=#0000ff
size=2>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN class=437003415-27092006><FONT face=Arial color=#0000ff
size=2>Peter</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
spug-list-bounces+pdarley=kinesis-cem.com@pm.org
[mailto:spug-list-bounces+pdarley=kinesis-cem.com@pm.org]<B>On Behalf Of
</B>Adrian Hands<BR><B>Sent:</B> Wednesday, September 27, 2006 8:31
AM<BR><B>To:</B> spug-list@pm.org<BR><B>Subject:</B> Re: SPUG: BD::mysql::st
execute failed:<BR><BR></FONT></DIV><!-- 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></BLOCKQUOTE></BODY></HTML>