== 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