distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by lemon_juice
2 years ago
Category: General
127 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
2 years ago
4973 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
2 years ago
127 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
2 years ago
127 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
2 years ago
127 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
2 years ago
127 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
2 years ago
4973 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
2 years ago
127 posts since Tue, 29 Jun 10
Okay, no problem!
Written by ansgar
2 years ago
4973 posts since Fri, 07 Apr 06
Fixed in r4115
Written by lemon_juice
2 years ago
127 posts since Tue, 29 Jun 10
Thanks, works very well now!
 

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