PDO: Getting Started - Handling Connection Errors

(Page 2 of 2 )

In the case of a connection error, the script will immediately terminate unless the returnedPDOExceptionobject is properly caught. Of course, you can easily do so using the exception-handling syntax first introduced in Chapter 8. The following example shows you how to catch the exception in case of a connection problem:

<?php
    try {
        $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");
    } catch (PDOException $exception) {
        echo "Connection error: " . $exception->getMessage();
    }
?>

Once a connection has been established, it’s time to begin using it. This is the topic of the rest of this chapter.

Handling Errors

PDO offers three error modes, allowing you to tweak the way in which errors are handled by the extension:

PDO_ERRMODE_EXCEPTION: Throws an exception using thePDOExceptionclass, which will immediately halt script execution and offer information pertinent to the problem.

PDO_ERRMODE_SILENT: Does nothing if an error occurs, leaving it to the developer to both check for errors and determine what to do with them. This is the default setting.

PDO_ERRMODE_WARNING: Produces a PHPE_WARNINGmessage if a PDO-related error occurs.

To set the error mode, just use thesetAttribute()method, like so:

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

There are also two methods available for retrieving error information. Both are introduced next.

Retrieving SQL Error Codes

The SQL standard offers a list of diagnostic codes used to signal the outcome of SQL queries, known as SQLSTATE codes. Execute a Web search for SQLSTATE codes to produce a list of these codes and their meanings. TheerrorCode()method is used to return this standard SQLSTATE code, which you might choose to store for logging purposes or even for producing your own custom error messages. Its prototype follows:

int PDOStatement::errorCode()

For instance, the following script attempts to insert a new product but mistakenly refers to a column named name, which is not allowed because it’s an Oracle keyword:

<?php
    try {
        $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");
    } catch (PDOException $exception) {
        echo "Connection error: " . $exception->getMessage();
    {

    $query = "INSERT INTO products(product_id, sku,name)
              VALUES(5, 'SS873221', 'Surly Soap') ";

    $dbh->exec($query);

    echo $dbh->errorCode();
?>

This should produce the code2A506, however at the time of publication Oracle’s PDO implementation of this feature wasn’t yet complete, resulting inHY000to be returned instead, which is the SQLSTATE code for “generic error”:

--------------------------------------------
HY000
--------------------------------------------

Oracle’s PDO implementation does, however, return a correct error message, which can be quite helpful in debugging an unexpected problem. This feature is introduced next.

Retrieving SQL Error Messages

TheerrorInfo()method produces an array consisting of error information pertinent to the most recently executed database operation. Its prototype follows:

array PDOStatement::errorInfo()

This array consists of three values, each referenced by a numerically indexed value between0and2:

0: Stores the SQLSTATE code as defined in the SQL standard

1: Stores the database driver–specific error code

2: Stores the database driver–specific error message

The following script demonstrateserrorInfo(), causing it to output error information pertinent to a missing table (in this case, the programmer mistakenly uses the plural form of the existingproductstable):

<?php 

    try {
        $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");
    } catch (PDOException $exception) {
        echo "Failed to obtain database handle " . $exception->getMessage();
    }

    $query = "INSERT INTO products(product_id, sku,title)
              VALUES('SS873221', 'Surly Soap') ";

    $dbh->exec($query);

    print_r($dbh->errorInfo());

?>

Presuming theproductstable doesn’t exist, the following output is produced (formatted for readability):

--------------------------------------------
Array (
    [0] => HY000
    [1] => 942
    [2] => OCIStmtExecute: ORA-00942: table or view does not exist
              (ext\pdo_oci\oci_driver.c:326)  )
--------------------------------------------

Please check back next week for the continuation of this article.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus