mhearse has asked for the wisdom of the Perl Monks concerning the following question:

When using DBI I usually create statemente handles using a heredoc like so: 
my $sth = $dbh->prepare(<<EOQ);
select
    tbl.column1,
    tbl2.column2
from
    tbl1,
    tbl2
where
    condition = ?
EOQ

[download]
For debugging purposes, I need to print out the prepared sql statement. Is there a method to fetch it?

Replies are listed 'Best First'.
Re: DBI - Get prepared/populated sql statement
by kennethk (Abbot) on Mar 10, 2009 at 15:53 UTC

    According to DBI, you can recover the statement passed to your prepare statement either through the statement handle or the database handle, by invoking the statement attribute. In your code, this could be done with:

    my $sth = $dbh->prepare(<<EOQ);
select
    tbl.column1,
    tbl2.column2
from
    tbl1,
    tbl2
where
    condition = ?
EOQ
print $sth->{statement};

    [download]
[reply]
[d/l]
      That works great. Thanks!

      Oops. I spoke too soon. $dbh->{Statement} gives only the raw query. For debugging purposes, I'd like to have the query which has been populated with arguments from $dbh->execute(). Oh well....

[reply]
[d/l]
[select]
[reply]
Re: DBI - Get prepared/populated sql statement
by tilly (Archbishop) on Mar 10, 2009 at 17:21 UTC
    Random tip. I prefer to use the qq{} syntax so that the interpolated string doesn't stick out from my code. I also have a slightly different standard for writing SQL. So a basic example could look like this: 
    my $sth = $dbh->prepare(qq{
  select
      tbl.column1
    , tbl2.column2
  from tbl1
    join tbl2
      on tbl1.tbl2_id = tbl2.id
  where condition = ?
    and condition2 = ?
}) or die "Can't prepare query: $DBI::errstr";

    [download]
    The basic idea being that continuation pieces like "," and "and" go in front of the next line rather than trailing on the previous line. The point being that when you go to change your SQL you're likely to add columns and/or conditions, and with my way of arranging things you avoid the annoying error where you insert a bunch of rows but forgot to add the continuation piece just before your insert.

    You don't have to do things that way, but if you write as much SQL as I do, you'll find that it makes sense.

    Update: zwon pointed out that I missed the qq{} in the code even though I had just talked about it. D'oh, fixed.

[reply]
[d/l]
      Thanks for your tip. I do write a great deal of SQL. After leafing through some of Joe Celko's books, there's a lot more for me to learn!
[reply]

Back to Seekers of Perl Wisdom