Re: [2.1] Create stored procedure via evolutions-script (MySQL)

[James Roper]

This is MySQL specific, nothing to do with Play or JDBC.  A quick google search reveals this:

http://forums.mysql.com/read.php?39,130834,130853#msg-130853

On Sunday, 16 December 2012 02:24:59 UTC+11, Benjamin wrote:

Hi, 

I don't know if this is a Play (Scala) or JDBC specific problem. This piece of code works perfect from MySQL-console or phpMyAdmin: 

# Create group-procedure.
DELIMITER //
CREATE PROCEDURE create_group (
    IN name VARCHAR(64),
    IN description VARCHAR(255),
    IN owner INT
)
BEGIN
    DECLARE group_id bigint(20);
    START TRANSACTION;
        INSERT INTO groups (name, description)
        VALUES (name, description);

        SET group_id = (SELECT id FROM groups ORDER BY id DESC LIMIT 1);
    COMMIT;

    INSERT INTO user_privileges (uid, gid, level)
    VALUES (owner, group_id, 50);

END //
DELIMITER ;

But when I append it to the 1.sql-file and run the evoultions script, I receive the following error: 

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 PROCEDURE create_group ( IN name VARCHAR(64), IN description' at line 2 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

It seems that JDBC isn't able to parse the DELIMITER-keyword and fails to execute the script? 

My question is if someone have a solution to this problem. I can always create a separate file with all the stored procedures for the database and do it manually, but it would be much nicer to have everything in a single file. 

Thank you

Benjamin

[René Grüner Vangsgaard]

Maybe this is relevant? http://stackoverflow.com/questions/12483025/play-framework-2-0-evolutions-and-create-trigger

Summary: The DELIMITER keyword is Mysql, remove it, and escape semicolon (;) with double-semicolon (;;).