Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Question Unanswered: Problem calling procedure from UDF

    Hello,

    I have a function which tries to call a procedure. But for some reason I'm getting the error SQL0374N The "MODIFIES SQL DATA" clause has not been specified in the CREATE FUNCTION statement for LANGUAGE SQL function "myschema.my_func". When I put the clause I'm getting SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. How could I fix it? Here are my function and procedure definitions :

    CREATE PROCEDURE myschema.my_proc(IN id INTEGER, IN F_DATE DATE, OUT res VARCHAR(7))
    P1: BEGIN
    DECLARE rs_atr char(4);
    DECLARE rs_dstart date;
    DECLARE rs_dstop date;
    DECLARE eors INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE rowVar CURSOR FOR select ...
    from ...
    where ...;
    DECLARE CONTINUE HANDLER FOR not_found SET eors = 1;

    OPEN rowVar;
    FETCH rowVar INTO rs_dstart, rs_dstop, rs_atr;

    SET res = '-------';

    WHILE eors = 0 DO
    IF RTRIM(rs_atr) = 'KD' THEN
    SET res = '1234567';
    ELSEIF RTRIM(rs_atr) = 'B' THEN
    SET res = LEFT(res, 5) || '67';
    ELSEIF RTRIM(rs_atr) = 'D' THEN
    SET res = '12345' || RIGHT(res, 2);
    ELSEIF (RTRIM(rs_atr) >= '1' AND RTRIM(rs_atr) <= '7') THEN
    SET res = LEFT(res, INT(RTRIM(rs_atr)) - 1) || RTRIM(rs_atr) || RIGHT(res, 7 - INT(RTRIM(rs_atr)));
    END IF;
    FETCH rowVar INTO rs_dstart, rs_dstop, rs_atr;
    END WHILE;

    CLOSE rowVar;
    END P1

    CREATE FUNCTION myschema.my_func(ID integer, dateStr varchar(50))
    RETURNS VARCHAR(7)
    F1: BEGIN ATOMIC

    DECLARE result varchar(7);
    DECLARE f_date date;

    SET f_date = date(dateStr);

    call myschema.my_proc(ID, f_date, result);
    RETURN result;
    END

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you read the manual for CREATE FUNCTION, the clause MODIFIES SQL DATA is only valid for functions returning a table, not for a scalar function like yours.

    Andy

  3. #3
    Join Date
    Jun 2007
    Posts
    6

    Re: Problem calling procedure from UDF

    I've put this clause only because I've got an error message. Anyway I still don't know how to fix this problem.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS are you using?

    Andy

  5. #5
    Join Date
    Jun 2007
    Posts
    6

    Re: Problem calling procedure from UDF

    DB2 ver. 8.1.15. Enterprise Server Edition

    OS: Win XP Pro

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think you are out of luck with this method. Maybe if you changed the stored procedure to a UDF it might work.

    Andy

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Create the procedure using the READS SQL DATA or CONTAINS SQL clauses. Your procedure (as shown above) doesn't do data modifications, so this should be sufficient. Once the procedure is READS SQL DATA/CONTAINS SQL, you can create your UDF as-is.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2007
    Posts
    6

    Re: Problem calling procedure from UDF

    Yes, that did the trick. Thank you!

Posting Permissions

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