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
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?