DanFromGermany DanFromGermany - 5 months ago 13
MySQL Question

Yet another MySQL syntax error

This isn't something I usually have problems with but I don't understand that one now. I tried with backticks, with double quotes, without backticks,...

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 '' at line 7

CREATE PROCEDURE mapping(
p_object INT(10),
brand VARCHAR(255),
model VARCHAR(255))
BEGIN
INSERT INTO `object_brand_model_mapping`
SET `object`=p_object, `brandNameNormalized`=brand, `modelNameNormalized`=model;
END;


I also tried

INSERT INTO `object_brand_model_mapping`
(`object`, `brandNameNormalized`, `modelNameNormalized`)
VALUES
(p_object, brand, model);


which produces exactly the same error.

I don't know what's wrong with this. Do any special rules apply to
INSERT
in a procedure?

All of the columns do exist.

Answer

The parser is getting confused by having a common delimiter between defining the procedure and the procedure contents. To remedy this, define a different delimiter before creating the procedure:

DELIMITER $$

CREATE PROCEDURE mapping(
    p_object INT(10),
    brand VARCHAR(255),
    model VARCHAR(255))
BEGIN
    INSERT INTO `object_brand_model_mapping` (`object`, `brandNameNormalized`, `modelNameNormalized`)
        VALUES (p_object, brand, model);
END$$

DELIMITER ;

From Alex Silverstein's article on the subject:

The next step is to change the default MySQL script parser’s delimiter from semicolon (;) to double-dollar sign ($$). The reason you do this is so that the semicolons after each statement in the body of the routine are not interpreted by the parser as meaning the end of the CREATE PROCEDURE statement. This is because the entire CREATE PROCEDURE block, from CREATE PROCEDURE to END is actually a single statement that must be executed by itself. Were it not for the delimiter change, the script would break, since there each statement inside BEGIN and END would execute individually. Note that you can use a variety of non-reserved characters to make your own custom delimiter.

Comments