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
DROP FUNCTION IF EXISTS `adatabase`.`delNodeAndChildren` $$
CREATE FUNCTION `adatabase`.`delNodeAndChildren` (delete_eventnode_id INT) RETURNS bool
SELECT lft as myLeft, rgt as myRight, (rgt - lft + 1) as myWidth
WHERE eventnode_id = delete_eventnode_id
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;
I simply can't understand why I am getting this error.
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
Looking for more Windows Networking info?
Sign up to the WindowsNetworking.com Monthly Newsletter, written by Enterprise Security MVP Deb Shinder, containing news, the hottest tips, Networking links of the month and much more. Subscribe today and don't miss a thing! View a sample newsletter.
Become a WindowsNetworking.com member!
Discuss your Windows Networking issues with thousands of other Windows Newtorking experts. Click here to join!