Symfony2 + Stored procedure || How to fetch the result?
mysql, php, stored-procedures, symfony, symfony-2.4
Solution
SOLUTION The following is the solution I finally found. It is not the best one as there is no Mapping anymore but in my case it is not necessary. Further: I needed to change to the the getConnection to fetch the PDO_MySQL and further change to `prepare` and `fetch()` functions. The vardump show's now the appropriate result.
// set doctrine
$em = $this->getContainer()->get('doctrine')->getManager()->getConnection();
// prepare statement
$sth = $em->prepare("CALL JobQueueGetJob(1)");
// execute and fetch
$sth->execute();
$result = $sth->fetch();
// DEBUG
if ($input->getOption('verbose')) {
$output->writeln(var_dump($result));
}
Output:
array(5) {
'msgId' =>
string(3) "122"
'msgTypeCode' =>
string(1) "1"
'jobCode' =>
string(22) "http://mail.google.com"
'jstate' =>
string(1) "A"
'created_at' =>
string(19) "2014-02-01 03:58:42"
}
Problem
I am currently struggling with fetching results out of `nativquery` in Symfony 2.4.3. To be simple I'am currently building a JobQueue/MsgQueue System which will just add/remove jobs in the queue. The Procedure will fetch the first job, set it active and SHOULD return the whole result. Exactly there is the problem - I can't fetch anything. I used this as my example: How to execute Stored Procedures with Doctrine2 and MySQL Here is the code I use in a `ConsoleCommand Class`: ``` protected function execute(InputInterface $input, OutputInterface $output) { ## start $output->writeln('<comment>Starting JobQueue Ping process</comment>'); // set doctrine $em = $this->getContainer()->get('doctrine')->getManager(); $rsm = new ResultSetMapping; $result = $em->createNativeQuery( 'CALL JobQueueGetJob (' . ':jobTypeCode' . ')', $rsm ); $result->setParameters(array('jobTypeCode' => 1)); $result->execute(); $em->flush(); if ($input->getOption('verbose')) { $output->writeln(var_dump($result->getResult())); } } ``` Here you go with the Procedure code and the result: Code ``` PROCEDURE `JobQueueGetJob`(IN `jobType` TINYINT(2)) BEGIN DECLARE jId int(11); SELECT `msgId` into jId FROM `jobqueue` WHERE `MsgTypeCode` = jobType AND `jState` = 'N' LIMIT 1; IF jId IS NOT NULL THEN UPDATE `jobqueue` SET `jState` = 'A' WHERE `msgId` = jId; SELECT * FROM `jobqueue` WHERE `msgId` = jId; END IF; END ``` Result via phpMyAdmin ``` Your SQL query has been executed successfully 0 rows affected by the last statement inside the procedure SET @p0 = '1'; CALL `JobQueueGetJob` ( @p0 ); ``` As the text suggest it will be no result returned but the last statement inside the procedure which should be the Query itself's. SOLUTION (Not the best) Command: ``` // set doctrine $em = $this->getContainer()->get('doctrine')->getManager()->getConnection(); // prepare statement $sth = $em->prepare("CALL JobQueueGetJob(1)"); // execute and fetch $sth->execute(); $result = $sth->fetch(); // DEBUG if ($input->getOption('verbose')) { $output->writeln(var_dump($result)); } ``` Output: ``` array(5) { 'msgId' => string(3) "122" 'msgTypeCode' => string(1) "1" 'jobCode' => string(22) "http://mail.google.com" 'jstate' => string(1) "A" 'created_at' => string(19) "2014-02-01 03:58:42" } ```