RE: Transactions and PDO

["Dale D. Attree" <Dale.Attree@xxxxxxxxxxxx>]

Why not create a Stored Procedure to handle the second table?

-----Original Message-----
From: Vandegrift, Ken [mailto:kvandegrift@xxxxxxxxxx] 
Sent: Wednesday, June 21, 2006 7:20 PM
To: php-windows@xxxxxxxxxxxxx
Subject:  Transactions and PDO

Just wanted to throw this out there to see if anyone may be able to help
with a PDO transaction that does not appear to be working.  I want to
update an order record in the database and also insert giftcard numbers
associated with this same order into another table.  So I am trying to
do one update in the customer order table and possibly several inserts
into a giftcard record table.  I purposely use the same giftcard number
to trigger a primary key violation and attempt to rollback the
transaction if this occurs.  I know users of the system will likely
enter the same giftcard numer at some point (although I will have
validation schemes in place to prevent db errors)
 
Running Windows 2003 Server IIS6 and PHP 5.1.2
 
Code example:
 
/**
  * 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']));
   $stmt = null;
   // 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();
   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
 

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