Posted: Tue Jan 13, 2009 9:23 am Post subject: Trouble creating stored function
I am using Mysql 5.
ok - ive posted this issue on a few forums, to no avail
Code:
DELIMITER $$
DROP FUNCTION IF EXISTS `adatabase`.`delNodeAndChildren` $$
CREATE FUNCTION `adatabase`.`delNodeAndChildren` (delete_eventnode_id INT) RETURNS bool
BEGIN
SELECT lft as myLeft, rgt as myRight, (rgt - lft + 1) as myWidth
FROM event_nodes
WHERE eventnode_id = delete_eventnode_id
LIMIT 1;
SELECT @numrows := FOUND_ROWS();
if @numrows = 1 then
DELETE FROM event_nodes WHERE lft BETWEEN myLeft AND myRight;
UPDATE event_nodes SET rgt = rgt - myWidth WHERE rgt > myRight;
UPDATE event_nodes SET lft = lft - myWidth WHERE lft > myRight;
return true;
end if;
return false;
END $$
DELIMITER ;
I simply can't understand why I am getting this error.
Quote:
Script line: 4 Not allowed to return a result set from a function
I've tried taking out the If statement to see if I still get the error (and have it alwyas return true), I still got the same error. So I tried removing everything (except for return true) and I could create the stored function.
Can anyone spot any syntax issues or anything that is keeping this from working?
Joined: 19 May 2004 Posts: 9 Location: Centerville, South Dakota
Posted: Tue Jan 13, 2009 2:16 pm Post subject:
You can not a result set from a function. Your statement, SELECT lft as myLeft, rgt as myRight, (rgt - lft + 1) as myWidth FROM event_nodes WHERE eventnode_id = delete_eventnode_id LIMIT 1; returns a result set.
Perhaps what you should really be trying to do is get a count of rows instead, since that does not return a result set?? Something like lft as myLeft, rgt as myRight, (rgt - lft + 1) as myWidth, count(*)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum