0

i need to create custom sql function, use it and delete it with magento install script. But it do not work. how to create sql function in magento install scipt? P. S. if i create this function in my database and then only use it in script it works

/* @var $installer Enterprise_CatalogPermissions_Model_Mysql4_Setup */
$installer = $this;

$installer->startSetup();

$connection = $installer->getConnection();

try {
    $connection->beginTransaction();
    $sql = "
    DROP FUNCTION IF EXISTS `regex_not_match_replace`;
        DELIMITER ||
        CREATE FUNCTION  `regex_not_match_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
        RETURNS VARCHAR(1000)
        DETERMINISTIC
        BEGIN
         DECLARE temp VARCHAR(1000);
         DECLARE ch VARCHAR(1);
         DECLARE i INT;
         SET i = 1;
         SET temp = '';
         IF original NOT REGEXP pattern THEN
          loop_label: LOOP
           IF i>CHAR_LENGTH(original) THEN
            LEAVE loop_label;
           END IF;
           SET ch = SUBSTRING(original,i,1);
           IF NOT ch NOT REGEXP pattern THEN
            SET temp = CONCAT(temp,ch);
           ELSE
            SET temp = CONCAT(temp,replacement);
           END IF;
           SET i=i+1;
          END LOOP;
         ELSE
          SET temp = original;
         END IF;
         RETURN temp;
        END||
        DELIMITER ;

        UPDATE catalog_product_entity_copy
        SET  sku = regex_not_match_replace('^[ҐЄ-ЇА-ЯЁёA-Z0-9\\/~*#@$%^&.,|_?!+-}{[.left-parenthesis.][.right-parenthesis.]]{1,}$','', sku)
        WHERE not sku REGEXP '^[ҐЄ-ЇА-ЯЁёA-Z0-9\\/~*#@$%^&.,|_?!+-}{[.left-parenthesis.][.right-parenthesis.]]{1,}$';

    DROP FUNCTION IF EXISTS `regex_not_match_replace`;
    ";

    $connection->query($sql);
}
catch (Exception $e) {
    $connection->rollback();
    throw $e;
}
$connection->commit();

$installer->endSetup();
?>
0

Seems it is not possible or maybe i need some permissions. My decision is that i add this function by hands in database

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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