mysql store procedure recusion not passing variable.

Question

Hi I'm having problems getting this to work in mysql.

DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_v5tag_count` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_v5tag_count`(IN call_tag_id INT,IN rel_tag_id VARCHAR(100),IN inp_object_id VARCHAR(100),IN level_num VARCHAR(100))
BEGIN

DECLARE done2 INT;
DECLARE i_tag_id2 INT;
DECLARE i_tag_id3 INT;
DECLARE ins_query2 VARCHAR(500);
DECLARE level_num2 INT;
DECLARE not_tag_id VARCHAR(500);

DECLARE tag_cur2 CURSOR FOR
SELECT tag_id FROM tagsV5_objects WHERE `object_type` = 6 AND `object_id` = inp_object_id AND `tag_id` != 0 AND `tag_id` != call_tag_id AND `tag_id` NOT IN(rel_tag_id);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=1;

OPEN tag_cur2;
  tag_loop2:LOOP
  FETCH tag_cur2 INTO i_tag_id2;

  IF done2=1 THEN
     LEAVE tag_loop2;
  END IF;

  SET ins_query2 = concat('insert into tagsV5_count SET tag_id = ',i_tag_id2,',level ="',level_num,'",object_type = 6,relationship_tag_id = "',call_tag_id,'",count = 1 ON DUPLICATE KEY UPDATE count = count + 1;');
  #    select ins_query;
  set @ins_query2=ins_query2;
  PREPARE ins_query2 from @ins_query2;
  EXECUTE ins_query2;


  SET level_num2 = level_num + 1;

  SET not_tag_id = CONCAT(rel_tag_id,',');

  SET not_tag_id = CONCAT(not_tag_id,i_tag_id2);

  CALL insert_v5tag_count(i_tag_id2,not_tag_id,inp_object_id,level_num2);



END LOOP;
CLOSE tag_cur2;



END $$

DELIMITER ;

SET @@session.max_sp_recursion_depth = 100;
CALL insert_v5tag_count(0,1,1509410,1);

The basic reason being is that not_tag_id doesn't want to concat and pass to the next function, any ideas would be great. Cos I'm just basing my face into the keyboard at the moment.

Thanks for your help. Richard

Answer 1

DELIMITER $$
DROP PROCEDURE IF EXISTS `fix_tag_relationship` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fix_tag_relationship`()
BEGIN

DECLARE i_tag_id INT;
DECLARE i_object_id INT;


DECLARE i_tag_id2 INT;
DECLARE i_object_id2 INT;

DECLARE ins_query2 VARCHAR(500);
DECLARE done INT;


DECLARE tag_cur CURSOR FOR
SELECT tag_id,object_id FROM tagsV5_objects WHERE object_type = 6 AND tag_id != 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

SET ins_query2 = concat('TRUNCATE debug;');
    #    select ins_query;
    set @ins_query2=ins_query2;
    PREPARE ins_query2 from @ins_query2;
    EXECUTE ins_query2;


OPEN tag_cur;
tag_loop:LOOP
  FETCH tag_cur INTO i_tag_id,i_object_id;

  IF done=1 THEN
    LEAVE tag_loop;
  END IF;

  # ok need to get all the photos with this tag and get the tags related to this one and store them at the appripriate level
  CALL insert_v5tag_count(i_tag_id,i_tag_id,i_object_id,1);


  /*SET ins_query=concat('insert into tagsV5_count SET tag_id = ',i_tag_id,',level =1,object_type = 6,relationship_tag_id = 0,count = 1 ON DUPLICATE KEY UPDATE count = count + 1;');
          #    select ins_query;
        set @ins_query=ins_query;
        PREPARE ins_query from @ins_query;
        EXECUTE ins_query;
        DROP PREPARE ins_query;*/


END LOOP;

CLOSE tag_cur;

END $$

DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_v5tag_count` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_v5tag_count`(IN call_tag_id INT,IN rel_tag_id VARCHAR(100),IN inp_object_id VARCHAR(100),IN level_num INT)
BEGIN

DECLARE done2 INT;
DECLARE i_tag_id2 INT;
DECLARE i_tag_id3 INT;
DECLARE ins_query2 VARCHAR(500);
DECLARE level_num2 INT;
DECLARE not_tag_id VARCHAR(500);

DECLARE row_num INT;
DECLARE number_of_rows INT;
DECLARE number_of_rows_child INT;
DECLARE i_number_children INT;

SET done2 = 0;
SET row_num = 0;
SET i_number_children = 0;

SET number_of_rows = (SELECT COUNT(1) FROM tagsV5_objects WHERE `object_type` = 6 AND `object_id` = inp_object_id AND `tag_id` != 0 AND `tag_id` != call_tag_id AND `tag_id` NOT IN(rel_tag_id));


IF number_of_rows > 0 THEN
  BEGIN
    DECLARE tag_cur2 CURSOR FOR
      SELECT tobj.tag_id,
        (SELECT COUNT(1) FROM tagsV5_objects WHERE `object_type` = 6 AND `object_id` = tobj.object_id
        AND tag_id != 0 AND tobj.tag_id != call_tag_id AND tag_id != tobj.tag_id )
       FROM tagsV5_objects tobj
        WHERE tobj.object_type = 6 AND tobj.object_id = inp_object_id AND tobj.tag_id != 0 AND tobj.tag_id != call_tag_id;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;

       # SET ins_query2 = concat('insert into debug SET text_val = "',done2,'"');
        #    select ins_query;
       # set @ins_query2=ins_query2;
        #PREPARE ins_query2 from @ins_query2;
        #EXECUTE ins_query2;


  OPEN tag_cur2;
    WHILE NOT done2 DO
      FETCH tag_cur2 INTO i_tag_id2,i_number_children;

      /*SET ins_query2 = concat('insert into debug SET text_val = "SELECT tobj.tag_id,
         (SELECT COUNT(1) FROM tagsV5_objects WHERE object_type = 6 AND object_id = tobj.object_id
        AND tag_id != 0 AND tag_id != tobj.tag_id AND tag_id NOT IN(CONCAT(',rel_tag_id,',tobj.tag_id)) )
       FROM tagsV5_objects tobj
        WHERE tobj.object_type = 6 AND tobj.object_id = ',inp_object_id,' AND tobj.tag_id != 0 AND tobj.tag_id != ',call_tag_id,' AND tobj.tag_id NOT IN(',rel_tag_id,');"');
        #    select ins_query;
        set @ins_query2=ins_query2;
        PREPARE ins_query2 from @ins_query2;
        EXECUTE ins_query2;*/

      IF NOT done2 THEN
        SET row_num = row_num +1;

        SET ins_query2 = concat('insert into debug SET text_val = "',i_number_children,' - ',inp_object_id,' - ',(level_num+1),' - ',i_tag_id2,' - ',rel_tag_id,'"');
            set @ins_query2=ins_query2;
            PREPARE ins_query2 from @ins_query2;
            EXECUTE ins_query2;



        SET ins_query2 = concat('insert into tagsV5_count SET tag_id = ',i_tag_id2,',level ="',level_num,'",object_type = 6,relationship_tag_id = "',call_tag_id,'",count = 1 ON DUPLICATE KEY UPDATE count = count + 1;');
        set @ins_query2=ins_query2;
        PREPARE ins_query2 from @ins_query2;
        EXECUTE ins_query2;

#it's here that I'm having trouble, it doesn't seem to want to go through.
        if STRCMP(REPLACE(rel_tag_id,i_tag_id2,''),rel_tag_id) = 1 THEN

          #do we need to do a check here for if has children?

          if i_number_children > 0 THEN

            SET ins_query2 = concat('insert into debug SET text_val = "second call"');
            set @ins_query2=ins_query2;
            PREPARE ins_query2 from @ins_query2;
            EXECUTE ins_query2;

            CALL insert_v5tag_count(i_tag_id2,CONCAT(rel_tag_id,',',i_tag_id2),inp_object_id,(level_num+1));
          END IF;
        END IF;
      END IF;
    END WHILE;
    CLOSE tag_cur2;
    END;
END IF;

END $$
DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS get_tag_count $$

CREATE FUNCTION get_tag_count(inp_object_id varchar(255),call_tag_id varchar(255),rel_tag_id2 varchar(255)) RETURNS int
BEGIN
  DECLARE ins_query2 VARCHAR(500);
  DECLARE v_return_val INT;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_return_val = -1;


  SELECT COUNT(1) INTO v_return_val FROM tagsV5_objects WHERE `object_type` = 6 AND `object_id` = inp_object_id AND `tag_id` != 0 AND `tag_id` != call_tag_id AND `tag_id` NOT IN(rel_tag_id2);

  if v_return_val = '0' THEN
    SET v_return_val = 0;
  END IF;


  RETURN v_return_val;
END $$

DELIMITER ;


SET @@SESSION.max_sp_recursion_depth=30;
CALL fix_tag_relationship();