Anyone using functions with EDB yet, and if so can you share some code.

Roy Lambert

Roy,

<< Anyone using functions with EDB yet, and if so can you share some code.
>>

Do you want just any old example, or something specific ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Any old example should do. Basically something I can use to see what and how to write them since the manual in its 1.01b1 incarnation isn't massively outspoken on the topic


Roy Lambert

Roy,

<< Any old example should do. Basically something I can use to see what and
how to write them since the manual in its 1.01b1 incarnation isn't massively
outspoken on the topic >>

Well, there is an example in the manual for CREATE FUNCTION:

-- This function looks up the sales tax
-- rate for a given state and county

CREATE FUNCTION LookupSalesTaxRate(IN State CHAR(2), IN County VARCHAR)
RETURNS DECIMAL(20,2)
BEGIN
  DECLARE TempCursor CURSOR FOR stmt;
  DECLARE Result DECIMAL(20,2) DEFAULT 0;

  PREPARE stmt FROM 'SELECT * FROM SalesTaxes WHERE State = ? AND County =
?';

  OPEN TempCursor USING State, County;

  IF (ROWCOUNT(TempCursor) > 0) THEN
     FETCH FIRST FROM TempCursor (TaxRate) INTO Result;
  END IF;

  CLOSE TempCursor;

  RETURN Result;
END

It's basically the same as a stored procedure except that a function has a
RETURNS clause and a RETURN statement at the end of the function body.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>It's basically the same as a stored procedure except that a function has a
>RETURNS clause and a RETURN statement at the end of the function body.

No need to swear. I don't know what stored procedure's are either

Roy Lambert

ps didn't think to look under the CREATE part of the OLH

Tim


I think old pea brain needs a bit more help than that. What I'm trying to do is a simple thing I can do easily in SQL (I though that would give me a head start). Simply test for wether a transaction is in a specific month or not. The SQL is

select * from transactions where
extract(MONTH from _Date) = 4

What I wanted to do (as a training exercise) is replace it with an SQL function so I would type

select * from transactions where
IsInMonth(4,_Date)


Roy Lambert

Cracked it


CREATE FUNCTION "IsInMonth" (IN "cDate" DATE, IN "Comparator" INTEGER)
RETURNS BOOLEAN
BEGIN
RETURN ( EXTRACT(MONTH FROM cDate) = Comparator);
END


Roy Lambert

Tim


This doesn't work, and I don't understand why


CREATE FUNCTION "IsInMonth" (IN "cDate" DATE, IN "Comparator" INTEGER)
RETURNS BOOLEAN
BEGIN
IF
EXTRACT(MONTH FROM cDate) = Comparator
THEN
RETURN TRUE
ELSE
RETURN FALSE
END IF;
END


================================================================================
SQL Error (ElevateDB 1.02 Build 1)
================================================================================

ElevateDB Error #700 An error was found in the statement at line 7 and column 1
(Expected : but instead found RETURN)

Roy Lambert

Roy,

<< Cracked it >>

Yep, that looks good.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< No need to swear. I don't know what stored procedure's are either >>

Swear ?

--
Tim Young
Elevate Software
www.elevatesoft.com