0

Is it possible to create a function from within a Magento installer script? I currently have the following code:

$installer = $this;
$installer->startSetup();
$installer->run("
         DROP function IF EXISTS {$this->getTable('getLatestActivity')};
         CREATE FUNCTION {$this->getTable('getLatestActivity')} (activityid int) RETURNS int(11)
         BEGIN

         set @rank = 0;

         set @matchingId := (select id from (
         SELECT   @rank := @rank+1 AS rank, t_act.id, t_act.date
         FROM      activityupdates ut_act
         LEFT JOIN activityupdates AS t_act ON t_act.ticked = 1
             AND t_act.date <= ut_act.date AND t_act.customer_id = ut_act.customer_id AND t_act.type = ut_act.type
         WHERE ut_act.id = activityid
         ORDER BY t_act.date DESC
         )ranked
         where ranked.rank = 1);
         return @matchingId;
         END;
     ");

But I get an exception thrown SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 - not very helpful.

To get this running in PHPMyAdmin, I've had to add lines regarding DELIMITERS, but I think thats a drawback of the GUI rather than the FUNCTION requiring them. If I include them, I just get an exception about defining a DELIMITER instead.

$installer->run("

         DROP function IF EXISTS `{$this->getTable('getLatestActivity')}`;
         DELIMITER $$
         CREATE FUNCTION `{$this->getTable('getLatestActivity')}` (activityid int) RETURNS int(11)
         BEGIN

         set @rank = 0;

         set @matchingId := (select id from (
         SELECT   @rank := @rank+1 AS rank, t_act.id, t_act.date
         FROM      activityupdates ut_act
         LEFT JOIN activityupdates AS t_act ON t_act.ticked = 1
             AND t_act.date <= ut_act.date AND t_act.customer_id = ut_act.customer_id AND t_act.type = ut_act.type
         WHERE ut_act.id = activityid
         ORDER BY t_act.date DESC
         )ranked
         where ranked.rank = 1);
         return @matchingId;
         END$$

         DELIMITER ;

     ");

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE FUNCTION getLatestActivity (activityid int)

3
  • You definitely need to change the delimiter as otherwise you cannot use the semicolon between BEGIN and END.
    – mam08ixo
    Sep 15 '15 at 10:46
  • Have added the code that states the delimeter. Sep 15 '15 at 10:53
  • My statement regarding delimiters turned out to be incorrect when Zend_Db/PDO are involved. See my answer below.
    – mam08ixo
    Sep 15 '15 at 11:58
1

Mage_Core_Model_Resource_Setup::run() accepts multiple queries, splitting the given argument (sql statement) at semicolons. This leads to the function body being split into multiple separate calls. The function declaration is then incomplete and the function creation fails with the given exception message.

Additionally, as opposed to using the MySQL CLI client (or PHPMyAdmin) directly, defining a delimiter leads to a PDOException. Nils Preuß received a solution from the Zend_Db mailing list a few years ago:

  1. Don't worry about DELIMITER, you don't need it.
  2. You must DROP and CREATE in two separate statements.
  3. Bypass the default ZF query method.

To sum up, the following snippet will lead to the function being created:

$this->getConnection()->query("DROP function IF EXISTS `{$this->getTable('getLatestActivity')}`");
$this->getConnection()->query("
CREATE FUNCTION `{$this->getTable('getLatestActivity')}` (activityid int) RETURNS int(11)
BEGIN
    set @rank = 0;
    set @matchingId := (select id from (
        SELECT   @rank := @rank+1 AS rank, t_act.id, t_act.date
        FROM      activityupdates ut_act
        LEFT JOIN activityupdates AS t_act ON t_act.ticked = 1
            AND t_act.date <= ut_act.date
            AND t_act.customer_id = ut_act.customer_id
            AND t_act.type = ut_act.type
        WHERE ut_act.id = activityid
        ORDER BY t_act.date DESC
    ) ranked
    where ranked.rank = 1);
    return @matchingId;
END;
");
2
  • An extended explanation would be great, but I assume run is doing something a direct query isn't doing? Sep 15 '15 at 12:07
  • But it's better to use exec instead of query. It works for me rather than query
    – Anthony
    Jan 27 '16 at 18:35

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.