Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Recursive Stored Procedure

    Hello,
    I am trying to compute the fibonacci sequence using recursive stored procedures.
    I am trying to call my procedure recursively, I can call it but I don't know how to get the output value.

    This is my procedure:

    Code:
    CREATE PROCEDURE fib(IN i INTEGER, OUT fib BIGINT)
    BEGIN
        DECLARE sql VARCHAR(200);
        DECLARE aux1 BIGINT;
    
        IF i=0 OR i=1
           THEN SET fib = i;
        ELSE
          --Im trying many ways, but I think one of those may do the trick, Im missing    
          --something
          
          --SET sql = 'CALL fib(1,aux1)';
          --SET sql = 'CALL fib(?,?)';
          EXECUTE IMMEDIATE sql; -- USING 1, aux1;
          SET fib = aux1;
        END IF;
    END@
    How can I get the out parameter from my recursive call?

    thank you,
    Oscar

  2. #2
    Join Date
    Jul 2009
    Posts
    150
    Somebody, Lenny, shown this query on this forum....
    You can use, if you want. It's free today....

    http://www.dbforums.com/db2/1646667-...plain-db2.html

    Kara

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you try simply
    Code:
    CALL fib(1,aux1)
    ?

    By the way, procedure calls can nest no more than 64 levels deep.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jul 2009
    Posts
    150
    Two rules you have to know:

    Self-referencing function: The body of an SQL function (that is, the expression or NULL in the RETURN clause of the CREATE FUNCTION statement) cannot contain a recursive invocation of itself or to another function that invokes it, because such a function would not exist to be referenced

    Version resolution:
    Normally, the currently active version of a native SQL
    | procedure will be used on a CALL statement. However, if the CALL
    | statement is a recursive call inside the body of the same stored
    | procedure, and the original CALL statement uses a version that is
    | different from the currently active version, the active version will not
    | be used. The version from the original CALL statement will be used for any
    | recursive CALL statements until the entire stored procedure finishes
    | executing. This preserves the semantics of the version that is used by the
    | original CALL statement. This includes the case where the recursive call
    | is indirect. For example, assume that procedure SP1 call procedure SP2,
    | which in turn recursively calls SP1. The second invocation of procedure
    | SP1 will use the version of the procedure that is active at the time of
    | the original CALL statement that invoked procedure SP1.


    Kara

  5. #5
    Join Date
    Nov 2009
    Posts
    7
    Hi!
    Thank you for your reply!
    @Kara
    Thanks for the link. I took a look at it but it seems he is calling the same select many times, isn't he? (I'm a beginner in DBs world).
    About your second post, what does "active version" means? Is it the version of "compilation"?

    @n_i
    yes, I tried that solution, but once it is recursive, I get the following error:
    "Unable to find a FIB procedure that accepts compatible parameters. "
    I think it is because it is not compiled yet, so not yet deployed, then the DB can't "find" it.
    The dynamic SQL was the way I found to call it, but I can't get the output result.
    I just need the syntax to call it...

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something along these lines:
    Code:
    DECLARE p_in, p_out BIGINT;
    
    SET sql = 'CALL fib(?,?)';
    
    PREPARE s FROM sql;
    EXECUTE s INTO p_out USING p_in;
    Not tested.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2009
    Posts
    7
    It worked!
    thank you a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •