List:General Discussion« Previous MessageNext Message »
From:Paul McCullagh Date:May 8 2007 12:41pm
Subject:Re: Function returns with NOTHING in a particular database
View as plain text  
Hi Zsolt,

The problem probably has something to do with an exception that is  
not handled.

Try using a cursor (for example):

BEGIN
   DECLARE done INT DEFAULT 0;
   declare Akt_keszlet float(5,2) ;
   DECLARE cur CURSOR FOR SELECT IFNULL(
   (Select tr_aktualis_keszlet
      From `termek_raktarhely`
             Where tr_termek_azon=termek_azon
                and tr_raktarhely_azon=raktar_azon), 0);
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

   OPEN cur;

   Akt_keszlet = 0;
   REPEAT
     FETCH cur INTO Akt_keszlet;
   UNTIL done
   END REPEAT;

   CLOSE cur;
   Return Akt_keszlet ;
END

- Paul


On May 8, 2007, at 2:03 PM, Zsolt Csillag wrote:

> Hello,
>
> I have the following function in Mysql:
>
> CREATE FUNCTION `GetTermekAktualisKeszlet`(termek_azon INTEGER(11),
> raktar_azon INTEGER(11), hosszban_tarolt_termek CHAR)
> RETURNS float(5,2)
> NOT DETERMINISTIC
> SQL SECURITY DEFINER
> COMMENT ''
> Begin
>
> Declare Akt_keszlet float(5,2) ;
>
> SELECT IFNULL(
>  (Select tr_aktualis_keszlet
>    From `termek_raktarhely`
>         Where tr_termek_azon=termek_azon
>              and tr_raktarhely_azon=raktar_azon)
> ,0) into Akt_keszlet;
>
>
> If Akt_keszlet Is Null THEN
> set Akt_keszlet = 0;
> END If;
> Return Akt_keszlet ;
>
> End;
>
>
> The function above returns either 0 or a value on every MySql  
> database,
> including mine.
> However at a new customer this returns NOTHING. (not even NULL).
>
> How can I correct this problem?
>
> Even if I put the line set Akt_keszlet =5 before Return doesn't  
> help getting
> a number (in this case returns with nothing, too).
>
>
> Thank you very much for your help.
>
> -- 
> Zsolt Csillag

Thread
Function returns with NOTHING in a particular databaseZsolt Csillag8 May
  • Re: Function returns with NOTHING in a particular databasePaul McCullagh8 May
    • Re: Function returns with NOTHING in a particular databaseZsolt Csillag8 May