Have Geek Brain

Strange mysql stored function issue

This is just for a test,

but rather strange:

    DELIMITER |

DROP FUNCTION IF EXISTS get_idiscussion_ask|

CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE body varchar(600);
  DECLARE created DATETIME;
  DECLARE anonymous TINYINT(1);
  DECLARE screen_name varchar(64);
  DECLARE result TEXT;
  DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  SET result = '';
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO body, created, anonymous, screen_name;
    SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');
  UNTIL done END REPEAT;
  CLOSE cur1;

  RETURN result;
END |

DELIMITER ;

mysql> DELIMITER ;
mysql> select get_idiscussion_ask(1);
+------------------------+
| get_idiscussion_ask(1) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.01 sec)



mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1;
+------+---------------------+-----------+-------------+
| body | created             | anonymous | screen_name |
+------+---------------------+-----------+-------------+
| haha | 2009-05-27 04:57:51 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:52 |         0 | NULL        |
| haha | 2009-05-27 04:57:53 |         0 | NULL        |
+------+---------------------+-----------+-------------+
4 rows in set (0.00 sec)
0


a source to share


4 answers


You must do



Select Result
+1


a source


1) You are not calling your function in your example.



2) by concatenating everything with null results to null

+1


a source


You ... concat null and a string ... which is null. That the stored procedure has anything to do with it if you don't even call it?

0


a source


if you change this:

DECLARE cur1 CURSOR FOR 
  SELECT body,created,anonymous,screen_name 
  FROM idiscussion 
  LEFT JOIN users ON idiscussion.uid=users.id 
  WHERE idiscussion.iask_id=iask_id;

to

DECLARE cur1 CURSOR FOR SELECT   
ifnull(body,'')  
,ifnull(created,now())    
,ifnull(anonymous,0)  
,ifnull(screen_name,'')  
FROM idiscussion   
LEFT JOIN users on idiscussion.uid=users.id   
WHERE idiscussion.iask_id=iask_id;

You shouldn't have a problem with CONCATing nulls

Also I'm wondering if this part where idiscussion.iask_id=iask_id;


won't give a naming conflict, maybe you should change the
iask_id

piask_id parameter
(but I'm not sure about this part).

0


a source






More articles:


All Articles