Calling stored procedure in a prepared statement multiple times

I'm using PHP to process some XML that I'm getting from a web service and to insert the results into a database. I've created a stored procedure to insert the records and I'm using PDO to prepare a statement that I'd then like to execute multiple times, something like this:

$st = $this->db->prepare('CALL AddClient(:Name, :Id, :Priority, :Territory, :DateCreated, :Result)');
$st->bindParam(':Name', $Name, PDO::PARAM_STR);
$st->bindParam(':Id', $Id, PDO::PARAM_INT);
$st->bindParam(':Priority', $Priority, PDO::PARAM_STR);
$st->bindParam(':Territory', $Territory, PDO::PARAM_STR);
$st->bindParam(':DateCreated', $dateFormat, PDO::PARAM_STR);    
$st->bindParam(':Result', $result, PDO::PARAM_STR);
foreach ($this->xml->AccountsDataSet->MainRecord as $Account) {
                  $Name = (string) $Account->AcctDesc;
                  $Id = (int) $Account->AcctID;
                  if ($num = (int)$Account->CDF309607) {
                      $Priority = self::$priorityFields[$num];                    
                  } else {
                      $Priority = '';
                  }
                  if (! $Territory = (string) $Account->AcctState) {
                      $Territory = '';
                  }
                  $date = new DateTime($Account->AcctCreationDate, new DateTimeZone('UTC'));
                  $dateFormat = $date->format('Y-m-d H:i:s');
                  $st->execute();
                  echo $result . '<br />';
              }

In this example ':Result' would be a output value from the procedure. I cannot make this work at all though. If I omit the out put parameter both in the procedure code and PHP then one row gets inserted but subsequent calls in the loop fail. To make this work I have to prepare the statement fresh each time in the loop, then binding the parameters to the statement again. I still cannot make this work with an output parameter in the procedure and have to Select a value in the procedure to get some return value to PHP to process.

Does anyone know where I'm going wrong with this? Ideally I'd like to prepare the statement once and then loop through the results, executing it each time with fresh data. Are there any tricks that I'm missing to make all of this work? I'm developing on Windows 7 with Apache 2.2.17, PHP 5.3.5 and MySQL 5.5.8.

EDIT: Well, the answer to executing the statement multiple times in the loop seems to be to call the PDOStatement::closeCursor() method each time after calling PDOStatement::execute(). Still no idea on how to get the value of an output parameter returned to PHP though.

EDIT Here's the code for the procedure:

CREATE
PROCEDURE foo.AddClient(IN ClientName VARCHAR(255), IN Client_Id INT UNSIGNED, IN Priority VARCHAR(255), IN Territory VARCHAR(100), IN DateCreated DATETIME, OUT Result VARCHAR(100))
  COMMENT 'Procedure to add a new client to the database'
BEGIN
  #Declare variables.
  DECLARE Priority_Id, Territory_Id  TINYINT UNSIGNED DEFAULT NULL;
  # Check if a Priority is defined. If so get the id, if not add it and get the id
  IF LENGTH(Priority) > 0 THEN
    SELECT
      PriorityId
    INTO
      Priority_Id
    FROM
      client_priority
    WHERE
      Name = Priority;
    IF Priority_Id IS NULL THEN
      INSERT INTO client_priority (Name) VALUES (Priority);
      SET Priority_Id = LAST_INSERT_ID();
    END IF;
  END IF;
  #Check if a Territory is defined. If so get the id, if not add it and get the id.
  IF LENGTH(Territory) > 0 THEN
    SELECT
      TerritoryId
    INTO
      Territory_Id
    FROM
      territories
   开发者_JAVA百科 WHERE
      Name = Territory;
    IF Territory_Id IS NULL THEN
      INSERT INTO territories (Name) VALUES (Territory);
      SET Territory_Id = LAST_INSERT_ID();
    END IF;
  END IF;
  #Add the details of the client.
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    SET Result = 'Client already exists'; # Error handler in case client with the same name already exists.  
    INSERT INTO clients
      (ClientId, Name, PriorityId, TerritoryId, DateCreatedSalesNet) VALUES (Client_Id, ClientName, Priority_Id, Territory_Id, DateCreated);
    SET Result = 'Client Inserted';
  END;
END

---

The fact is you can't access you result like this, see these posts :

• http://bugs.mysql.com/bug.php?id=25970
• http://dev.mysql.com/doc/refman/5.0/en/call.html

So you will need to store the result into a MySQL var, then you can display it, like this :

<?php
// change 
$st = $this->db->prepare('CALL AddClient(:Name, :Id, :Priority, :Territory, :DateCreated, :Result)');
// to
$st = $this->db->prepare('CALL AddClient(:Name, :Id, :Priority, :Territory, :DateCreated, @result)');

// remove this line
$st->bindParam(':Result', $result, PDO::PARAM_STR);

// change
$st->execute();
echo $result . '<br />';
// to 
$st->execute();
echo $this->db->query('SELECT @result')->fetchColumn() . '<br />';

---

Try adding a string length to the output parameter:

$st->bindParam(':Result', $result, PDO::PARAM_STR, 4000);

---

Try to add PDO::PARAM_INPUT_OUTPUT:

 $st->bindParam(':Result', $result, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);