CODEWITHSUNDEEP
mysqlsqlstored-procedurestemp-tables
Jiho Kang
Jiho Kang

Reputation: 2492

temporary table not returning result set in MySQL stored proc

I have a stored proc which inserts rows from a view with ranks into a temporary table.

The temp table is created before I run a cursor loop that inserted values, and SELECT'ed after the loop is done.

However when I CALL medianMessagesPerWeek(); I get an "Error Code : 1329 No data - zero rows fetched, selected, or processed."

If I create the table as a MYISAM table I can manually select the table and confirm that data has been inserted but the stored proc will still give me nothing.

Am I missing something here?

DELIMITER $$

USE `yongopal_metrics`$$

DROP PROCEDURE IF EXISTS `medianMessagesPerWeek`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `medianMessagesPerWeek`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tempJoinWeek, tempActiveWeek, rank INT DEFAULT 0;
    DECLARE joinWeek, activeWeek, memberNo, messages INT;
    DECLARE cur CURSOR FOR SELECT * FROM cohortMessagesPerMemberPerWeek;

    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    CREATE TEMPORARY TABLE medianMessagesPerWeek
    (
        joinWeek INT,
        activeWeek INT,
        memberNo INT,
        messages INT,
        rank INT
    ) ENGINE=MEMORY;    

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO joinWeek, activeWeek, memberNo, messages;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF tempJoinWeek = joinWeek AND tempActiveWeek = activeWeek THEN
            SET rank = rank + 1;
        ELSE
            SET tempJoinWeek = joinWeek;
            SET tempActiveWeek = activeWeek;
            SET rank = 1;
        END IF;
        INSERT INTO medianMessagesPerWeek VALUES (joinWeek, activeWeek, memberNo, messages, rank);
    END LOOP;

    CLOSE cur;

    SELECT * FROM medianMessagesPerWeek;
    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    END$$

DELIMITER ;

EDIT

here is what cohortMessagesPerMemberPerWeek looks like

DELIMITER $$

USE `yongopal_metrics`$$

DROP VIEW IF EXISTS `cohortMessagesPerMemberPerWeek`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cohortMessagesPerMemberPerWeek` AS 
SELECT
  WEEK(`m`.`regDatetime`,0) AS `joinWeek`,
  WEEK(`cd`.`sendDate`,0) AS `activeWeek`,
  `m`.`memberNo` AS `memberNo`,
  COUNT(0)       AS `messages`
FROM (`yongopal`.`chatData` `cd`
   JOIN `yongopal`.`members` `m`
     ON ((`cd`.`sender` = `m`.`memberNo`)))
GROUP BY WEEK(`m`.`regDatetime`,0),WEEK(`cd`.`sendDate`,0),`m`.`memberNo`
ORDER BY WEEK(`m`.`regDatetime`,0),WEEK(`cd`.`sendDate`,0)$$

DELIMITER ;

Upvotes: 1

Views: 2630

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Looks like you're missing a not found handler for your cur cursor. This is necessary to set your done boolean to true when the fetch statement no longer returns any rows (and hence you have reached the end of the dataset returned by the cursor declaration).

Give this a try:

DELIMITER $$

USE `yongopal_metrics`$$

DROP PROCEDURE IF EXISTS `medianMessagesPerWeek`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `medianMessagesPerWeek`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tempJoinWeek, tempActiveWeek, rank INT DEFAULT 0;
    DECLARE joinWeek, activeWeek, memberNo, messages INT;
    DECLARE cur CURSOR FOR SELECT * FROM cohortMessagesPerMemberPerWeek;

    declare continue handler for not found set done := true; 

    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    CREATE TEMPORARY TABLE medianMessagesPerWeek
    (
        joinWeek INT,
        activeWeek INT,
        memberNo INT,
        messages INT,
        rank INT
    ) ENGINE=MEMORY;    

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO joinWeek, activeWeek, memberNo, messages;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF tempJoinWeek = joinWeek AND tempActiveWeek = activeWeek THEN
            SET rank = rank + 1;
        ELSE
            SET tempJoinWeek = joinWeek;
            SET tempActiveWeek = activeWeek;
            SET rank = 1;
        END IF;
        INSERT INTO medianMessagesPerWeek VALUES (joinWeek, activeWeek, memberNo, messages, rank);
    END LOOP;

    CLOSE cur;

    SELECT * FROM medianMessagesPerWeek;
    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    END$$

DELIMITER ;

Upvotes: 2

Related Questions