4

I have a stored procedure, called "myprocedure". How can I call it from Magento?

I tried:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'CALL myprocedure()';
$results = $readConnection->fetchAll($query);
var_dump($results);

This gives me a PDO error, whats wrong?

Thanks!

4
  • this might lead you in the right direction: stackoverflow.com/questions/6282391/…
    – pzirkind
    Commented Jun 16, 2014 at 13:48
  • Thanks, but this is not for calling it, but for the creation. I need to actually call it from an observer. Commented Jun 16, 2014 at 13:49
  • 1
    what if you use $readConnection->exec($query) instead of fetchAll
    – pzirkind
    Commented Jun 16, 2014 at 13:58
  • @pzirkind. Please write you comment as an answer so the OP can accept it.
    – Marius
    Commented Jun 16, 2014 at 14:26

3 Answers 3

6

Try using prepare and execute instead:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'CALL myprocedure()';
$sql   = $readConnection->prepare($query);
$sql->execute();
$data  = $sql->fetchAll();
3

It seems that to execute stored procedures you would need to use the following code:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'CALL myprocedure()';
$results = $readConnection->exec($query); // this is the line that needs changing (use exec)

In other words use exec() instead of fetchAll()

0

If you want to use parameters in and out then you have to run like this to get output

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$connection->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); 
$results = $connection->query("SELECT @someOutParameter");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.