MySQL create function fails

Question

This

CREATE FUNCTION getTagID(tag VARCHAR(100)) RETURNS BIGINT

BEGIN
DECLARE tagID BIGINT;
            SET tagID = (SELECT id FROM tags WHERE text = tag);
IF tagID IS NULL
THEN
            (INSERT INTO tags (text) VALUES (tag));
            SET tagID = (SELECT LAST_INSERT_ID());
END IF;
RETURN tagID;
END

fails with

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 4

Why?

This function is related to the problem here:

mysql insert if not exists on joined tables

Have you set delimiter to something different from `;`? – Mikhail Vladimirov Mar 21 '13 at 13:09 — Mikhail Vladimirov, Mar 21 '13 at 13:09

score 3 · Accepted Answer · answered Mar 21 '13 at 13:09

3

Try to change the DELIMITER and remove the parenthesis around the INSERT statement.

DELIMITER $$
CREATE FUNCTION getTagID(tag VARCHAR(100)) 
RETURNS BIGINT
BEGIN
    DECLARE tagID BIGINT;
    SET tagID = (SELECT id FROM tags WHERE text = tag);
    IF tagID IS NULL THEN
        INSERT INTO tags (text) VALUES (tag); -- don't put inside parenthesis
        SET tagID = (SELECT LAST_INSERT_ID());
    END IF;
    RETURN tagID;
END $$
DELIMITER ;

answered Mar 21 '13 at 13:09

John Woo

258,903
69
498
492

that seems to work and you are the hero of my day... but could you explain what's the matter with "DELIMITER"? – Raphael Jeger Mar 21 '13 at 13:12
`DELIMITER` changes the termination symbol on the query and allowing you to execute multiple statements that contains `;`. Remember that every after `;` closes the statement that is why when changing to another symbol allows you to execute multiple statement without breaking it. – John Woo Mar 21 '13 at 13:14

score 2 · Answer 2 · answered Mar 21 '13 at 13:11

Type to set delimiter to ;; like this:

DELIMITER ;;

CREATE FUNCTION getTagID(tag VARCHAR(100)) RETURNS BIGINT
BEGIN
  DECLARE tagID BIGINT;
  SET tagID = (SELECT id FROM tags WHERE text = tag);
  IF tagID IS NULL
  THEN
    INSERT INTO tags (text) VALUES (tag);
    SET tagID = (SELECT LAST_INSERT_ID());
  END IF;
  RETURN tagID;
END;;

DELIMITER ;

Delimiter is used by MySQL client (not sever) to split input into separate queries. Default delimiter is ; so MySQL client sent the following query to the server:

CREATE FUNCTION getTagID(tag VARCHAR(100)) RETURNS BIGINT

BEGIN
DECLARE tagID BIGINT

Which is incomplete and thus incorrect.

MySQL create function fails

2 Answers2