Cursor Problem

[cnplnsk]

I have mysql server version: 5.0.15
I am facing this problem last a week about cursor.
well ! i have created cursor program as follows...
I written this program as external file, named as c:\sqldata\cur_test.sql
and compiled it using command
mysql> \. c:\sqldata\cur_test.sql

#PROGRAM HERE
drop procedure if exists cur_test;
delimiter $$
create procedure cur_test()#out p1 text
begin
declare tid int(11) default 0;
declare tname varchar(20) default '';
declare op text default '';
declare done int default 0;
declare done1 int default 0;
declare cur1 cursor for select id,tab_name from tab;
declare continue handler for sqlexception set done=1;
open cur1;
loop1:loop
fetch cur1 into tid,tname;
if done=1 then
leave loop1;
end if;
set op=concat(op,'\n',tid,' ',tname);
end loop loop1;
close cur1;
select op as output;
end;
$$
delimiter ;

when i go to run this program, i always get an error message....

mysql> \. c:\sqldata\cur_test.sql
Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call cur_test();
ERROR 1329 (02000): No data to FETCH
mysql>

while 'tab' table is not empty

mysql> select * from tab;
+------+----------+
| id | tab_name |
+------+----------+
| 1 | xyz |
| 2 | pqr |
| 3 | lmn |
| 4 | abc |
| 5 | asd |
| 6 | zxc |
| 7 | qwe |
| 8 | rty |
| 9 | uio |
| 10 | cvb |
| 11 | poi |
+------+----------+
11 rows in set (0.11 sec)

ok...!!!
plz help me:
plz solve this problem immediatly plz plz plz
point out any error if there in my program...
suggest any idea or give me any clue...

plz plz plz

[ScarletPimpernal]

DROP PROCEDURE IF EXISTS cur_test;
CREATE PROCEDURE cur_test()
BEGIN
DECLARE tid int(11) DEFAULT 0;
DECLARE tname varchar(20)DEFAULT '';
DECLARE op text DEFAULT '';
DECLARE done int DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id,tab_name FROM tab;
DECLARE CONTINUE handler for NOT FOUND set done=1;
open cur1;
loop1:
LOOP
FETCH cur1 INTO tid,tname;
IF done=1 THEN
close cur1;
LEAVE loop1;
END IF;
SET op=CONCAT(op,'\n',tid,' ',tname);
END LOOP loop1;
SELECT op AS output;
END;

CALL cur_test();

[ronverdonk]

Both posters: read the Posting Guidelines about enclosing code within php or code tags!!

It would be a pity to remove posts because some members constantly refuse to adhere to the posting guidelines.

moderator

[cnplnsk]

DROP PROCEDURE IF EXISTS cur_test;
CREATE PROCEDURE cur_test()
BEGIN
DECLARE tid int(11) DEFAULT 0;
DECLARE tname varchar(20)DEFAULT '';
DECLARE op text DEFAULT '';
DECLARE done int DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id,tab_name FROM tab;
DECLARE CONTINUE handler for NOT FOUND set done=1;
open cur1;
loop1:
LOOP
FETCH cur1 INTO tid,tname;
IF done=1 THEN
close cur1;
LEAVE loop1;
END IF;
SET op=CONCAT(op,'\n',tid,' ',tname);
END LOOP loop1;
SELECT op AS output;
END;
 
CALL cur_test();
 
 
What is the different between your code and my code?