PDO and Exceptions

------_=_NextPart_001_01C69565.B1846693
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Good Afternoon,
=20
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?
=20
The insert statements are part of a transaction but I am unable to
rollback the transaction on error.
=20
Any ideas would be greatly appreciated.
=20
Code sample:
=20
/** Excerpt from DB connection class **/
$this->db =3D new PDO('odbc:DBNAME', $userid, $pwd);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
=20
/**
* Updates order information in the database.
* Called from Order::dbSaveOrder
* @return boolean true on success
* @access protected
*/
protected function dbUpdateOrder() {
=20
// VARIABLES TO TRACK PROPER SQL FORMATTING
$count =3D count($this->attributes);
$i =3D 1;
=20
// BUILD ORDER TABLE UPDATE QUERY
$sql =3D "UPDATE " . ORDER_TABLE . " SET ";
foreach ($this->attributes as $attribute =3D> $value) {
$attribute =3D strtoupper($attribute);
$sql .=3D (is_string($value) || is_null($value)) ? "{$attribute}=
=3D
'{$value}'" : "{$attribute} =3D {$value}";
if ($i < $count) {
$i++;
$sql .=3D ", ";
}
}
$sql .=3D " WHERE " . ORDER_TABLE_ID . " =3D :orderid;";
=20
try {
$this->db->beginTransaction();
$stmt =3D $this->db->prepare($sql);
$stmt->execute(array(':orderid' =3D> $this->attributes['order_id']));
=20
/**** ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT TRIGGER
AN EXCEPTION ON DUPLICATE ENTRIES ****/
=20
// SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
if (!empty($this->gcnums)) {
$sql =3D null;
foreach ($this->gcnums as $itemid =3D> $gcnumbers) {
foreach ($gcnumbers as $gcnum) {
$sql .=3D "INSERT INTO GIFTCARD_RECORD " .
"(" .
"ORDER_ID, " .
"ITEM_ID, " .
"GC_NUM " .
") " .
"VALUES" .
"(" .
"{$this->__get('order_id')}, " .
"{$itemid}, " .
"'{$gcnum}'" .
");";
} // End Inner FOREACH
} // End Outer FOREACH
$stmt =3D $this->db->prepare($sql);
$stmt->execute();
}
$this->db->commit();
$stmt =3D null;
return true;
}catch (PDOException $e) {
$this->db->rollBack();
Log::write(LOG_DIR, 'order',
"\r******** Order::dbUpdateOrder ********\r" .
"[UPDATE SQL] =3D> " . $sql . "\r" .
"[DB ERROR] =3D> " . $e->getMessage() . "\r" .
"**************************************"
);
$this->error =3D 'The system has encountered an error and is unable=
to
update this order. ' .
'Please contact the system administrator.';
$stmt =3D null;
return false;
}
}
=20
Ken Vandegrift
=20

------_=_NextPart_001_01C69565.B1846693--

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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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]=20
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,
> =20
> I have an instance of a PDO object set to throw a PDOException on=20
> errors. However, it does not appear to do this when a primary key=20
> violation error occurs on an insert sql statement. An exception=20
> should be thrown in this case correct?
> =20
> The insert statements are part of a transaction but I am unable to=20
> rollback the transaction on error.
> =20
> Any ideas would be greatly appreciated.
> =20
> Code sample:
> =20
> /** Excerpt from DB connection class **/ $this->db =3D new=20
> PDO('odbc:DBNAME', $userid, $pwd);=20
> $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> =20
> /**
> * Updates order information in the database.
> * Called from Order::dbSaveOrder
> * @return boolean true on success
> * @access protected
> */
> protected function dbUpdateOrder() {
> =20
> // VARIABLES TO TRACK PROPER SQL FORMATTING
> $count =3D count($this->attributes);
> $i =3D 1;
> =20
> // BUILD ORDER TABLE UPDATE QUERY
> $sql =3D "UPDATE " . ORDER_TABLE . " SET ";
> foreach ($this->attributes as $attribute =3D> $value) {
> $attribute =3D strtoupper($attribute);
> $sql .=3D (is_string($value) || is_null($value)) ? "{$attribut=
e}
=3D
> '{$value}'" : "{$attribute} =3D {$value}";
> if ($i < $count) {
> $i++;
> $sql .=3D ", ";
> }
> }
> $sql .=3D " WHERE " . ORDER_TABLE_ID . " =3D :orderid;";
> =20
> try {
> $this->db->beginTransaction();
> $stmt =3D $this->db->prepare($sql);
> $stmt->execute(array(':orderid' =3D> $this->attributes['order_id'])=
);
> =20
> /**** ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT=20
> TRIGGER AN EXCEPTION ON DUPLICATE ENTRIES ****/
> =20
> // SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
> if (!empty($this->gcnums)) {
> $sql =3D null;
> foreach ($this->gcnums as $itemid =3D> $gcnumbers) {
> foreach ($gcnumbers as $gcnum) {
> $sql .=3D "INSERT INTO GIFTCARD_RECORD " .
> "(" .
> "ORDER_ID, " .
> "ITEM_ID, " .
> "GC_NUM " .
> ") " .
> "VALUES" .
> "(" .
> "{$this->__get('order_id')}, " .
> "{$itemid}, " .
> "'{$gcnum}'" .
> ");";
> } // End Inner FOREACH
> } // End Outer FOREACH
> $stmt =3D $this->db->prepare($sql);
> $stmt->execute();
> }
> $this->db->commit();
> $stmt =3D null;
> return true;
> }catch (PDOException $e) {
> $this->db->rollBack();
> Log::write(LOG_DIR, 'order',
> "\r******** Order::dbUpdateOrder ********\r" .
> "[UPDATE SQL] =3D> " . $sql . "\r" .
> "[DB ERROR] =3D> " . $e->getMessage() . "\r" .
> "**************************************"
> );
> $this->error =3D 'The system has encountered an error and is unable=20
> to update this order. ' .
> 'Please contact the system administrator.';
> $stmt =3D null;
> return false;
> }
> }
> =20
> Ken Vandegrift
> =20
>=20

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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php