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
– Mikhail Vladimirov

2013-03-21 13:09:39 +00:00
Commented Mar 21, 2013 at 13:09 — Mikhail Vladimirov
– Mikhail Vladimirov, Commented Mar 21, 2013 at 13:09

John Woo · Accepted Answer · 2013-03-21 13:09:25Z

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, 2013 at 13:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raphael Jeger Over a year ago

that seems to work and you are the hero of my day... but could you explain what's the matter with "DELIMITER"?

John Woo Over a year ago

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.

Mikhail Vladimirov · Accepted Answer · 2013-03-21 13:11:04Z

2

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.

answered Mar 21, 2013 at 13:11

Mikhail Vladimirov

13.9k1 gold badge40 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL create function fails

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related