DBI debugging: SQL dumping?

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

Dear Monks,

I have lots of complex SQL in my script in the form:

$dbh->do("INSERT ... etc ... (FOO(?), ?, ?, ?, ?, ...)", {}, $var1, ...);

(yeah I know, I should use CDBI or Rose::DB or whatever. But this is actually some low-level data importing stuffs which require SQL tweaking.)

Sometimes when things do not go right, $dbh->do() failed with error from MySQL along the line of "syntax error near ) line 45". Since the SQL is pretty long/complex, is there a way to make DBI print the substituted/final SQL sent to the DB so I can better see which part is causing the syntax error?

Comment on DBI debugging: SQL dumping? Download Code

Replies are listed 'Best First'.
Re: DBI debugging: SQL dumping? by moritz (Cardinal) on Mar 07, 2008 at 13:33 UTC
Look for "trace" in the DBI man page. There's a whole section on tracing, and I'm sure it has all you need. (As a side note, if you do many inserts with the same SQL you should use prepare_cached(), execute(), execute(), ... instead of do(), do(), ...).	[reply]
Re: DBI debugging: SQL dumping? by Fletch (Bishop) on Mar 07, 2008 at 13:33 UTC
Look for the `trace` method in TFM (DBI). A setting of ~2 should get you the level of detail you're looking for. The cake is a lie. The cake is a lie. The cake is a lie.	[reply]
Re: DBI debugging: SQL dumping? by clinton (Priest) on Mar 07, 2008 at 13:36 UTC
Given that you're using place holders, the SQL itself shouldn't change, so if it works once it should "always" work, as long as the parameters that you're passing in are valid. So you could try just dumping the parameters, and seeing what doesn't look right. Alternatively, if you want more, try using tracing, available through DBI. See the tracing section in DBI. Clint	[reply]
Re: DBI debugging: SQL dumping? by perrin (Chancellor) on Mar 07, 2008 at 15:27 UTC
Are you executing those multiple times? If so, you'd be much better off preparing the queries and then executing the handles over and over instead of using do().	[reply]
Re: DBI debugging: SQL dumping? by dgaramond2 (Monk) on Mar 08, 2008 at 00:30 UTC
Thanks for all the suggestions, I should've read DBI manpage beyond the first two pages. :-) I'm doing do() because it's shorter to code, and the script is a one-off thing that's not performance critical anyway. But I do use prepare() and execute() other times.	[reply]