Printing full SQL statement with placeholders

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

In the DBI module (and/or DBD::mysql), is there a way to print the completed SQL statements when you're using placeholders?

In other words, I'm using placeholders in a query and I receive this message:

DBD::mysql::st execute failed: You have an error in your SQL syntax ne
+ar 'type='1',span='2',kit='10',block='C',run='Fals' at line 1 at chan
+ge.pl line 385.
[download]

That doesn't help much for debugging purposes. I need to see the full SQL statement, but all I have in the program is "UPDATE table SET field=?,field=?,field=? WHERE field=?". Is there a way to print the filled-in statement?

I've checked the perldoc, etc, or I wouldn't be posting here. If the answer is there, please point me to chapter and verse, since I don't see it.

Comment on Printing full SQL statement with placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Printing full SQL statement with placeholders by Fletch (Bishop) on Oct 24, 2001 at 20:28 UTC
Check the `DBI` documentation for information on the `trace` method. Turning on tracing on a handle (statement or database connection) makes `DBI` dump out information about what it's doing as it passes requests through to the database. Something like `$sth->trace(2)` will probably give you enough detail to see the entire query.	[reply]
Re: Printing full SQL statement with placeholders by Masem (Monsignor) on Oct 24, 2001 at 20:29 UTC
While not a strict perl solution: Nearly every SQL server that I've encountered keeps a log of SQL requests, whether from command line or from script. The full version of the SQL (with all placeholders filled) should be available here. ----------------------------------------------------- Dr. Michael K. Neylon - mneylon-pm@masemware.com \|\| "You've left the lens cap of your mind on again, Pinky" - The Brain "I can see my house from here!" It's not what you know, but knowing how to find it if you don't know that's important	[reply]
Re: Re: Printing full SQL statement with placeholders by mpeppler (Vicar) on Oct 24, 2001 at 20:37 UTC
Sybase will do this only with auditing turned on. DBI->trace is probably a better solution, although it's probably too heavy for general use. Maybe DBI's PrintError/RaiseError functionality should know about the parameters passed to execute() or bind_param(), and display/include them in the error message? Of course in the case of DBD::Sybase the error message is generated by the driver, so it might not be so easy to generalize, unfortunately... Michael	[reply]
Re: Printing full SQL statement with placeholders by rayoslav (Novice) on Oct 24, 2001 at 23:07 UTC
heres what i do: `my $statement="SELECT data FROM table WHERE conditions = x"; my $sth=$sql->prepare("$statement"); $sth->execute(); #...(etc) print STDOUT "$statement";` [download] and then i clean it out after debugging	[reply] [d/l]