Categories
CMS Joomla Mastering Development

Using the SQL `Delimiter` statement in component update

For the next version of my component I need split some data into different tables, which requires some rather more complex SQL than I would usually use. The problem is that there doesn’t seem to be a way to use the Delimiter command – I have a fairly simple bit of code that I’m using in /admin/sql/updates/0.0.60.sql:

CREATE TABLE IF NOT EXISTS `increment` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER #

CREATE PROCEDURE do_while()
BEGIN
  DECLARE v1 INT DEFAULT 100;
  WHILE 0 < v1  DO
    INSERT INTO increment VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END#

DELIMITER ;

But when I try to run it I get the message: 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' at line 1

Changing the delimiter character doesn’t seem to have any effect. I have found some very old threads suggesting that in the 1.* versions there was no way to use multi-line statements because the delimiter is fixed by the underlying SQL library – is that still the case now? Is there another way around this in more modern versions of Joomla?

Leave a Reply

Your email address will not be published. Required fields are marked *