Professional Web Applications Themes

handle no data found in function - MySQL

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. #1

    Default handle no data found in function

    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




    Jeff Guest

  2. #2

    Default Re: handle no data found in function

    == Quote from Jeff User (com)'s article 

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

  3. #3

    Default Re: handle no data found in function

    On Wed, 20 Jun 2007 13:19:04 GMT, lark <net> wrote:
     
    >
    >can you post the actual stored procedure's syntax?[/ref]

    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 ;


    Jeff Guest

  4. #4

    Default Re: handle no data found in function

    == Quote from Jeff User (com)'s article 
    > >
    > >can you post the actual stored procedure's syntax?[/ref]
    > 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 ;[/ref]

    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
    lark Guest

Similar Threads

  1. Replies: 3
    Last Post: July 20th, 06:50 AM
  2. How to handle the POST data
    By Koala in forum PHP Development
    Replies: 2
    Last Post: November 11th, 10:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139