Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / May 2007

Tip: Looking for answers? Try searching our database.

Recursive UDF

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Szymon Dembek - 28 May 2007 13:14 GMT
Hello

Is it possible to write a recursive (a function which calls itself) UDF
in DB2 (v.9) ?

When I load one, I get SQL0440N - function "XXX" not found...

Thanks in advance

Signature

Szymon Dembek

Serge Rielau - 28 May 2007 13:40 GMT
> Hello
>
> Is it possible to write a recursive (a function which calls itself) UDF
> in DB2 (v.9) ?
>
> When I load one, I get SQL0440N - function "XXX" not found...
You have to go through dynamic SQL. To do that in a UDF you need a
helper procedure.
Here is a recipe (untested):

--#SET TERMINATOR @
CREATE PROCEDURE poo(IN a INT, OUT aplus1 INT)
BEGIN
  DECLARE txt VARCHAR(200);
  DECLARE stmt STATEMENT;
  DECLARE cur CURSOR FOR stmt;
  SET aplus1 = a;
  IF a < 60 THEN
    SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
    PREPARE stmt FROM txt;
    OPEN cur;
    FETCH cur INTO aplus1;
    CLOSE cur1;
  END IF;
END
@

CREATE FUNCTION FOO(a INT) RETURNS INT
BEGIN ATOMIC
  DECLARE aplus1 INT;
  CALL POO(a, aplus1);
  RETURN aplus1;
END
@
--#SET TERMINATOR ;

VALUES FOO(1);

Note that the permitted level of recursion is 64 in DB2 9 (up from 16 in
DB2 V8)

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau - 28 May 2007 13:47 GMT
>     SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';

Note: In your production solution you want to use parameter markers for
the function arguments of course. I was a bit sloppy here.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Szymon Dembek - 28 May 2007 13:51 GMT
>>     SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
>
> Note: In your production solution you want to use parameter markers for
> the function arguments of course. I was a bit sloppy here.

Thanks a lot !

Signature

Szymon Dembek

--CELKO-- - 29 May 2007 16:53 GMT
>> Is it possible to write a recursive (a function which calls itself) UDF in DB2 (v.9) ? <<

Perhaps if you post your actual problem, we can find a way to avoid
recursion.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.