Re: Cursors in 5.0.13
Hello. Looks to me like a bug in the cursor handling but I'd like someone to confirm this. See: http://bugs.mysql.com/bug.php?id=13771 Rob Hall wrote: On Wednesday 05 Oct 2005 15:29, Jeff Smelser wrote: On Wednesday 05 October 2005 04:05 am, Rob Hall wrote: Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? when loading a procedure, do show warnings after it.. It tells you what problems its running into.. So what error are you getting? None :-). No Warnings or Errors are returned. All I get is a count of 1=20 evertime when Distinct is used in the cursor select statement. Omit the=20 DISTINCT and the count is 50511. Do a single select from the command line=20 including the distinct and I get 28K+ entries returned.=20 Looks to me like a bug in the cursor handling but I'd like someone to confi= rm=20 this. =2D-=20 Best regards, =C2=A0Rob Hall - Red Hat Certified Engineer =C2=A0Technical Team Leader =C2=A0Newsquest Digital Media -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cursors in 5.0.13
On Wednesday 05 Oct 2005 15:29, Jeff Smelser wrote: On Wednesday 05 October 2005 04:05 am, Rob Hall wrote: Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? when loading a procedure, do show warnings after it.. It tells you what problems its running into.. So what error are you getting? None :-). No Warnings or Errors are returned. All I get is a count of 1 evertime when Distinct is used in the cursor select statement. Omit the DISTINCT and the count is 50511. Do a single select from the command line including the distinct and I get 28K+ entries returned. Looks to me like a bug in the cursor handling but I'd like someone to confirm this. -- 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]
Cursors in 5.0.13
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]
Re: Cursors in 5.0.13
On Wednesday 05 October 2005 04:05 am, Rob Hall wrote: Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? when loading a procedure, do show warnings after it.. It tells you what problems its running into.. So what error are you getting? Jeff pgpZjgXtyciGn.pgp Description: PGP signature
