List:General Discussion« Previous MessageNext Message »
From:Rob Hall Date:October 5 2005 9:05am
Subject:Cursors in 5.0.13
View as plain text  
Having a few problems using cursors in 5.0.13 and I don't know wether it's an 
'operator error' :)

Should this work?

DECLARE cur1 CURSOR FOR SELECT DISTINCT 
name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone FROM 
extended WHERE centre_name != "";

The DISTINCT in the SELECT statement seems to screw things up merrily?

(Full code at bottom of this mail). If I remove the distinct from the cursor I 
get a count returned of 50511. A simple 

SELECT COUNT(DISTINCT 
name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone) FROM 
extended WHERE centre_name != "";

Returns 26813.

The stored procedure with the distinct in place only ever returns 1? 

DELIMITER $$

DROP PROCEDURE IF EXISTS `directory`.`Test1`$$
CREATE PROCEDURE `directory`.`Test1` (OUT counted INT)
BEGIN
DECLARE countit,done INT DEFAULT 0;
DECLARE name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone 
CHAR(255);
DECLARE cur1 CURSOR FOR SELECT DISTINCT 
name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone FROM 
extended WHERE centre_name != "";
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

REPEAT
    FETCH cur1 INTO 
name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone;
    IF NOT done THEN
       SET countit=countit+1;
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET counted=countit;

END$$

DELIMITER ;

(Before anybody states that the above is pointless I know! The above codes the 
result of trying to track this issue!).

-- 
Best regards,
 Rob Hall - Red Hat Certified Engineer
 Technical Team Leader
 Newsquest Digital Media
Thread
Cursors in 5.0.13Rob Hall5 Oct
  • Re: Cursors in 5.0.13Jeff Smelser5 Oct
    • Re: Cursors in 5.0.13Rob Hall6 Oct
      • Re: Cursors in 5.0.13Gleb Paharenko7 Oct