How can I display SQL created by DBI

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

Replies are listed 'Best First'.
Re: How can I display SQL created by DBI by mpeppler (Vicar) on Jul 27, 2004 at 17:16 UTC
In general you can't see the SQL after the placeholders have been bound - at least not for those drivers and databases that use server-side prepare. The reason is that DBI never sees the SQL with the values inserted. Instead it uses the server API to send the SQL with the placeholders to the server, and then just sends the values for each `execute()` iteration. The `DBI->trace()` functionality may list the bound parameters - for example using `DBI->trace(3)` with DBD::Sybase will show this data (along with a lot of other stuff!) Michael	[reply] [d/l] [select]
Re: How can I display SQL created by DBI by davorg (Chancellor) on Jul 27, 2004 at 16:27 UTC
See the section on TRACING in the DBI docs. Unfortunately the exact feature you want (the SQL trace flag) is currently marked as "unimplemented", but the other trace data might give you clues to what is going on. -- <http://www.dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: How can I display SQL created by DBI by rir (Vicar) on Jul 27, 2004 at 18:09 UTC
Using `perl -d` I've seen my sql query text associated with `$DBI::lasth`. That might work for you. When I was following the DBI mailing list, a significant portion of people asking questions like this were trying to use placeholders for field or table names. This is not supported by either DBI or some of its drivers. Placeholders are only sure to work for the values in a sql statement. Be well.	[reply] [d/l] [select]
Re: How can I display SQL created by DBI by saberworks (Curate) on Jul 27, 2004 at 17:56 UTC
Doing a trace with MySQL will definitely show you the query. I don't know about other DBs. I guess it's better to test your query first then copy/paste it into perl code. Even still, it's frustrating to get "error in your sql syntax" but not have the actual query in front of you to look at.	[reply]
Re: How can I display SQL created by DBI by CountZero (Bishop) on Jul 27, 2004 at 17:43 UTC
Perhaps in the log-file of the DB-server, if you set its logging level high enough? CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: How can I display SQL created by DBI by etcshadow (Priest) on Jul 27, 2004 at 16:26 UTC
Well, I don't know that there isn't a standard way, but I do know there are ways which depend on which database you're using. Which database are you using (e.g. oracle, mysql, postgress, etc)? `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l]