CODEWITHSUNDEEP
phpmysqlstored-procedurespdozend-framework2
Erik Urrutia Santamaria
Erik Urrutia Santamaria

Reputation: 23

Trouble with multiple select in stored procedure zend framework 2.2 tablegateway

I'm working with stored procedures in PDO-mysql with Zend framework 2.2, and the tablegateway library.

A generic example of a stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_proc`(IN id SMALLINT)
BEGIN
     SELECT * FROM TABLE_A WHERE ID_TABLE=ID;
     SELECT * FROM TABLE_B WHERE ID_FK=ID;
END

and this is function from model

public function __construct(Adapter $adapter = null, $databaseSchema = null, ResultSet $selectResultPrototype = null)
    {
        return parent::__construct('', $adapter, $databaseSchema, 
            $selectResultPrototype);
    }

public function listaServicio()
   {
    $dbAdapter=$this->getAdapter();
        $stmt = $dbAdapter->createStatement(); 
        $stmt->prepare('CALL sp_proc(:id)'); 
        $id=15;
        $stmt->getResource()->bindParam(':id', $id, \PDO::PARAM_INT); 
        $resultado=$stmt->execute();
        while ($resultado->next()) {
          var_dump($resultado->current());
        }
    }

I only have the result from the "first" select (table_a), but could not get the result from "second"(table_b). What am I missing?

Upvotes: 1

Views: 629

Answers (1)

Erik Urrutia Santamaria
Erik Urrutia Santamaria

Reputation: 23

I answer myself, maybe it's wil work to someone.

public function listaServicio()
{
$stmt = $dbAdapter->createStatement(); 
$stmt->prepare('CALL sp_proc(:id)'); 
$id=15;
$stmt->getResource()->bindParam(':id', $id, \PDO::PARAM_INT); 
$resultado=$stmt->execute();
$result1=$stmt->getResource()->fetchAll(\PDO::FETCH_OBJ);
var_dump($result1);
$stmt->getResource()->nextRowset();
$result2=$stmt->getResource()->fetchAll(\PDO::FETCH_OBJ);
var_dump($result2);
$stmt->getResource()->closeCursor();
}

Upvotes: 1

Related Questions