Cursor not working - Page 2

n_i · #16 (**permalink**) 02-10-10, 07:21

I think this error is raised when you try to execute the update after reaching the end of result set. Add a condition handler for NOT FOUND that would exit the loop. There are examples of how it's done in the manual. Something like:

Code:

declare done int default 0;
...
declare condition handler for not found set done=1;
...
loop
  fetch ...;
  if done = 1 leave;
  ...
end loop

jeunne0000 · #17 (**permalink**) 02-10-10, 09:08

Thanks n_i!

I have one last question.

Is it possible to have like this in WHERE clause?

===============================
update table1
set body = 'blah'
where id in (select id from table2)
and current of c1;
===============================

C1 is the cursor name.
Thanks again!

[-]

n_i · #18 (**permalink**) 02-10-10, 10:04

Quote:

Originally Posted by jeunne0000

Is it possible to have like this in WHERE clause?

The answer to your question is in the manual. You can use a construct similar to

Code:

if exists (select ...)
 update .. where current ...
end if

jeunne0000 · #19 (**permalink**) 02-11-10, 03:04

Thanks again n_i!

I've madesome changes to my script. Now it is giving me this error

================================================== ========

SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.

================================================== ========

What I would like to do is like this:

================================================== ========
begin
declare c1 cursor...
for read only
open c1
loop...
fetch c1 into v_id, v_name;
call session.UPDATE_TBL(v_id, v_name); (is this possible?)
end loop...
close c1;
end@
================================================== ========

session.UPDATE_TBL procedure is just a one UPDATE statement.

Please help. Many Thanks again!

[-]

n_i · #20 (**permalink**) 02-11-10, 07:14

Quote:

Originally Posted by jeunne0000

session.UPDATE_TBL procedure is just a one UPDATE statement.

Are you sure about that? Did you sneak a commit in there too, by chance?

jeunne0000 · #21 (**permalink**) 02-12-10, 01:58

Oh! the session.UPDATE_TBL has 2 update statements with commit. So is that a problem and causes the FETCH and CLOSE error?

Many thanks again for the prompt reply. I really really appreciate it!

[-]

Internet Services

Web Development

Digital Marketing

Consumer Tech