Geek Answers Handbook

Error declaring an integer variable inside a MySQL stored function

I get an error when trying to declare a new stored function in MySQL (server version: 5.5.13)

Basically, I have a large table that classifies the rows according to how they start. My function takes a string (from user input) and then tells you the classification of that string by searching the database for classification. This is a bit like a LIKE query, except for the opposite, since user input contains a complete string and the database contains the string to search. Hope this makes sense!

The concept and logic underlying it work fine, since I wrote / developed it in PHP and it works great, however, trying to translate this into a stored function, I get an error from MySQL. Function Code:

delimiter $ DROP FUNCTION IF EXISTS get_string_class$ CREATE FUNCTION get_string_class(mystring VARCHAR(15)) RETURNS VARCHAR(15) READS SQL DATA BEGIN DECLARE i INT; SET i = 2; DECLARE mystringlength INT; SET mystringlength = LENGTH(mystring); DECLARE segment VARCHAR(15); DECLARE String_Class VARCHAR(15); SET String_Class = NULL; WHILE i <= mystringlength DO SET segment = LEFT(mystring, i); SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment); IF SELECT FOUND_ROWS() = 1 THEN RETURN String_Class END IF; i = i + 1; END WHILE; RETURN String_Class; END$ delimiter ;

I get 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 'DECLARE mystringlength INT; SET mystringlength = LENGTH(mystring); DECLARE segm' at line 10

I played many times, trying to find a job where I am wrong. I even completely disabled the loop to check it, but still getting the same error. Does anyone know what I did wrong by declaring this INT variable? This is probably something incredibly basic ...!

Thank you very well in advance.

+4
source share
2 answers

There were some syntax errors -

One of them - all announcements should be at the beginning of the BEGIN ... END sentence.

 DELIMITER $ DROP FUNCTION IF EXISTS get_string_class$ CREATE FUNCTION get_string_class(mystring VARCHAR(15)) RETURNS VARCHAR(15) READS SQL DATA BEGIN DECLARE i INT; DECLARE segment VARCHAR(15); DECLARE String_Class VARCHAR(15); DECLARE mystringlength INT; SET i = 2; SET mystringlength = LENGTH(mystring); SET String_Class = NULL; WHILE i <= mystringlength DO SET segment = LEFT(mystring, i); SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment); IF (SELECT FOUND_ROWS()) = 1 THEN RETURN String_Class; END IF; SET i = i + 1; END WHILE; RETURN String_Class; END$ DELIMITER ;
+6
source

DECLARE part inside the stored procedure must be on top and SET , after which other statemnts are launched.

+4
source

More articles:


All Articles