DBI's tracing facilities

There are a couple things you should read first, in the official DBI documentation:

• the TRACING section
• the DBI_TRACE environment variable
• the DBI->trace() API call
• the TraceLevel attribute
• optionally the DBI->parse_trace_flags() API call

For a CGI script, I typically do this:

• $dbh->trace(2, "/tmp/pbupdate_dbilog.$$");

The generated log files are a bit hard to read. This Vim syntax file makes reading them slightly easier:

• copy syntax/dbi_trace.vim into your ~/.vim/ folder
• When viewing the file in Vim, run :set syntax=dbi_trace

An example session of me focusing on the SQL statements that were run:

• rm -f /var/tmp/sql.log # the next line appends, not overwrites
• DBI_TRACE='1=/var/tmp/sql.log' perl script.pl
• grep "FETCH('Statement')\|prepare_cached\|bind_param" /var/tmp/sql.log | less
  • ^^^ TODO: this might not be totally refined yet

More verbose:

• DBI_TRACE='2=/var/tmp/sql.log' perl script.pl
• grep -i 'prepare for .*\| execute for .*\|Binding parameters.*\|parse_params .*\|dbd_st_execute returning.*row_num.*\|fetchrow_array= .*\|dbd_st_fetch.*currow.*' /var/tmp/sql.log | less

Even more verbose, which includes the data returned in the result set:

• DBI_TRACE='SQL|4=/var/tmp/sql.log' perl script.pl
• grep 'FETCH= \[' /var/tmp/sql.log | less