Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

DBI debugging: SQL dumping?

by dgaramond2 (Monk)
on Mar 07, 2008 at 13:29 UTC ( #672767=perlquestion: print w/replies, xml ) Need Help??

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?

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(), ...).

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.

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

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().
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://672767]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2023-01-27 11:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?