• RSS
  • Twitter
  • FaceBook

Security Forums

Log in

FAQ | Search | Usergroups | Profile | Register | RSS | Posting Guidelines | Recent Posts

Trouble creating stored function

Users browsing this topic:0 Security Fans, 0 Stealth Security Fans
Registered Security Fans: None
Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases

View previous topic :: View next topic  
Author Message
ctytrungloi
Just Arrived
Just Arrived


Joined: 13 Jan 2009
Posts: 0


Offline

PostPosted: Tue Jan 13, 2009 9:23 am    Post subject: Trouble creating stored function Reply with quote

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
Back to top
View user's profile Send private message
Groovicus
Trusted SF Member
Trusted SF Member


Joined: 19 May 2004
Posts: 9
Location: Centerville, South Dakota

Offline

PostPosted: Tue Jan 13, 2009 2:16 pm    Post subject: Reply with quote

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(*)
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   

Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases All times are GMT + 2 Hours
Page 1 of 1


 
Jump to:  
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

Community Area

Log in | Register