Magento: Create Stored Procedure

Question

I am trying to create a Magento module installer, that will in turn create a stored procedure.

The code for the procedure has been run through: Toad, phpmyadmin, and mysql.exe command line. It worked in all 3. However, it fails when Magento goes to execute it during the installation process.

Error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

Magento Code:

    $installer = $this;
    $installer->startSetup();
    $sql = <<<____SQL
       DELIMITER // 

       CREATE PROCEDURE GetStuff(
           IN pSomeId int(11)
       )
       BEGIN
         DECLARE pOtherId INT;

       SELECT some_var INTO pOtherId FROM some_table WHERE id = pSomeId;
       END;//
       DELIMITER ;
       ____SQL;

     $installer->run($sql);
     $installer->endSetup();

There's some other code in between, of course, but simplifying did not change the error. My guess is that it is something to do with Delimeters and how PDO/Magento is treating them. Thank you.

Answer 1

I figured it out.

I did not need to use any delimiters, PDO can take care of them on its own.

But I did need to switch the method from query (used by the installer) to exec.

So, I ended up with:

 $write = Mage::getSingleton('core/resource')->getConnection('core_write');
 $write->exec($sql);