PHP : Database Access - [10.11] Logging Debugging Information and Errors

10.11.1 Problem

You want access to information to help you debug database problems. For example, when a query fails, you want to see what error message the database returns.

10.11.2 Solution

Use DB::isError( ) to investigate the results of a single query:

$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");
DB::isError($sth) and print 'Database Error: '.$sth->getMessage();

Use DB::setErrorHandling( ) to automatically take action on any database error:

$dbh->setErrorHandling(PEAR_ERROR_PRINT);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

10.11.3 Discussion

When they encounter an error, most PEAR DB methods return an DB_Error object. The DB::isError( ) method returns true if it's passed a DB_Error object, so you can use that to test the results of individual queries. The DB_Error class is a subclass of PEAR::Error, so you can use methods such as getMessage( ) to display information about the error. If you want to display everything in the error object, use print_r( ):

$sth = $dbh->query('SELECT aroma FROM zodiac WHERE element LIKE 'fire'");
if (DB::isError($sth)) {
    print_r($sth);
}

Since there is no aroma column in the zodiac table, this prints:

db_error Object
(
    [error_message_prefix] => 
    [mode] => 1
    [level] => 1024
    [code] => -19
    [message] => DB Error: no such field
    [userinfo] => SELECT aroma FROM zodiac WHERE element LIKE 'fire' \
[nativecode=1054 ** Unknown column 'aroma' in 'field list']
    [callback] => 
)

Using setErrorHandling( ) lets you define a behavior that's invoked automatically whenever there's a database error. Tell setErrorHandling( ) what to do by passing it a PEAR_ERROR constant. The PEAR_ERROR_PRINT constant prints the error message, but program execution continues:

$dbh->setErrorHandling(PEAR_ERROR_PRINT);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

This prints:

DB Error: no such field

To print out an error message and then quit, use PEAR_ERROR_DIE. You can also use the PEAR_ERROR_CALLBACK constant to run a custom function when an error is raised. This custom function can print out even more detailed information:

function pc_log_error($error_obj) {
    error_log(sprintf("%s (%s)",$error_obj->message,$error_obj->userinfo));
}

$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,'pc_log_error');
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

When the incorrect SQL in the $dbh->query( ) method raises an error, pc_log_error( ) is called with the DB_Error object passed to it. The pc_log_error( ) callback uses the properties of the DB_Error object to print a more complete message to the error log:

DB Error: no such field (SELECT aroma FROM zodiac WHERE element 
LIKE 'fire' [nativecode=Unknown column 'aroma' in 'field list'])

To capture all the data in the error object and write it to the error log, use print_r( ) with output buffering in the error callback:

function pc_log_error($error_obj) {
    ob_start();
    print_r($error_obj);
    $dump = ob_get_contents();
    ob_end_clean();
    error_log('Database Error: '.$dump);
}

$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,'pc_log_error');
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

This includes all of the error object's fields in the error log message:

Database Error: db_error Object
(
    [error_message_prefix] => 
    [mode] => 16
    [level] => 1024
    [code] => -19
    [message] => DB Error: no such field
    [userinfo] => SELECT aroma FROM zodiac WHERE element LIKE 'fire' \
[nativecode=1054 ** Unknown column 'aroma' in 'field list']
    [callback] => pc_log_error
)

You can also have a DB_Error generate an internal PHP error with PEAR_ERROR_TRIGGER:

$dbh->setErrorHandling(PEAR_ERROR_TRIGGER);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

With the PEAR_ERROR_TRIGGER constant, setErrorHandling( ) uses PHP's trigger_error( ) function to generate an internal error. This error is handled by PHP's default error handler or a user-defined error handler set by set_error_handler( ). By default, the internal error is an E_USER_NOTICE :

<br />
<b>Notice</b>:  DB Error: no such field in <b>/usr/local/lib/php/PEAR.php</b> \
on line <b>593</b><br />

Make the error an E_USER_WARNING or E_USER_ERROR by passing a second argument to setErrorHandling( ):

$dbh->setErrorHandling(PEAR_ERROR_TRIGGER,E_USER_ERROR);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

If the error is an E_USER_ERROR, program execution terminates after displaying the error message:

<br />
<b>Fatal error</b>:  DB Error: no such field in <b>/usr/local/lib/php/PEAR.php</b> 
on line <b>593</b><br />