SEO

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

== Quote from Jeff User (jen1162@hotmail.com)'s article
> 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

can you post the actual stored procedure's syntax?
--
POST BY: lark with PHP News Reader

On Wed, 20 Jun 2007 13:19:04 GMT, lark <hamzee@sbcgobal.net> wrote:

>== Quote from Jeff User (jen1162@hotmail.com)'s article
>> 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
>
>can you post the actual stored procedure's syntax?

Thanks, here it is:

delimiter //

DROP FUNCTION IF EXISTS getAllRegions //

CREATE FUNCTION getAllRegions (i_regionID INT) RETURNS varchar(127)

BEGIN
declare v_procName varchar(40) DEFAULT 'getAllRegions';
declare v_parentID INT;
declare v_name varchar(50);
declare v_catName varchar(100) DEFAULT '-1';
declare flag_last_row INT DEFAULT 0;
declare cnt int default 0;
/* Declare error handlers conditions */
DECLARE CONTINUE HANDLER FOR NOT FOUND
begin
SET flag_last_row = -1;
INSERT INTO
error_log(procedureName,errorDesc, userId)
VALUES(v_procName, 'A Not Found error
occured', 1);
END;

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
INSERT INTO
error_log(procedureName,errorDesc, userId)
VALUES(i_regionID, cnt, 1);
END;

region_loop: LOOP

SELECT regionName, regionParentID
INTO v_name, v_parentID
FROM region
WHERE regionID = i_regionID;

--for testing only:
set cnt = cnt + 1;
-- Fails somewhere around here

IF (flag_last_row=-1) THEN
LEAVE region_loop;
END IF;

IF (v_catName = '-1') THEN
SET v_catName = v_name;
ELSE
set v_catName = CONCAT(v_name,'-',v_catName);
END IF;

SET i_regionID = v_parentID;
--if cnt = 6 then leave region_loop; end if;

END LOOP region_loop;

RETURN v_catName;

END;
//

delimiter ;

== Quote from Jeff User (jen1162@hotmail.com)'s article
> On Wed, 20 Jun 2007 13:19:04 GMT, lark <hamzee@sbcgobal.net> wrote:
> >== Quote from Jeff User (jen1162@hotmail.com)'s article
> >> 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
> >
> >can you post the actual stored procedure's syntax?
> Thanks, here it is:
> delimiter //
> DROP FUNCTION IF EXISTS getAllRegions //
> CREATE FUNCTION getAllRegions (i_regionID INT) RETURNS varchar(127)
> BEGIN
> declare v_procName varchar(40) DEFAULT 'getAllRegions';
> declare v_parentID INT;
> declare v_name varchar(50);
> declare v_catName varchar(100) DEFAULT '-1';
> declare flag_last_row INT DEFAULT 0;
> declare cnt int default 0;
> /* Declare error handlers conditions */
> DECLARE CONTINUE HANDLER FOR NOT FOUND
> begin
> SET flag_last_row = -1;
> INSERT INTO
> error_log(procedureName,errorDesc, userId)
> VALUES(v_procName, 'A Not Found error
> occured', 1);
> END;
> DECLARE EXIT HANDLER FOR SQLWARNING
> BEGIN
> INSERT INTO
> error_log(procedureName,errorDesc, userId)
> VALUES(i_regionID, cnt, 1);
> END;
> region_loop: LOOP
> SELECT regionName, regionParentID
> INTO v_name, v_parentID
> FROM region
> WHERE regionID = i_regionID;
> --for testing only:
> set cnt = cnt + 1;
> -- Fails somewhere around here
> IF (flag_last_row=-1) THEN
> LEAVE region_loop;
> END IF;
> IF (v_catName = '-1') THEN
> SET v_catName = v_name;
> ELSE
> set v_catName = CONCAT(v_name,'-',v_catName);
> END IF;
> SET i_regionID = v_parentID;
> --if cnt = 6 then leave region_loop; end if;
> END LOOP region_loop;
> RETURN v_catName;
> END;
> //
> delimiter ;

maybe this'll work: for the continue handler:
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIGN
blah blah
ENG:
--
POST BY: lark with PHP News Reader