Debugging DBI placeholders

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

I have the following code in a subroutine:

my $insert_query = "INSERT INTO $table VALUES ( $layout )";
my $sth = $dbh->prepare($insert_query) or die $DBI::errstr;
$sth->execute(@values) or die $DBI::errstr;
[download]

The name of the table, the layout and the values are passed in, with the layout containing some constants and some placeholders. One such layout parameter looks like this:"DEFAULT, ?, ?, DEFAULT, DEFAULT". This means that there are lots of places where things can go wrong. And wrong something is certainly going. What I would really like to know is what SQL statement actually gets invoked, for example INSERT INTO data VALUES ( DEFAULT, 1, 2, DEFAULT, DEFAULT ). I've tried googling and going through the DBI documentation, but haven't found any means of getting this printed. Can this be done, and if so, how?

Regards,

John Davies

Comment on Debugging DBI placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Debugging DBI placeholders by marto (Cardinal) on Feb 04, 2011 at 13:27 UTC
To see the query which is run you could enable Tracing. Cheers Martin	[reply]
Re^2: Debugging DBI placeholders by davies (Monsignor) on Feb 04, 2011 at 13:38 UTC
Thanks. Trace was mentioned in reply to one of my first posts, but I neither understood (at the time) nor remembered until you reminded me. A quick test with trace seems to indicate that the problem is not what I expected, so I have something to get my teeth into. Thanks again & regards, John Davies	[reply]
Re: Debugging DBI placeholders by Tux (Canon) on Feb 04, 2011 at 13:51 UTC
The sugested `$dbh->trace (...)` is of course the documented way of tracing what actually happens with your code on the way to and from the database. You didn't tell what database you are using, but some (DBD::Oracle, DBD::Pg, and DBD::Unify that I know of) have implemented the `dbd_verbose` attribute, in which you can track what the DBD backend is doing with your data. Read the documentation of your DBD for how it - if at all - implements `dbd_verbose`. The huge advantage of `dbd_verbose` over `$dbh->trace` is that you do not get to see the DBI messages, which I already trust enough not to be part of my doubts. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^2: Debugging DBI placeholders by davies (Monsignor) on Feb 04, 2011 at 14:06 UTC
Thanks. I'll play around with that. I'm not specifying DBD explicitly; I'm trying to write code that should work for any database back end. I'm actually using MySQL myself, and marto's help highlighted that my code is connecting to localhost instead of the specified server, which is understandably causing problems! Regards, John	[reply]
Re: Debugging DBI placeholders by mje (Curate) on Feb 04, 2011 at 15:29 UTC
DBIx::Log4perl can log SQL only and even parameters and a lot more.	[reply]
Re^2: Debugging DBI placeholders by davies (Monsignor) on Feb 04, 2011 at 16:05 UTC
This looks very interesting, but it doesn't seem to be in any of the PPM repositories I know about. Is it available for ActiveState Perl, and if so, where? Regards, John Davies	[reply]
Re^3: Debugging DBI placeholders by mje (Curate) on Feb 04, 2011 at 16:16 UTC
I've no idea, I wrote it but do not decide what gets into ActiveState. It is a pure Perl module so should be easy to install even without a ppm.	[reply]
Re^4: Debugging DBI placeholders by VinsWorldcom (Prior) on Feb 04, 2011 at 20:18 UTC
Re^3: Debugging DBI placeholders by mje (Curate) on Feb 05, 2011 at 12:00 UTC
Look here. Apparently there was something broken with the DBI build and this broke modules depending on it. I'm reliably informed the DBIx::Log4perl module should appears in repositories very soon.	[reply]
Re^4: Debugging DBI placeholders by davies (Monsignor) on Feb 05, 2011 at 13:53 UTC
Re: Debugging DBI placeholders by dont_you (Hermit) on Feb 05, 2011 at 21:42 UTC
A crude approach: `sub _debug_query { my ($sql, @values) = @_; # Format query $sql =~ s/(FROM\|(LEFT )?JOIN\|WHERE\|AND\|GROUP\|ORDER\|LIMIT)/\n$1/g; $sql =~ s/(SELECT\|ROWS)/$1\n/g; $sql =~ s/(\w{4,},)/$1\n/g; # Replace values @values = map { $_ =~ m/^\d+$/ ? $_ : "'$_'" } @values; $sql =~ s/\?/shift @values/e while (@values); warn "$sql\n"; }` [download]	[reply] [d/l]
Re^2: Debugging DBI placeholders by davies (Monsignor) on Feb 06, 2011 at 10:38 UTC
Thanks for the suggestion, but I don't see it helping me. Trace revealed my problem because it included output from the connection command, revealing where my immediate problem was. While your code would be better for some things (trace gives the prepare and execute information on two lines while I would really prefer a single combined exposition), I can't see how it would handle the insert query I included in my OP. I'm not good enough at Perl or SQL to build a full SQL parser that would cover all queries without being another possible point of failure that would have to be investigated when next I foul up. Regards, John Davies Update, in case anyone reads this while in search of a similar solution. Increasing the trace level DOES produce the full query, but it also makes the trace information more verbose.	[reply]