Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

    Hi,

    I have a function that returns the error No data - zero rows fetched, selected, or processed. I've searched on Google and nobody seems to have found a solution. The code of my stored function goes like this, maybe someone will be able to help me find a workaround:

    CREATE FUNCTION get_trans_header(No_auction INT UNSIGNED)
    RETURNS INT UNSIGNED
    BEGIN
    DECLARE int_no_transfert INT UNSIGNED DEFAULT 0;

    SELECT TrFH_no_transfert INTO int_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1;

    IF int_no_transfert IS NULL THEN
    INSERT INTO Transfert_fed_header(TrFH_no_auction) VALUES(No_auction);
    SELECT LAST_INSERT_ID() INTO int_no_transfert;
    END IF;

    RETURN(int_no_transfert);
    END//


    The purpose of this function is to find a record who's start and end dates are null. If we find one, we return the id (TrFH_no_transfert) else we insert a new record and get the new id with LAST_INSERT_ID().

    I'm running this on a MySQL 5.0.24a-community-nt.

    Any help would be greatly appreciated,

    Denis Dupere.
    Last edited by wehtiko; 09-13-06 at 14:40.

  2. #2
    Join Date
    Jul 2004
    Posts
    62

    Talking

    Hi,
    I'm not an expert in MySQL 5, but i also know how the PL/SQL for oracle works to avoid zero rows fetched. It is not eficient but to minimize unknown bugs.

    In your code
    SELECT TrFH_no_transfert INTO int_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1;
    Of coz the result would be NO result or YES result, and No data - zero rows fetched will be thrown if NO result.
    To avoid that

    SELECT count(TrFH_no_transfert) INTO isexist FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL);

    IF isexist then .....
    SELECT TrFH_no_transfert INTO int_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1;
    END IF;
    Yes it helps a little on your code..

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    Hi pakcik,

    Sounds logical, I'll try that.

    Thank you for your help!!

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    Hi again,

    I've tried your solution and it worked. Thanks a million!!!

  5. #5
    Join Date
    Apr 2008
    Posts
    1
    Hi folks,

    Today I ran into a similar situation on a Mysql 5.0.27 box
    Here is how I made it work:

    .................
    DECLARE int_no_transfert INT UNSIGNED DEFAULT 0;

    SET int_no_transfert = (SELECT TrFH_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1);

    IF int_no_transfert !=0 THEN
    -- it means select returned something and our local variable has changed
    -- so here I treat the duplicate case....
    ELSE
    -- select returned no resultset... thus leaving untouched the local variable : int_no_transfert (0)
    -- so, no duplicate.. proceed with insert.. or stuff
    END

Posting Permissions

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