Trouble creating stored function

Networking/Security Forums -> Databases

Author: ctytrungloi PostPosted: 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?

Removed unnecessary links~groove

Author: GroovicusLocation: Centerville, South Dakota PostPosted: 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(*)



Networking/Security Forums -> Databases


output generated using printer-friendly topic mod, All times are GMT + 2 Hours

Page 1 of 1

Powered by phpBB 2.0.x © 2001 phpBB Group