Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: Will Calling Procedure from user defined function work in DB2 v8 for Windows????

    Will Calling a Procedure from user defined function work in DB2 v8 for Windows????

    it throws the below error while i try to compile:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "CALL TEMP_GET_INT_VARIABLE" was found following
    "ALUE INT default 0; ". Expected tokens may include: "<compound_return>".
    LINE NUMBER=7. SQLSTATE=42601


    Example:
    ---------------------------------------------------------------------------
    CREATE PROCEDURE TEMP_GET_INT_VARIABLE(p_v int)
    RESULT SETS 0
    LANGUAGE SQL
    NOT DETERMINISTIC
    BEGIN
    DECLARE var int;
    END
    @

    CREATE FUNCTION GET_INT_VARIABLE(P_VAR_NAME VARCHAR(2000))
    RETURNS INT
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN ATOMIC
    DECLARE INT_VALUE INT default 0;
    CALL TEMP_GET_INT_VARIABLE(1);
    RETURN INT_VALUE;
    END
    @
    ---------------------------------------------------------------------------

    appreciate any help regarding this problem....

    Jake

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Jake K
    Will Calling a Procedure from user defined function work in DB2 v8 for Windows????

    it throws the below error while i try to compile:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "CALL TEMP_GET_INT_VARIABLE" was found following
    "ALUE INT default 0; ". Expected tokens may include: "<compound_return>".
    LINE NUMBER=7. SQLSTATE=42601


    Example:
    ---------------------------------------------------------------------------
    CREATE PROCEDURE TEMP_GET_INT_VARIABLE(p_v int)
    RESULT SETS 0
    LANGUAGE SQL
    NOT DETERMINISTIC
    BEGIN
    DECLARE var int;
    END
    @

    CREATE FUNCTION GET_INT_VARIABLE(P_VAR_NAME VARCHAR(2000))
    RETURNS INT
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN ATOMIC
    DECLARE INT_VALUE INT default 0;
    CALL TEMP_GET_INT_VARIABLE(1);
    RETURN INT_VALUE;
    END
    @
    ---------------------------------------------------------------------------

    appreciate any help regarding this problem....

    Jake
    You should be able to call a procedure from a UDF... I think your issue is actually that you can't have "MODIFIES SQL DATA" in a function unless it returns a table.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    i think MODIFIES SQL DATA is not the reason... to make myself sure about it, i removed it and compiled the procedure once again... .the same error occurs... as far as i know MODIFIES SQL DATA tells the database that the function modifies data persisted in the database...

    Jake

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Jake K
    i think MODIFIES SQL DATA is not the reason... to make myself sure about it, i removed it and compiled the procedure once again... .the same error occurs... as far as i know MODIFIES SQL DATA tells the database that the function modifies data persisted in the database...

    Jake
    In the SQL Ref volume 2 under CREATE FUNCTION you'll note that "MODIFIES SQL DATA" has a foot note (1), and that footnote reads:

    "Notes: 1 Valid only if RETURNS specifies a table (TABLE column-list)"

    What version of db2 are you using? When I copy and paste your exact definitions I see this error:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA"
    clause are present. LINE NUMBER=10. SQLSTATE=42613
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    It's happenig the same problem with me, if you find a solution please, tell us, my db2 version is the latest 8.2.

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Posts
    107
    sorry for the late reply.... i'm using DB2 v8.1.3 for Windows

    Jake

Posting Permissions

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