Stored Procedure: Error 1064

Question

I am receiving an error 1064 on the following stored procedure:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_photo;
14 CREATE PROCEDURE add_python_photo(  IN fname VARCHAR,
15                                     IN fpath VARCHAR,
16                                     IN fdata LONGBLOB,
17                                     IN python INT,
18                                     IN is_preferred TINYINT,
19                                     OUT python_photo_id INT)
20 BEGIN
21     INSERT INTO python_photo (  file_name,
22                                 file_path,
23                                 file_data,
24                                 python_id,
25                                 preferred)
26                         VALUES (fname,
27                                 fpath,
28                                 fdata,
29                                 python,
30                                 is_preferred);
31 
32     SELECT LAST_INSERT_ID() INTO python_photo_id;
33 END//
34 DELIMITER ;

---

mysql> source stored_procedures/add-python-photo.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): 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 '
                                                                    IN fpath VARCHAR,
                                                                    IN fdata LONGBLOB,
                                                                    IN python INT,
' at line 1

I'm uncertain as to what syntactic error MySQL is referring to. The list of 'IN' parameters appears to be syntactically correct - yet, that appears to be the source of the error. It seems to fail while processing the 'IN python_id INT' parameter.

---

UPDATE

Regarding the DELIMITER suggestion, below is another stored procedure - which executes correctly:

12 DELIMITER //
13 DROP PROCEDURE IF EXISTS add_python_parent;
14 CREATE PROCEDURE add_python_parent( IN python_id INT,
15                                     IN mother_id INT,
16                                     IN father_id INT)
17 BEGIN
18     INSERT INTO python_parent (python_id, mother_id, father_id)
19         VALUES (python_id, mother_id, father_id);
20 END//
21 DELIMITER ;

Execution:

mysql> source stored_procedures/add-python-parent.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Answer 1

Your problem is the not the syntax of the stored procedure but the DELIMITER statement.

There are two simple solutions

SOLUTION #1

Change the DELIMITER after you drop the procedure

DROP PROCEDURE IF EXISTS add_python_photo;
DELIMITER //  
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SOLUTION #2

Use the DELIMITER // with the DROP PROCEDURE IF EXISTS

DELIMITER //  
DROP PROCEDURE IF EXISTS add_python_photo //
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SUGGESTION

You should change the variables so that they are not the exact same name as the DB Columns

CREATE PROCEDURE add_python_photo(IN given_file_name VARCHAR(20),
                                  IN given_file_path VARCHAR(20),
                                  IN given_file_data LONGBLOB,
                                  IN given_python_id INT,
                                  IN given_preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (given_file_name,
                              given_file_path,
                              given_file_data,
                              given_python_id,
                              given_preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

GIVE IT A TRY !!!

UPDATE 2016-10-10 13:51 EDT

Turns out to be the use of VARCHAR in the list of Stored Proc variables. While Other RDBMS products will allow for VARCHAR, MySQL will not accept it. VARCHAR variables must be followed by a width definition such as VARCHAR(20) in MySQL's Stored Proc variable list. This was pointed out by @ypercube in the comments, so all kudos for him on this one today.