entity framework mysql - error creating function

Question

I'm trying to create a function in my mySQL database using entity framework migrations. The SQL works great when I run it directly in MySQL Workbench but it fails when it runs in the EF migration.

How can I create a function using EF migration?

     Sql(@"
DROP function IF EXISTS `f_JumperName`;

DELIMITER $$
CREATE FUNCTION `f_JumperName` 
(
    JumperID int
)
RETURNS varchar(500)
BEGIN
    DECLARE result VARCHAR(500);

    SELECT j.LastName + ', ' + j.FirstName
    INTO result
    FROM Jumper j
    WHERE j.JumperID = JumperID;

    RETURN x;
END$$

DELIMITER ;
");

Error 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 $$ CREATE FUNCTION f_JumperName (...

Diana · Accepted Answer · 2017-03-02 19:41:39Z

6

It seems the problem is caused by the DELIMITER sentences.

To work around it you can split your SQL in sentences, so you don't need to redefine the ; delimiter. You get two separate sentences, one for the drop and one for the create function:

public override void Up()
{
    Sql(@"DROP function IF EXISTS `f_JumperName`;");
    Sql(@"CREATE FUNCTION `f_JumperName` 
        (
            JumperID int
        )
        RETURNS varchar(500)
        BEGIN
            DECLARE result VARCHAR(500);
            SELECT j.LastName + ', ' + j.FirstName
            INTO result
            FROM Jumper j
            WHERE j.JumperID = JumperID;

            RETURN x;
        END;");
}

And you replace your previous $$ characters with ; at the end of the second sentence.

This is not fixing the real problem, which is not being able to set the delimiter correctly, but it works. I found some pages about really changing the delimiter through the MySqlScript.Delimiter property provided by the MySQL connector, but I don't know if it is a good practice (or even possible) to use it in a migration file, because you would have to access the real connection to the database somehow. I'm not really sure this can be used here.

edited Mar 2, 2017 at 19:41

answered Nov 9, 2016 at 14:20

Diana

2,2561 gold badge21 silver badges32 bronze badges

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

2 Comments

jhilden Over a year ago

Thank you very much Diana, that worked. It would be nice if the same command that runs in mySQL Workbench worked in EF as well, but I'm happy to have a work around.

youen Over a year ago

I was doubting this answer at first, but actually, it works great :) Works for CREATE PROCEDURE as well.

Collectives™ on Stack Overflow

entity framework mysql - error creating function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related