SQL Syntax error 1604 in CREATE PROCEDURE statement [duplicate]

Question

I am using this SQL query to generate a procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS GetSessionID //
CREATE PROCEDURE GetSessionID(IN token VARCHAR(64), OUT id INTEGER)
BEGIN
    SELECT s.ID INTO id FROM Sessions AS s WHERE s.Token = token;
END //

DELIMITER ;

I get the following error message:

Error 1064: 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 //

DROP PROCEDURE IF EXISTS GetSessionID //
CREATE PROCEDURE "GetSessi' at line 1

I tried using also $$ as a delimiter, surrounding the procedure's name with double quotes and removing the space before the delimiters in the DROP statement and END statement. If I look at the reference, there is a space after the procedure name - I tried adding that too, and still doesn't work.

I am using Go's db.Exec method to execute queries for creating tables and procedures, one after another.

I don't understand why do I get this error, as the syntax looks correct to me. Worked also in Goland's SQL runner, doesn't work in Go.

Answer 1

I found out what the problem actually was: The MySQL Go driver for the database/sql library I am using, go-sql-driver does not support multi statements by default. They can be activated as in here, although there are some limitations, documented there.

In conclusion, the problem is framework-related. I hope other people facing the same problem in Go will find this useful, as it isn't so obvious at the first glance. See also this Stack Overflow question

EDIT: the DELIMITER statement isn't supported by the server itself, but by the MySQL command line. It won't work even as a single statement.

Answer 2

Please try the below code snippet.

DROP PROCEDURE statement needs to be ended by ';'. One small correction is needed with table alias.

DELIMITER //

DROP PROCEDURE IF EXISTS GetSessionID;

CREATE PROCEDURE GetSessionID(IN token VARCHAR(64), OUT id INTEGER)
BEGIN
  SELECT s.ID INTO id FROM Sessions s WHERE s.Token = token;
END //
    
DELIMITER ;