Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: creating scalar UDF which modifies data

    Hi,
    I am trying to create a function which returns a scalar data. My function tries to insert a row in a table as well.

    /**

    CREATE function getManufacturerId
    (
    manufacturer VARCHAR(128)
    )
    RETURNS BIGINT
    Language sql
    modifies sql data
    BEGIN ATOMIC

    declare manufacturer_row bigint;
    set manufacturer_row = (select manufacturer_id from manufacturer where manufacturer_name = manufacturer);
    IF(manufacturer_row IS NULL ) THEN

    set manufacturer_row = nextval for manufacturer_seq ;
    INSERT INTO manufacturer VALUES(manufacturer_row, manufacturer);

    END IF;
    return manufacturer_row;
    END@
    */

    I am getting following 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=18. SQLSTATE=42613

    After googling for a while i found out that only table functions can declare "modifies sql data". This raises the question whether i can create a table function to get only one value from the function and use the return value in "call procedure statement".


    In my procedure i have to use the above function

    call getManufacturerId(manufacturer)

    And the above statement will return me the result set if i use table function, how can i use that result set in procedure to get value.

    Similarly instead of using function which has plenty of restrictions, if i create procedure can i get get one value by calling a procedure from a procedure.

    I hope I am able to explain my problem, if not please inform me i will try to explain again.

    Any hint to solve above problem will be highly appreciated.

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's a hint for you in the manual: "MODIFIES SQL DATA valid only if RETURNS specifies a table (TABLE column-list)"

  3. #3
    Join Date
    Oct 2003
    Posts
    11
    Hi,
    Thanks for replying.

    Well as i said i my email earlier that i came to know that only table function can have "modifies sql data".

    My requirement is to use this function (table or scalar) in a procedure where it gets a value (may be a row if i am able to implement that) after calling the function.

    Can somebody tell me how can i use a table function to get a row and use it in a procedure? Does DB2 provides method like fetch one row etc. or means to extract data from the result set?

    I am porting my database from firebird (derived from interbase) to db2 and most of the database provide the functionality like

    Execute procedure (param) returning values retVal;

    or function provide this capability by which we can get a value without restrictions.

    Any help in solving the above problem will be highly appreciated.

  4. #4
    Join Date
    Oct 2003
    Posts
    11
    One solution which i can implement is to create a procedure with out parameter.

    But why to implement procedure when the same should be done using function?

    Other solution which i found out from other forum is to create a function table which returns like

    ...Returns table(int)
    ...Return values(1)

    This will give me only one value.

Posting Permissions

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