SPUG: BD::mysql::st execute failed:

[Adrian Hands]

Generally speaking,
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.
If you don't use placeholders, particularly in a loop, you'll pollute the db's statement cache.

On the other hand, the statement analyzer will determine the execution plan BEFORE the vars are bound, so
it won't have the benefit of knowing the value and will assume the worst.
This becomes an issue with statements like:

  where foo_col LIKE ?

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

YMMV depending on the db, db version and driver.


-----Original Message-----
From: spug-list-bounces+aeh=akc.org at pm.org on behalf of Peter Darley
Sent: Wed 9/27/2006 11:20 AM
To: Jacinta Richardson; spug-list at pm.org
Cc: luis medrano
Subject: Re: SPUG: BD::mysql::st execute failed:
 
Jacinta,

	I don't agree.  Anything that hides the actual SQL statement is bad bad
bad.  If there wasn't an alternative it would be worth putting up with the
place holders, but since you can get the same protection without hiding the
exact query, I don't think it's a very good practice.  Things like
SQL::Abstract make me cringe. :)

Thanks,
Peter

-----Original Message-----
From: spug-list-bounces+pdarley=kinesis-cem.com at pm.org
[mailto:spug-list-bounces+pdarley=kinesis-cem.com at pm.org]On Behalf Of
Jacinta Richardson
Sent: Saturday, September 23, 2006 6:45 PM
To: spug-list at pm.org
Cc: luis medrano
Subject: Re: SPUG: BD::mysql::st execute failed:


Peter Darley wrote:

> $Query = "INSERT INTO HTML_Pages (Page_ID, Type, Entity, Name, Title,
> Content) VALUES (" . Quote($Args{PageID}) . ", " . Quote($Args{Type}) . ",
"
> . Quote($Args{Entity}) . ", " . Quote($Args{Name}) . ", " .
> Quote($Args{Title}) . ", " . Quote($Args{Content}) . ")";
>
> $Neo::DB::Shopper->do($Query) || die "Bad Query!\n\t$Query";

It's still a better solution to use placeholders:

my @values = ($post_author, $post_date, $post_date_gmt, "@post_content",
	$post_title, $post_status, $comment_status, $ping_status, $post_name,
	$post_modified, $post_modified_gmt, $guid);

my $sth1 = $dbh->prepare("INSERT INTO wp_posts (post_author, post_date,
 		post_date_gmt, post_content, post_title, post_status,
 		comment_status, ping_status, post_name, post_modified,
		post_modified_gmt, guid)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
	or die $dbh->errstr;

my $rv1 = $sth->execute(@values);


As it says in the documentation, using place holders allows the database
engine
to be more efficient as well as ensuring that your values are properly
quoted
for whichever database engine you are using (and if your underlying database
engine changes, then that's handled too!

You may also find it useful to look into SQL::Abstract as it makes these
large
inserts and updates much easier.  Of course there's always
Class::DBI/DBIx::Class if you want to take it a step further than that too.

	my %fieldvars = (
		post_author => $post_author,
		post_date   => $post_date,
		...
		guid        => $guid,
	);

	use SQL::Abstract;

	my $sql = SQL::Abstract->new;
	my($stmt, @bind) = $sql->insert("wp_posts", \%fieldvals);

	my $sth = $dbh->prepare($stmt);
   	$sth->execute(@bind);

All the best,

	Jacinta

--
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
 (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |
_____________________________________________________________
Seattle Perl Users Group Mailing List
     POST TO: spug-list at pm.org
SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
    MEETINGS: 3rd Tuesdays
    WEB PAGE: http://seattleperl.org/

_____________________________________________________________
Seattle Perl Users Group Mailing List  
     POST TO: spug-list at pm.org
SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
    MEETINGS: 3rd Tuesdays
    WEB PAGE: http://seattleperl.org/




-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.pm.org/pipermail/spug-list/attachments/20060927/a3aa95f0/attachment-0001.html