I tried to add a simple function :


FUNCTION "strtofloat" (IN "str" VARCHAR(500) COLLATE DEU_CI_AI_KI_WI)
RETURNS FLOAT
BEGIN
  return cast(str as float);
EXCEPTION
  return 0;
END


Cannot be saved, it seems EDB doesn't like two "returns". So I tried it this way :


FUNCTION "strtofloat" (IN "str" VARCHAR(500) COLLATE DEU_CI_AI_KI_WI)
RETURNS FLOAT
BEGIN
  declare result float default 0;
  set result = cast(str as float);
  return result;
EXCEPTION
  set result = 0;
END

This one can be saved, but I get an Acess Violation if I use it.

I think it's clear what I want to achieve, can someone tell me how to do it ?

TIA,

Heiko

Heiko,

Here is what I think you are trying to do:

FUNCTION "StrToFloat" (IN "Str" VARCHAR COLLATE DEU_CI_AI)
RETURNS FLOAT
BEGIN
 DECLARE v FLOAT;
 BEGIN
   SET v = CAST(Str AS FLOAT);
 EXCEPTION
   SET v = 0;
 END;
 RETURN V;
END

--
Fernando Dias
[Team Elevate]

Fernando,

Thanks ! But I still get an access violation, as soon as Str has non-numeric content.

Heiko

Heiko,

I couldn't reproduce the AV here with version 2.00.
Are you getting the AV in EDB Manager or your own application?
Also, what version are you using?

--
Fernando Dias
[Team Elevate]

Fernando

EDB Version 2.00 Build 1, EDB Manager

Access violation at address 004E1046 in module 'edbmgr.exe'. Read of address 00000000

Strange :
select strtofloat('blabla') as test from table
is working, but
select strtofloat(column) as test from table
is not.

Heiko

Heiko,

> EDB Version 2.00 Build 1, EDB Manager
> Access violation at address 004E1046 in module 'edbmgr.exe'. Read of address 00000000

It seems there is a problem related to empty strings that is causing
this error. Let's wait for Tim to confirm this but I think it's a bug.

--
Fernando Dias
[Team Elevate]

....

Meanwhile, as a workaround, you can use this alternative function:

ALTER FUNCTION "StrToFloat" (IN "Str" VARCHAR COLLATE ANSI)
RETURNS FLOAT
BEGIN
  DECLARE v FLOAT;
  DECLARE s VARCHAR;
  IF Str = '' THEN SET s = ' '; ELSE SET s = Str; END IF;
  BEGIN
    SET v = CAST(s AS FLOAT);
  EXCEPTION
    SET v = 0;
  END;
  RETURN V;
END



--
Fernando Dias
[Team Elevate]

Fernando,

<< It seems there is a problem related to empty strings that is causing this
error. Let's wait for Tim to confirm this but I think it's a bug. >>

Yeah, it's the internal StrtoFloat code that isn't handling empty strings
properly.

--
Tim Young
Elevate Software
www.elevatesoft.com