Hi,

I'm using MySQL 5.1.42.  I'm trying to create a function, but running
into some syntax errors taht I can't figure out.  The function is


	CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
	DETERMINISTIC
	BEGIN
		DECLARE finish_name VARCHAR(255);
		DECLARE result VARCHAR(255);
		DECLARE cur CURSOR FOR SELECT NAME FROM PROD_PRODUCT_FINISHES P,
FINISH F WHERE P.FINISH_ID = F.ID AND P.PRODUCT_ID = p_id;
		OPEN cur;
		read_loop: LOOP
			FETCH cur INTO finish_name;
			IF LENGTH(result) != 0 THEN
				SET result = CONCAT(result, ',');
			END IF
			SET result = CONCAT(result, finish_name);
		END LOOP;
		CLOSE cur;
		RETURN result;
	END;

and the error I get is

MySQL said:

#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 4

What thing(s) are wrong in the above function?  Thanks, - Dave

On 2/20/2011 3:05 PM, Dave wrote:
> Hi,
>
> I'm using MySQL 5.1.42.  I'm trying to create a function, but running
> into some syntax errors taht I can't figure out.  The function is
>
>
> 	CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
> 	DETERMINISTIC
> 	BEGIN
> 		DECLARE finish_name VARCHAR(255);
> 		DECLARE result VARCHAR(255);
> 		DECLARE cur CURSOR FOR SELECT NAME FROM PROD_PRODUCT_FINISHES P,
> FINISH F WHERE P.FINISH_ID = F.ID AND P.PRODUCT_ID = p_id;
> 		OPEN cur;
> 		read_loop: LOOP
> 			FETCH cur INTO finish_name;
> 			IF LENGTH(result) != 0 THEN
> 				SET result = CONCAT(result, ',');
> 			END IF
> 			SET result = CONCAT(result, finish_name);
> 		END LOOP;
> 		CLOSE cur;
> 		RETURN result;
> 	END;
>
> and the error I get is
>
> MySQL said:
>
> #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 4
>
> What thing(s) are wrong in the above function?  Thanks, - Dave

How are you creating the function?

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 2011-02-20 21:05, Dave wrote:
> Hi,
> 
> I'm using MySQL 5.1.42.  I'm trying to create a function, but running
> into some syntax errors taht I can't figure out.  The function is
> 
> 
> 	CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
> 	DETERMINISTIC
> 	BEGIN
> 		DECLARE finish_name VARCHAR(255);
> 		DECLARE result VARCHAR(255);
> 		DECLARE cur CURSOR FOR SELECT NAME FROM PROD_PRODUCT_FINISHES P,
> FINISH F WHERE P.FINISH_ID = F.ID AND P.PRODUCT_ID = p_id;
> 		OPEN cur;
> 		read_loop: LOOP
> 			FETCH cur INTO finish_name;
> 			IF LENGTH(result) != 0 THEN
> 				SET result = CONCAT(result, ',');
> 			END IF
> 			SET result = CONCAT(result, finish_name);
> 		END LOOP;
> 		CLOSE cur;
> 		RETURN result;
> 	END;
> 
> and the error I get is
> 
> MySQL said:
> 
> #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 4
> 
> What thing(s) are wrong in the above function?  Thanks, - Dave

Try using another delimiter than ";".

delimiter @
CREATE FUNCTION GET_FINISHES (p_id INT) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
	DECLARE finish_name VARCHAR(255);
	[...]
end @
delimiter ;

You might want to have a look the GROUP_CONCAT function:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

/Lennart