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

Dear Monks,
I often find myself troubleshooting why a complex DBI select query return zero rows. Often, these queries are over a dozen tables in a complex join, and also contain dynamically created SQL 'where' conditions based on combinations of certain arguments being present.

Being the lazy monk that I am, I just want to see a pretty version of the query that DBI executes, with the bind variables interpolated in the query, so that I can manually pick apart the query and determined what data is missing from which tables that made the query return zero rows.

I know about DBI->trace, but the output is not very readable. There's a lot of debugging info that I don't need. Can anyone recommend an existing module or share a snippet of code you may have which parses the trace output, and pretty prints the query like I described?
Thanks!
  • Comment on Pretty printing the exact query (with bind variables) executed by DBI?

Replies are listed 'Best First'.
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by jZed (Prior) on Dec 18, 2007 at 02:07 UTC
    Part of the problem is that DBI deos not ever interpolate the binds into the SQL and most (all?) DBDs likewise pass along the SQL and bind values separately to the RDBMS. The best you can do is simulate it as I show in Interpolate binds into SQL on error - DBI subclassing. That should show you how to "assemble" the statement and where you can format it as you desire.
[reply]
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by erix (Prior) on Dec 18, 2007 at 02:45 UTC

    I find it handy (as a debug facility) to add the actual values out-commented behind the placeholders. This doesn't give you the exact statement but it makes it easy to reproduce them, or just to check the actual values that the statement was executed with: 

    select
   name,
   address,
   phone

where name = ? -- 'Doe'

    [download]

    Another approach would be to let DBI trace produce its mess, and make your statements easy to parse out of the 'wealth of information' by a markup scheme, again SQL-outcommented, of course: 

    -----8<---------------

select
   name,
   address,
   phone

where name = ?

-----8<---------------

    [download]
[reply]
[d/l]
[select]
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by pfaut (Priest) on Dec 18, 2007 at 02:11 UTC

    That information may not be available. Oracle's OCI supports parameterized queries. The prepare step sends the statement with placeholders to the server for parsing and generation of an execution plan. The data is plugged in on the server at the time of execution. The full statement with data plugged into placeholders is never available to DBI or DBD::Oracle.

    There may be other ways to find out, though. I know PostgreSQL can be told to keep a log of the statements it executes. If we knew what database you were working with, maybe someone who knows that database could tell you how to trace at the database.

    90% of every Perl application is already written. ⇒
    dragonchild
[reply]
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by andye (Curate) on Dec 18, 2007 at 20:48 UTC
[reply]

Back to Seekers of Perl Wisdom