How do I call a stored procedure from Magento code?

Question

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!

Answer 1

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();

Answer 2

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()

Answer 3

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");