Re: PDO and Exceptions

Vandegrift, Ken wrote:

Good Afternoon, I have an instance of a PDO object set to throw a PDOException on errors. However, it does not appear to do this when a primary key violation error occurs on an insert sql statement. An exception should be thrown in this case correct? The insert statements are part of a transaction but I am unable to rollback the transaction on error. Any ideas would be greatly appreciated. Code sample: /** Excerpt from DB connection class **/ $this->db = new PDO('odbc:DBNAME', $userid, $pwd); $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); /** * Updates order information in the database. * Called from Order::dbSaveOrder * @return boolean true on success * @access protected */ protected function dbUpdateOrder() { // VARIABLES TO TRACK PROPER SQL FORMATTING $count = count($this->attributes); $i = 1; // BUILD ORDER TABLE UPDATE QUERY $sql = "UPDATE " . ORDER_TABLE . " SET "; foreach ($this->attributes as $attribute => $value) { $attribute = strtoupper($attribute);

   $sql      .= (is_string($value) || is_null($value)) ? "{$attribute} =

'{$value}'" : "{$attribute} = {$value}"; if ($i < $count) {

    $i++;
    $sql .= ", ";

} } $sql .= " WHERE " . ORDER_TABLE_ID . " = :orderid;"; try { $this->db->beginTransaction(); $stmt = $this->db->prepare($sql); $stmt->execute(array(':orderid' => $this->attributes['order_id'])); /**** ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT TRIGGER AN EXCEPTION ON DUPLICATE ENTRIES ****/ // SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER if (!empty($this->gcnums)) {

    $sql = null;
    foreach ($this->gcnums as $itemid => $gcnumbers) {
      foreach ($gcnumbers as $gcnum) {
        $sql .= "INSERT INTO GIFTCARD_RECORD " .
                "(" .
                "ORDER_ID, " .
                "ITEM_ID, " .
                "GC_NUM " .
                ") " .
                "VALUES" .
                "(" .
                "{$this->__get('order_id')}, " .
                "{$itemid}, " .
                "'{$gcnum}'" .
                ");";
      } // End Inner FOREACH
       } // End Outer FOREACH
       $stmt = $this->db->prepare($sql);
     $stmt->execute();

} $this->db->commit(); $stmt = null; return true; }catch (PDOException $e) { $this->db->rollBack(); Log::write(LOG_DIR, 'order',

     "\r******** Order::dbUpdateOrder ********\r" .
     "[UPDATE SQL] => " . $sql . "\r" .
     "[DB ERROR] => " . $e->getMessage() . "\r" .
     "**************************************"

); $this->error = 'The system has encountered an error and is unable to update this order. ' .

     'Please contact the system administrator.';

$stmt = null; return false; } } Ken Vandegrift

I can't see a "try" in the second part. Wouldn't you need: try { update query try {

    insert query

} catch exception } catch exception ? I could be on the completely wrong track but I think you need a try / catch for each part. -- Postgresql & php tutorials http://www.designmagick.com/