dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1705
share rss forum feed


Nordy

join:2007-10-20
kudos:1

Error 1329: No data - zero rows fetched, selected

In the following, I get “Error 1329: No data - zero rows fetched, selected, or processed”, even when all is done correctly. My other functions work, and this same one used to work well a few days ago.

BEGIN
    DECLARE Id INT(10) DEFAULT '0';
    DECLARE Elm INT(10) DEFAULT '0';
    DECLARE ElmParent INT(10) DEFAULT '0';
    DECLARE Type TINYINT(1) DEFAULT '0';
    DECLARE Processed TINYINT(1) DEFAULT '0';
    DECLARE Country VARCHAR(2) DEFAULT "";
    DECLARE updateDone INT DEFAULT 0;
    DECLARE Increment TINYINT(1) DEFAULT '0';
 
    -- declare cursor
    DEClARE updater CURSOR FOR
        SELECT id, klm, parent, type, processed, countryCode FROM votes where voteProcessed=0;
 
    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET updateDone = 1;
 
    OPEN updater;
 
    doUpdate: LOOP
 
        FETCH updater INTO Id, Elm, ElmParent, Type, Processed, Country;
 
        IF updateDone =1 THEN
            LEAVE doUpdate;
        END IF;
 
        IF Type = 0 THEN
            SET Increment = 1;
        ELSEIF Type = 1 THEN
            SET Increment = -1;
        END IF;
 
         -- update likes
        update likes set votes=votes+Increment where id=Elm and parent = ElmParent and country=Country;
        update votes set voteProcessed = 1 where id=Id;
 
    END LOOP doUpdate;
 
    CLOSE updater;
 
END
 

Am I missing something here? I'm using MySQL version 5.5.25


Nordy

join:2007-10-20
kudos:1

After doing a little debugging, I've noticed that, in line 22 in the code on this page, If I remove Country, all works well, but If you add anything more to that FETCH line, it give this error.

Also, the other thing is, all works fine. Its only the error that's thrown. The updates all are all perfect.


dave
Premium,MVM
join:2000-05-04
not in ohio
kudos:8
reply to Nordy

Is 'countryCode' actually VARCHAR(2) or CHAR(2) in the db?



Nordy

join:2007-10-20
kudos:1

It is VARCHAR(2). Like 'US', "UK', 'SE'



justin
..needs sleep
Australian
join:1999-05-28
kudos:15
reply to Nordy

Add a dummy statement involving a table to the end of the loop. eg a pointless one row select into a variable. It is a mysql bug that the most recent version fixes, allegedly. Try it anyway.



Nordy

join:2007-10-20
kudos:1

Everything works fantastic. I even added an insert that inserted the data from the FETCH into a new table, and it inserts every row flawlessly. I just don't understand why it keep throwing that error.



justin
..needs sleep
Australian
join:1999-05-28
kudos:15

there is a mysql bug - you need to add a dummy select with a real table, as the last operation, to clear the error.



Nordy

join:2007-10-20
kudos:1

Actually, I went looking for the correct way to do this, and found this »www.markomedia.com.au/mysql-curs···cedures/

He has the FETCH after the IF done End If and I did just that way and it worked well. There's a comment on that page saying what he's posted is wrong. But it works. I wonder who's right...


dave
Premium,MVM
join:2000-05-04
not in ohio
kudos:8
Reviews:
·Verizon FiOS
reply to Nordy

'US' is not inerently VARCHAR; it is a literal string, and it can be stored in a VARCHAR(2) or CHAR(2) column.

Mind you, I don't know what happens if you FETCH a CHAR field into a VARCHAR variable.

For a fixed-length 2 character string, I'd choose CHAR(2).