Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Unanswered: SQL Function with select stmt that uses parameter from function

    Hello,

    I have encountered the following problem while migrating some functions from postgres to db2.

    I want to write a function like this:

    create function myfunc( tablename varchar(30))
    RETURNS varchar(4000) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
    begin atomic

    for row as
    select * from $tablename$
    do
    ...
    end for;

    return sth.

    end;

    My problem is, that I have no idea how to use the parameter "tablename" in this sql statement you find in the for clause.

    In the PostGres Function I built a string representing my select statement and executed it to iterate over the result.

    Guess it's easy for someone who knows DB2 much better.

    Any ideas?

    Have a nice weekend.

    Marcus

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think you can do it in an SQL function, but you can use dynamic SQL in a stored procedure, which in turn can be called from the function. Search documentation for "dynamic sql".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2008
    Posts
    39
    You can try it as follows.

    create function func1(tablename varchar(50))
    return........
    begin atomic
    declare cmd varchar(200);

    SET cmd = 'SELECT * FROM' ||tablename;
    PREPARE stmt from cmd;
    DECLARE c1 cursor for stmt;
    open c1;
    fetch.........

    END@

    Thanks,
    Madhavi

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by madhu_kaza
    You can try it as follows.
    Clearly, you haven't tried it yourself. Cursors (and related statements) are not supported in user-defined SQL functions.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2008
    Posts
    39
    yea, you cannot use cursors or prepare statements in a CLP code, but you can use them in SQC(Embedded C).

    you can refer to udfcli.sqc sample in sqllib/samples/c directory.

    Thanks,
    Madhavi

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by madhu_kaza
    yea, you cannot use cursors or prepare statements in a CLP code, but you can use them in SQC(Embedded C).
    Certainly. You may have noticed though that we are talking about SQL functions here, not CLP or C functions.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may be able to put this code into a stored procedure and call the procedure from a UDF.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    You may be able to put this code into a stored procedure and call the procedure from a UDF.
    You may even let that stored procedure just declare and open the cursor, and return a handle to the cursor, so that the fetches happen in the caller.
    Not sure though whether a UDF is able to do the FETCHes.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, a UDF can't do that. :-(
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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