469,647 Members | 1,789 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Cursor Problem

19
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
Mar 28 '07 #1
3 2218
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();
Mar 30 '07 #2
ronverdonk
4,258 Expert 4TB
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
Mar 31 '07 #3
cnplnsk
19
Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS cur_test;
  2. CREATE PROCEDURE cur_test()
  3. BEGIN
  4. DECLARE tid int(11) DEFAULT 0;
  5. DECLARE tname varchar(20)DEFAULT '';
  6. DECLARE op text DEFAULT '';
  7. DECLARE done int DEFAULT 0;
  8. DECLARE cur1 CURSOR FOR SELECT id,tab_name FROM tab;
  9. DECLARE CONTINUE handler for NOT FOUND set done=1;
  10. open cur1;
  11. loop1:
  12. LOOP
  13. FETCH cur1 INTO tid,tname;
  14. IF done=1 THEN
  15. close cur1;
  16. LEAVE loop1;
  17. END IF;
  18. SET op=CONCAT(op,'\n',tid,' ',tname);
  19. END LOOP loop1;
  20. SELECT op AS output;
  21. END;
  22.  
  23. CALL cur_test();
  24.  
  25.  
  26. What is the different between your code and my code?
  27.  
Apr 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by DarthMacgyver | last post: by
4 posts views Thread by Oliver Stratmann | last post: by
reply views Thread by Prashanth | last post: by
1 post views Thread by ilPostino | last post: by
5 posts views Thread by Vlad Simionescu | last post: by
2 posts views Thread by Craig | last post: by
6 posts views Thread by Tomek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.