2

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?

Your Answer

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

Browse other questions tagged or ask your own question.