RE: [PHP-DB] PDO and Exceptions

Thanks. That was my issue. I had to separate each insert instead of grouping them together and executing the query. Exception is now thrown if a primary key violation occurs and entire transaction is rolled back. Ken Vandegrift -----Original Message----- From: Chris [mailto:dmagick@gmail.com] Sent: Wednesday, June 21, 2006 5:39 PM To: Vandegrift, Ken Cc: php-db@lists.php.net Subject: Re: [PHP-DB] 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/