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

[Peter Darley]

RE: SPUG: BD::mysql::st execute failed:Adrian,

    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.

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 Adrian
Hands
  Sent: Wednesday, September 27, 2006 8:31 AM
  To: spug-list at pm.org
  Subject: Re: SPUG: BD::mysql::st execute failed:


  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/23018fdb/attachment.html