Creating stored procedure in Mysql with BLOB variable in it

Question

This is stored procedure that worked on earlier version of mysql. Now, I have Mysql 5.5.19.0 and I can not create stored procedure that look like this


DELIMITER $$

USE `XXX`$$

DROP PROCEDURE IF EXISTS `current_to_prev`$$

CREATE DEFINER=`user`@`localhost` PROCEDURE `current_to_prev`(
IN _USERNAME VARCHAR(45),
IN _FILENAME VARCHAR(45),
IN _TYPE INT
)
BEGIN
    DECLARE _uid INT;
    DECLARE _fdata BLOB;
    DECLARE _fname VARCHAR(45);
    DECLARE _type VARCHAR(45);
    SET _uid = (SELECT userid FROM USER WHERE username = _USERNAME);
    SET _fdata = (SELECT FileData FROM userfiles_current WHERE userid = _uid AND filename = _FILENAME);
    SET _fname = (SELECT FileName FROM userfiles_current WHERE userid = _uid AND filename = _FILENAME);
    SET _type = (SELECT TYPE FROM userfiles_current WHERE userid = _uid AND TYPE = _TYPE);
    UPDATE userfiles_prev SET FileData = _fdata, FileName = _fname, TYPE = _type WHERE userid = _uid AND filename = _FILENAME;
END$$

DELIMITER ;

I receive following error:

Query : CREATE DEFINER=user@localhost PROCEDURE current_to_prev( IN _USERNAME VARCHAR(45), IN _FILENAME VARCHAR(45), IN _TYPE INT ...

Error Code : 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 '_FILENAME VARCHAR(45), IN _TYPE INT ) BEGIN DECLARE _uid INT; DECLARE _fdata B' at line 3

As you can see MySQL can not accept BLOB variable. How can I solve this problem?

Thanks in advance!

ravnur ravnur · Accepted Answer · 2011-12-29T19:30:19

MySQL accepts params, variables and so on with type like blob.

The error in parameter IN _FILENAME - it is the reserved word. Change it something else and you will be fine.

Creating stored procedure in Mysql with BLOB variable in it

1 Answers