20.3 DBI Debug Techniques

Sometimes the code that talks to the database server doesn't seem to work. It's important to know how to debug this code at the DBI level. Here is how this debugging can be accomplished.

To log a trace of DBI statement execution, you must set the DBI_TRACE environment variable. The PerlSetEnv DBI_TRACE directive must appear before you load Apache::DBI and DBI.

For example, if you use Apache::DBI, modify your httpd.conf file with:

PerlSetEnv DBI_TRACE "3=~/tmp/dbitrace.log"
PerlModule Apache::DBI

Replace 3 with the trace level you want. The traces from each request will be appended to /tmp/dbitrace.log. Note that the logs will probably be interleaved if requests are processed concurrently.

Within your code, you can control trace generation with the trace( ) method:

DBI->trace($trace_level)
DBI->trace($trace_level, $trace_filename)

DBI trace information can be enabled for all handles using this DBI class method. To enable trace information for a specific handle, use the similar $dbh->trace method.

Using the trace option with a $dbh or $sth handle is useful to limit the trace information to the specific bit of code that you are debugging.

The trace levels are:

0

Trace disabled

1

Trace DBI method calls returning with results

2

Trace method entry with parameters and exit with results

3

As above, adding some high-level information from the driver and also adding some internal information from the DBI

4

As above, adding more detailed information from the driver and also including DBI mutex information when using threaded Perl

5+

As above, but with more and more obscure information