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

Thank you for the fast answer.

I tried your method, but the result is the same.
What is very interesting, the same sql with just the same parameters works
perfectly OUTSIDE of the function (I mean in an Sql window).
So the same code works in sql window but not in the function.

Another thing: if I change the result type from float(5,2) to Integer it
will work.
But I need float type.

Thank you

Zsolt


On 5/8/07, Paul McCullagh <paul.mccullagh@stripped> wrote:
>
> 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
>
>


-- 
Csillag Zsolt

www.egyediszoftverek.hu
www.starsoft.hu

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