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 <[EMAIL PROTECTED]> 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
