Mysql Error 1064 Create Function
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 Learn more about Stack Overflow the company Business declare variable mysql Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs
Mysql Delimiter
Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just
Error 1064 (42000)
like you, helping each other. Join them; it only takes a minute: Sign up Unable to CREATE FUNCTION in MYSQL ERROR 1064 (42000) up vote 0 down vote favorite Hi I'm using MySQL to create a function:
Mysql Create Procedure
CREATE FUNCTION INSERTGROUP(name VARCHAR(50)) RETURNS INT NOT DETERMINISTIC BEGIN DECLARE 'idGroup' INT; IF (NOT EXISTS (SELECT groupID FROM groupsTable WHERE groupName = 'name')) THEN INSERT INTO groupsTable (groupName) VALUES ('name'); SELECT groupID INTO 'idGroup' FROM groupsTable WHERE groupName='name'; RETURN 'idGroup'; END// But I get this error when I try to create it: 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 ''idGroup' INT; IF (NOT EXISTS (SELECT groupID FROM serverGroupsTable WHERE gro' at line 5 I've been through forums and other questions similar to this one but I can not make it create the function. What I'm doing wrong? Is the syntax correct? Do I need to add something else? Ok this is what I've try: CREATE FUNCTION INSERTGROUP(name VARCHAR(255)) RETURNS INT NOT DETERMINISTIC BEGIN DECLARE idGroup INT; IF (NOT EXISTS (SELECT groupID FROM groupsTable WHERE groupName = name)) THEN INSERT INTO groupsTable (groupName) VALUES (name); SELECT groupID INTO idGroup FROM groupsTable WHERE groupName=name; RETURN idGroup; END// and CREATE FUNCTION INSERTGROUP(name VARCHAR(255)) RETURNS INT NOT DETERMINISTIC BEGIN DECLARE idGroup INT; IF (NOT EXISTS (SELECT groupID FROM groupsTable WHERE groupName = 'name')) THEN INSERT INTO groupsTable (groupName) VALUES ('name'); SELECT groupID INTO idGroup FROM groupsTable WHERE groupName='name'; RETURN idGroup; END// and for the last two I got this error: 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 '' at line 13 Similar to the one above Also I do DELIMITER // to change it from ; mysql function share|improve this question edited Apr 30 '13 at 0:51 Jocelyn 7,46242242 asked Nov 14 '12 at 18:04 afsg77 168315
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 http://stackoverflow.com/questions/13384581/unable-to-create-function-in-mysql-error-1064-42000 up MySQL Create Procedure Yields Error 1064 up vote 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 http://stackoverflow.com/questions/8732137/mysql-create-procedure-yields-error-1064 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 D
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 http://stackoverflow.com/questions/17368710/cant-build-mysql-function-getting-error-1064 this site About Us Learn more about Stack Overflow the company Business http://stackoverflow.com/questions/3831077/creating-functions-in-mysql-doesnt-work-error-1064 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 error 1064 Can't build MySQL Function, getting Error 1064 up vote 1 down vote favorite I am just starting to try MySQL functions and when trying to build the below function I get an error. -> DELIMITER // -> CREATE FUNCTION addD(d VARCHAR(20), rt VARCHAR(40), a VARCHAR(5), de VARCHAR(35)) -> RETURNS VARCHAR(20) -> BEGIN -> DECLARE ret VARCHAR(20); -> IF (SELECT count(DN) FROM DB.TABLE WHERE mysql error 1064 DN = d) > 0 THEN ret=concat(s,' - exists!'); -> ELSE ret = 'clear'; -> IF ret = 'clear' THEN (INSERT INTO DB.TABLE (DN,ROUTE,ACTIVE,Description) VALUES (dn,rt,a,de)); -> RETURN ret; -> END // The error I get is: 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 '= CONCAT(dn,' - exists!'); ELSE ret = 'clear'; IF ret = 'clear' THEN (IN' at line 1 Any help would be great. Please be gentle as I am new to MySQL functions. mysql sql stored-procedures stored-functions share|improve this question edited Jun 28 '13 at 16:42 asked Jun 28 '13 at 15:49 jcsbrotha 50112 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Your IF syntax is incorrect and you need to use SET or SELECT to assign a value to a variable. DELIMITER // CREATE FUNCTION addD(d VARCHAR(20), rt VARCHAR(40), a VARCHAR(5), de VARCHAR(35)) RETURNS VARCHAR(20) BEGIN DECLARE ret VARCHAR(20); IF (SELECT count(DN) FROM DB.TABLE WHERE DN = d) > 0 THEN SET ret = CO
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 Creating functions in mysql doesnt work - Error 1064 up vote 0 down vote favorite I tried this example via phpMyAdmin http://www.databasejournal.com/features/mysql/article.php/3569846/MySQL-Stored-Functions.htm mysql> DELIMITER | mysql> CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT) RETURNS INT DETERMINISTIC BEGIN DECLARE avg INT; SET avg = (n1+n2+n3*2+n4*4)/8; RETURN avg; END| This worked DELIMITER | The next statement gave: Error SQL query: CREATE FUNCTION WEIGHTED_AVERAGE( n1 INT, n2 INT, n3 INT, n4 INT ) RETURNS INT DETERMINISTIC BEGIN DECLARE avg INT; MySQL said: Documentation #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 5 mysql phpmyadmin mysql-error-1064 stored-functions share|improve this question asked Sep 30 '10 at 13:42 siliconpi 2,908114792 1 Which MySQL version are you using ? I'd say it's phpAdmin that bugs out, as this works from the command line client –nos Sep 30 '10 at 13:45 hi nos - your solution worked - it was fine from the command line. want to add that as an answer? –siliconpi Sep 30 '10 at 14:19 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote As it is mentioned in the link: As mentioned in the first stored procedures tutorial, we declare the "|" symbol as a delimiter, so that our function body can use ordinary ";" characters You can write a lot of commands on different consecutive lines. But usually only when ';' is met, the hole statement is executed. Putting a DELIMITER character means that MySQL should wait until this is closed no matter if you use ';' or not and only then to interpret what is between delimiters. share|improve this answer edited Sep 30 '10 at 14:14 answered Sep 30 '10 at 14:08 CristiC 12k84276 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post