Can't edit function: "You have an error in your SQL syntax"

User, date Message
Written by lemon_juice
1 year ago
Category: General
94 posts since Tue, 29 Jun 10 		Today I wanted to make a stored function deterministic and Heidi has a problem with that. After checking the "Deterministic" checkbox and pressing "Save" I get this error:

/* SQL 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 'COLLATE utf8_polish_ci

BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm' at line 8 */



And this is the offending SQL as copied from the log:

CREATE DEFINER=`interfoto2`@`%` FUNCTION `HeidiSQL_temproutine_1`(`path` VARCHAR(255), `delim` VARCHAR(30))
RETURNS varchar(1024)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Zwraca pelna sciezke do kategorii w postaci tekstowej'
COLLATE utf8_polish_ci

BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm VARCHAR(255);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i TINYINT UNSIGNED DEFAULT 0;
IF delim IS NULL THEN SET delim = ' > '; END IF;
REPEAT
SET segm = SUBSTRING_INDEX(path, '/', 1);
SET path = SUBSTR(path, LENGTH(segm)+2);

SET out_segm = (SELECT name FROM sklep_kategorie WHERE cat_id=segm LIMIT 1);

IF i > 0 THEN
SET result = CONCAT(result, delim);
END IF;
SET result = CONCAT(result, out_segm);
SET i = i + 1;
UNTIL path='' OR path IS NULL OR i > 100 END REPEAT;
RETURN result;
END;



I think the problem is with the COLLATE definition which seems to be not allowed in CREATE FUNCTION syntax.
Written by ansgar
1 year ago
3951 posts since Fri, 07 Apr 06 		You have the "COLLATE utf8_polish_ci" in your SQL body I guess? How did that come in there?
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		Well, "COLLATE utf8_polish_ci" is not in the function body - I thought you would know how it got in there :). OK, more information: "COLLATE utf8_polish_ci" is the specified collation of the "RETURNS varchar(1024)" value and I think Heidi got confused by that. Here is the original function definition as returned by "SHOW CREATE FUNCTION GET_CAT_PATH_AS_TEXT":

CREATE DEFINER=`interfoto2`@`%` FUNCTION `GET_CAT_PATH_AS_TEXT`(`path` VARCHAR(255), `delim` VARCHAR(30)) RETURNS varchar(1024) CHARSET utf8 COLLATE utf8_polish_ci
READS SQL DATA
COMMENT 'Zwraca pelna sciezke do kategorii w postaci tekstowej'
BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm VARCHAR(255);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i TINYINT UNSIGNED DEFAULT 0;
IF delim IS NULL THEN SET delim = ' > '; END IF;
REPEAT
SET segm = SUBSTRING_INDEX(path, '/', 1);
SET path = SUBSTR(path, LENGTH(segm)+2);

SET out_segm = (SELECT name FROM sklep_kategorie WHERE cat_id=segm LIMIT 1);

IF i > 0 THEN
SET result = CONCAT(result, delim);
END IF;
SET result = CONCAT(result, out_segm);
SET i = i + 1;
UNTIL path='' OR path IS NULL OR i > 100 END REPEAT;
RETURN result;
END



So "CHARSET utf8 COLLATE utf8_polish_ci" is there but Heidi puts it in the wrong place and strips "CHARSET utf8".
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		One more thing: when I view the "Routine body" in Heidi then I can see "COLLATE utf8_polish_ci" as the first line of the body, before the "BEGIN" statement. So I think it's already wrong at that stage - no sql should be present before "BEGIN", right?

PS. Don't be confused by READS SQL DATA instead of CONTAINS SQL in my post above - I changed it myself.
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		Have you been able to look into this? I'm using the latest nightly and still I'm not able to edit this function in Heidi.
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		I can create a bug report so it doesn't get lost if you don't have time to work on this now.
Written by ansgar
1 year ago
3951 posts since Fri, 07 Apr 06 		Didn't have the time yet. No bug report please. Here's the place with all details. Trying to find some time soon.
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		Okay, no problem!
Written by ansgar
1 year ago
3951 posts since Fri, 07 Apr 06 		Fixed in r4115
Written by lemon_juice
1 year ago
94 posts since Tue, 29 Jun 10 		Thanks, works very well now!
 

Please login to leave a reply, or register at first.