PHP Database Access Logging Debugging Information and Errors

PHP Database Access

Logging Debugging Information and Errors

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.

Solution

Use PDO::errorCode() or PDOStatement::errorCode() after an operation to get an error code if the operation failed. The corresponding errorInfo() method returns more information about the error. 

Exampe  Printing error information

       $st = $db->prepare('SELECT * FROM imaginary_table');

       if (! $st) {

            $error = $db->errorInfo();

            print "Problem ({$error[2]})";

       }

Discussion

The errorCode() method returns a five-character error code. PDO uses the SQL 92 SQLSTATE error codes. By that standard, 00000 means “no error,” so a call to error Code() that returns 00000 indicates success.

The errorInfo() method returns a three-element array. The first element contains the five-character SQLSTATE code (the same thing that errorCode() returns). The second element is a database backend-specific error code. The third element is a database backend-specific error message.

Make sure to call errorCode() or errorInfo() on the same object on which you called the method that you’re checking for an error. In the prepare() method is called on the PDO object, so errorInfo() is called on the PDO object. If you want to check whether a fetch() called on a PDOStatement object succeeded, call error Code() or errorInfo() on the PDOStatement object.

One exception to this rule is when creating a new PDO object. If that fails, PDO throws an exception. It does this because otherwise there’d be no object on which you could call errorCode() or errorInfo(). The message in the exception details why the con‐ nection failed.

To have PDO throw exceptions every time it encounters an error, call setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) on your PDO object after it’s created. This way, you can handle database problems uniformly instead of larding your code with repeated calls to errorCode() and errorInfo().

Example  Catching database exceptions

       try {

             $db = new PDO('sqlite:/tmp/zodiac.db');

             // Make all DB errors throw exceptions

             $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

             $st = $db->prepare('SELECT * FROM zodiac');

             $st->execute();

             while ($row = $st->fetch(PDO::FETCH_NUM)) {

                    print implode(',',$row). "<br/>\n";

             }

       } catch (Exception $e) {

             print "Database Problem: " . $e->getMessage();

       }

Handling PDO errors as exceptions is useful inside of transactions, too. If there’s a problem with a query once the transaction’s started, just roll back the transaction when handling the exception.

Similar to the exception error mode is the “warning” error mode. setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING) tells PDO to issue warnings when a database error is encountered. If you prefer to work with regular PHP errors instead of exceptions, this is the error mode for you. Set up a custom error handler with set_error_handler() to handle E_WARNING level events and you can deal with your database problems in the error handler.

Whatever the error mode, PDO throws an exception if the initial PDO object creation fails. When using PDO, it’s an extremely good idea to set up a default exception handler with set_exception_handler(). Without a default exception handler, an uncaught exception causes the display of a complete stack trace if display_errors is on. If an exception is thrown when connecting to the database, this stack trace may contain sensitive information, including database connection credentials.