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

lemon_juice's profile image lemon_juice posted 12 years ago in General Permalink
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.
ansgar's profile image ansgar posted 12 years ago Permalink
You have the "COLLATE utf8_polish_ci" in your SQL body I guess? How did that come in there?
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
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".
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
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.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
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.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
I can create a bug report so it doesn't get lost if you don't have time to work on this now.
ansgar's profile image ansgar posted 12 years ago Permalink
Didn't have the time yet. No bug report please. Here's the place with all details. Trying to find some time soon.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
Okay, no problem!
Code modification/commit from ansgar.becker, 12 years ago, revision 7.0.0.4115
Expect CHARSET + COLLATE clause after RETURN, in regular expression logic of TDBConnection.ParseRoutineStructure(). See http://www.heidisql.com/forum.php?t=10297
ansgar's profile image ansgar posted 12 years ago Permalink
Fixed in r4115
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
Thanks, works very well now!

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