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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
