handle no data found situation

handle no data found situation

am 20.06.2007 05:26:32 von Jeff User

Hi all

I am kind of new and am trying to set up error handlers in MySql
5.0.41.
I create a function. Within, I try to keep selecting until I get
nothing back from select.

I cannot seem to catch the error when the select returns nothing.
I declare a handler like this:
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET tester = 01;
more stufff....
END;

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
write out to error log...
END;

Then, I have a loop like this:

myloop: LOOP

select x INTO y
FROM z
WHERE a = b;

if tester = -1 then
LEAVE myloop;
end if;

do stuff here....

set b = b + 1;

END LOOP myloop;

The select is guarenteed to retrieve nothing after a few loops around.
but it never ends. The declare continue handler does not fire.

If I run from client command prompt the EXIT handler fires, but I
dont get the warning message, just message thatit exited without a
RETURN.

If I remove all error handlers and run form prompt, it runs endlessly
until I stop it with a counter var. Then I get X number of warnings,
but it doesnt say what the warning is...

What is happening here?

Thanks
jeff