MySQL cursor returns only one record

Question

I'm using Hostgator to host my PHP Website with MySQL 5.5.33 and PHPMyAdmin 3.5.5. I want to use cursor to get values from individual record in a table. The followings is the code for that stored procedure:

CREATE DEFINER=`lnutri`@`localhost` PROCEDURE `TestCursor`()
   NO SQL
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE cGroupID INT;
DECLARE cGroupName VARCHAR(50);
DECLARE cursor1 CURSOR FOR SELECT GroupID, GroupName FROM Groups;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor1;

read_loop: LOOP
FETCH cursor1 INTO cGroupID,cGroupName;
IF done THEN
    LEAVE read_loop;
END IF;
SELECT cGroupID AS GroupID, cGroupName as GroupName;

END LOOP;

CLOSE cursor1;
END

But the stored procedure seems to return the first record only:

GroupID GroupName
1           Default Group

This is the structure of the Groups table

CREATE TABLE IF NOT EXISTS `Groups` (
  `GroupID` int(11) NOT NULL AUTO_INCREMENT,
  `GroupName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `Description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`GroupID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=39 ;

And this is the Groups Table's data:

GroupID GroupName   Description DateCreated
1           Default Group               0000-00-00 00:00:00
2           Group 2                     0000-00-00 00:00:00
29          Regis                       0000-00-00 00:00:00
31          Benetas                     0000-00-00 00:00:00
32          Domain                      0000-00-00 00:00:00
36          none                        0000-00-00 00:00:00
37          Aevum                       2013-11-11 17:40:56
38          Uniting Aged Care       2013-11-15 07:26:19

Answer 1

Your problem is that the stored procedure returns as many resultset as you have rows in your table and every resultset has only one row. Your client, which happens to be phpmyAdmin, doesn't process multiple resultsets. Thats why you see only one row.

To check that your cursor works just fine all you need is to temporarily create table (let's call it log) and then change

SELECT cGroupID AS GroupID, cGroupName as GroupName;

to

INSERT INTO log(group_id, group_name)
SELECT cGroupID AS GroupID, cGroupName as GroupName;

And you'll see that you after you call your procedure you'll have all your records in log table.

Here is SQLFiddle demo

---

Now it's absolutely impractical to do what you just did. If you really need to do some processing on a row by row basis and the return some results then

1. create a temporary table
2. inserted needed results in it while you iterate over the cursor
3. return the resultsest from the temp table to the client using select
4. drop the temporary table

To illustrate this let's say you want your procedure to return only every other row. Then your procedure might look like this

DELIMITER $$
CREATE PROCEDURE `TestCursor`()
BEGIN
  DECLARE done, counter INT DEFAULT FALSE;
  DECLARE cGroupID INT;
  DECLARE cGroupName VARCHAR(50);
  DECLARE cursor1 CURSOR FOR SELECT GroupID, GroupName FROM Groups;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  CREATE TEMPORARY TABLE log
  (
    id int not null auto_increment primary key, 
    group_id int,
    group_name varchar(50)
  );

  OPEN cursor1;

  read_loop: LOOP
    FETCH cursor1 INTO cGroupID,cGroupName;
    SET counter = counter + 1;
    IF done THEN
        LEAVE read_loop;
    END IF;
    IF counter % 2 <> 0 THEN
      INSERT INTO log(group_id, group_name)
      SELECT cGroupID AS GroupID, cGroupName as GroupName;
    END IF;  
  END LOOP;

  CLOSE cursor1;

  SELECT * FROM log;
  DROP TABLE log;
END

DELIMITER ;

Here is SQLFiddle