Magento:SQL升级脚本中的存储过程 [英] Magento: Stored Procedures in SQL Upgrade Scripts

问题描述

我一直在尝试在Magento的SQL升级脚本中添加存储过程创建步骤. 喜欢

I've been trying to put a stored procedure creation step into my SQL upgrade script in Magento. like

$this->startSetup();
$sql = <<< __SQLPRC
CREATE PROCEDURE my_proc(
    IN length int
    ,IN column_used_for varchar(50)
    ,OUT return_id bigint
)
BEGIN
    DECLARE count_unused INT;
    SET count_unused = 0;
    select 
            id into return_id
        from 
            my_table
        where 
            used_status=0
            limit 1;

    if length(return_id) = length 
    then
        update my_table
            set 
                used_status=1
            where 
                id = return_id;
    else
        set return_id = 0;
    end if;
END;
__SQLPRC;

try {
$this->run($sql);
}
catch(Exception $e)
{ echo "<pre>" . $e->getTraceAsString(); }

$this->endSetup();

我从调试中得出的结论是，Magento只是抓住了SQL，直到第一个分号";"为止 有人可以帮我吗?

What I've concluded from my debugging is that Magento just grabs the SQL till first semi-colon ";" Can somebody help me with this?

推荐答案

您已将存储过程代码放入:-

You have put the Stored Procedure code as:-

$this->startSetup();
$sql = <<< __SQLPRC
...

这必须是:-

$this->startSetup();
$sql = <<<__SQLPRC
...

现在应该可以使用了，因为根据

This should work now, as there shouldn't be any space after the "<<<" according to the Heredoc Syntax of PHP String Delimiting.

希望有帮助.

更新后的答案:-

您可以尝试使用以下代码:-

Can you try using this code:-

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

而不是:-

$this->run($sql);

我早些时候在Magento上看到了一些与此有关的问题.

I have seen some issues earlier regarding this on Magento.

这篇关于Magento:SQL升级脚本中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！