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?