Mysql Create Stored Procedure Error 1064
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Error Code 1064 In Mysql Stored Procedure
Learn more about Stack Overflow the company Business Learn more about hiring developers delimiter mysql or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack error 1064 42000 Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Create Procedure Yields Error 1064 up vote
Mysql Error 1064
0 down vote favorite 1 Good Morning, I'm creating the following procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_inventory_audit`( IN `pitem_id` int, IN `puser_id` int, IN `pfield_name` varchar(265), IN `pold_value` mediumtext, IN `pnew_value` mediumtext ) BEGIN INSERT INTO inventory_audit (item_id, user_id, field_name, old_value, new_value) VALUES (pitem_id, puser_id, pfield_name, pold_value, pnew_value); END$$ It is being copied to our new server running MySQL 5.5.19 from our old server running MySQL 5.0.45. When I excecute the above code on the new server, I recieve the following 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 '' at line 11 Does this mean that each entry inside the VALUES parentheses must be surrounded by '' eg. 'pitem_id' ? mysql mysql-error-1064 share|improve this question asked Jan 4 '12 at 18:42 SidC 1,44384295 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted You need to have DELIMTER $$ before the create statement. share|improve this answer answered Jan 4 '12 at 18:47 kcdragon 1,274715 add a comment| up vote 2 down vote You didn't change the delimiter from the default ;, so the ; you're using there is actually terminating the procedure, not the query. DELIMITER $$ <--- add this line CREATE .... ... END$$ share|improve this answer answered Jan 4 '12 at 18:47 Marc B 273k24233352 Thnx This works for me –Armaan Dec 18 '14 at 14:34 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign u
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Mysql: #1064 error in stored procedure up vote 1 down vote favorite Whenever I try to create the below http://stackoverflow.com/questions/8732137/mysql-create-procedure-yields-error-1064 stored procedure. CREATE PROCEDURE purchaseItem (IN productID INT, IN quantity INT, IN price DECIMAL(10,2) , IN memID INT) BEGIN DECLARE qnty INT DEFAULT 0; SELECT p_Unit INTO qnty FROM product WHERE p_ID = productID; IF qnty >= quantity && qnty != 0 THEN INSERT INTO purchase VALUES (NULL, productID, quantity, price, memID, 0, NULL); UPDATE product SET p_Unit = (qnty - quantity) WHERE p_ID = productID; END IF; END; I get the error http://stackoverflow.com/questions/12658955/mysql-1064-error-in-stored-procedure #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 '' at line 3 Can someone please point out me where I'm doing wrong. Thanks! mysql stored-procedures syntax share|improve this question edited Apr 24 '13 at 1:39 Aaron Bertrand 165k18265320 asked Sep 30 '12 at 5:41 don 2372521 What happens if you execute the procedure body line by line - does it work, or do you get the same error? –Anders Lindahl Sep 30 '12 at 5:53 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted Don't forget to change the DELIMITER, try the following query without using SELECT..INTO DELIMITER $$ CREATE PROCEDURE purchaseItem ( IN productID INT, IN quantity INT, IN price DECIMAL(10,2) , IN memID INT ) BEGIN DECLARE qnty INT; SET qnty = (SELECT p_Unit FROM product WHERE p_ID = productID); IF qnty >= quantity && qnty != 0 THEN INSERT INTO purchase VALUES (NULL, productID, quantity, price, memID, 0, NULL); UPDATE product SET p_Unit = (qnty - quantity) WHERE p_ID = productID; END IF; END$$ DELIMITER ; share|improve this answer edited Sep 30 '12 at 5:48 answered Sep 30 '12 at 5:43 John Woo 165k30276329 add
here for a quick overview of the site Help Center Detailed answers to any questions you might http://stackoverflow.com/questions/31225207/error-1064-in-stored-procedure-in-mysql have Meta Discuss the workings and policies of this site About http://stackoverflow.com/questions/26508967/mysql-error-1064-stored-procedure-creation-error Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, error 1064 just like you, helping each other. Join them; it only takes a minute: Sign up Error #1064 in stored procedure in mysql up vote -1 down vote favorite if cant to understand my english is because my english is bad.... i will try to explain my problem as best as possible. I try to create mysql create stored a stored procedure in mysql (phpmyadmin) but i have this 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 '' at line 3 This is the code of stored procedure: CREATE PROCEDURE `sp_user_insert` (IN `sp_email` varchar(255), IN `sp_password` text, IN `sp_role` int) BEGIN DECLARE `sp_encrypted_password` text; SET `sp_encrypted_password` = SELECT MD5(`sp_encrypted_password`); INSERT INTO `user`( `user_email`, `user_password`, `user_role_id`) VALUES ( `sp_email`, `sp_encrypted_password`, `sp_role`); END And this is the code of the table user: CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_email` varchar(255) NOT NULL, `user_password` text NOT NULL, `user_role_id` int(11) NOT NULL, PRIMARY KEY (`user_id`) ); PD: I'm sorry, my english is bad :C mysql stored-procedures phpmyadmin share|improve this question asked Jul 4 '15 at 21:22 Nac-Ho 42 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted This will help. Try once. DELIMITER $$; CREATE PR
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up mysql error (#1064) stored procedure creation error up vote 0 down vote favorite I'm trying to create a stored procedure but i keep getting 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 'NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' B' at line 3 My SQL is as follows: DELIMITER $$ CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) ) RETURNS VARCHAR NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN (select GROUP_CONCAT(DISTINCT id_category SEPARATOR ',') from cms_prodcategories where id_parent=idcat and active='1'); END$$ I've tried to include DELIMITER but no result. I guess the problem might be in the quotes. Can someone point me out the solution? Thanks! MySQL version 5.5 mysql stored-procedures phpmyadmin share|improve this question edited Oct 22 '14 at 15:22 asked Oct 22 '14 at 13:45 Luis 327 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted You need a varchar length on the return statement of the procedure something as RETURNS VARCHAR(1024) So the procedure becomes DELIMITER $$ CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) ) RETURNS VARCHAR(1024) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN (select GROUP_CONCAT(DISTINCT id_category SEPARATOR ',') from cms_prodcategories where id_parent=idcat and active='1'); END; $$ share|improve this answer answered Oct 22 '14 at 14:15 Abhik Chakraborty 37k41737 Thanks. There was a problem with VARCHAR definition but a new error appears: #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 '' at line 9 - used phpmyadmin –Luis Oct 22 '14 at 15:25 Tried with SQL Manager and it worked but first i needed to set this GLOBAL: SET GLOBAL log_bin_trust_function_creators = 1; Thanks! –Luis Oct 22 '14 at 15:32 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up