1

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)
2

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.

| improve this answer | |
  • I've tried modifying the DELIMITER statement as suggested - to no avail. Note, I have more stored procedures in this project which utilize the exact same order of statements, with regards to the DELIMITER/DROP statements - with no issues. I've applied your suggestion with regards to the variable names though. – hermetik Oct 10 '16 at 17:33
  • @Rolando shouldn't that VARCHAR be VARCHAR(20) or whatever? – ypercubeᵀᴹ Oct 10 '16 at 17:45
  • @ypercubeᵀᴹ you are right. I think other DBMS products will accept VARCHAR only in Stored Proc definitions, but not MySQL. You nailed the problem, so you should write that answer and I'll upvote it. – RolandoMySQLDBA Oct 10 '16 at 17:47
  • Just edit this one. I'm on a phone – ypercubeᵀᴹ Oct 10 '16 at 17:50
  • @ypercubeᵀᴹ OK I'll do that. – RolandoMySQLDBA Oct 10 '16 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.