Set MySQL delimiter inside an sql-file

Question

I want to set the delimiter inside an sql file (because I cannot rely on users to do that through the terminal).

Is there a mysql statement that will allow me to set the delimiter?

Using

DELIMITER //

throws an error.

# Categories schema

# --- !Ups


CREATE  TABLE IF NOT EXISTS `category` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `pid` INT NULL DEFAULT 0 ,
  `label` VARCHAR(64) NULL ,
  `active` TINYINT NULL DEFAULT 0,
  PRIMARY KEY (`id`) );

DELIMITER //

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    category
                WHERE   pid = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, pid
                INTO    _id, _parent
                FROM    category
                WHERE   id = _parent;
        END LOOP;
END//

DELIMITER ;

# --- !Downs

#DROP TABLE category;

We got the following error: 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 hierarchy_connect_by_parent_eq_prior_id(value INT) ' at line 1 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

what does setting delimiter inside sql file means? can you be more clear? what are you trying to do? — dejjub-AIS
The error should also contain 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 part, and what's after near is quite important here. — Mchl
Running 5.5.21 CE. The code is contained in a .sql file and run from inside Play! framework (Scala) as a database evolution. I have added the error code to the question. — Joseph Tura

lxblanco lxblanco · Accepted Answer · 2013-02-27T17:46:21

In the version of MySql I use the same error occurs when using the delimiter command, but this version handles the delimiter ";" for statements and delimiter "|" for stored procedures and functions, which i think solves the problem; try this:

-- any comma-terminated statements here (select, create table, drop table, update, delete, etc)

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT

-- function body here

END |

-- other statements or functions here

Set MySQL delimiter inside an sql-file

2 Answers