Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Question Unanswered: not able to call function

    Hi ,

    I am not able to call bellow function ..

    Code----

    create FUNCTION GRPTDTA.ABC_TEST( )
    RETURNS CHAR(1)
    LANGUAGE SQL
    BEGIN
    DECLARE RETVAL CHAR (1) ;
    DECLARE VC_XML CHAR(2000);
    select 'A' into RETVAL from SYSIBM.SYSDUMMY1;
    RETURN (RETVAL) ;
    END


    calling

    select GRPTDTA.ABC_TEST() from SYSIBM.SYSDUMMY1;

    given error --
    SQL Error: DB2 SQL Error: SQLCODE=-303, SQLSTATE=22001, SQLERRMC=1;*N

  2. #2
    Join Date
    Jan 2013
    Posts
    10
    Hello,
    I found this old topic with no answers. I have the same question: how could I execute a user defined function in db2?
    My function has 3 integer parameters that must be inserted as a new row into a table if they are not already there. So the function returns a message (if the row is already there) or the id of the new row.
    I am trying to call the function like this:

    SELECT SUBJECT_ASSIGNMENT_ADD(2, 1, 148)

    and I get this error message:

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "NT_ADD(9040, 1, 148)". Expected tokens may include: "<table_expr>".
    SQLSTATE=42601

    Could anyone help me? Thank you!

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT SUBJECT_ASSIGNMENT_ADD(2, 1, 148)
    It might be not the issue related to a function.

    Though, FROM clause is not mandatory in some DBMS (like Microsoft SQL Server),
    you must include FROM clause in a select statement in DB2.

    You may want to use VALUES (instead of the statement) like...
    VALUES SUBJECT_ASSIGNMENT_ADD(2, 1, 148)


    Saying moreover,
    that syntax(select statement without FROM clause) might be not compatible with SQL sandard, like...

    Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT SUBJECT_ASSIGNMENT_ADD(2, 1, 148)

    See Validator Examples

    Code:
    Result:
    
    SELECT SUBJECT_ASSIGNMENT_ADD(2, 1, 148) 
    
    ^
    syntax error: <end>
        expected: * + , - -> . / < <= <> = > >= [ || <ascii identifier>
                  <delimited identifier> <identifier> <unicode identifier> AND AS
                  AT BETWEEN COLLATE DAY FROM HOUR IN INTO IS LIKE MATCH MEMBER
                  MINUTE MONTH MULTISET NOT OR OVERLAPS SECOND SIMILAR SUBMULTISET
                  YEAR
      correction: . * FROM <identifier> <end>

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It looks that VALUES is compatible with SQL standard.

    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    VALUES SUBJECT_ASSIGNMENT_ADD(2, 1, 148)
    See Validator Examples

    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    F391, "Long identifiers"
    F661, "Simple tables"

  5. #5
    Join Date
    Jan 2013
    Posts
    10
    "VALUES.." statement doesn't work either.
    This is the error:

    SQL0740N Routine "ARMSADM.SUBJECT_ASSIGNMENT_ADD" (specific name
    "SQL130628140045700") is defined with the MODIFIES SQL DATA option, which is
    not valid in the context where the routine is invoked. SQLSTATE=51034

    I tried also "SELECT SUBJECT_ASSIGNMENT_ADD(2, 1, 148) FROM SYSIBM.SYSDUMMY1;" and I got the same error from above.

    I simplified the function to this:

    CREATE OR REPLACE FUNCTION TESTFUNCT(subject INTEGER, role INTEGER, scope INTEGER)
    RETURNS varchar(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    BEGIN
    INSERT INTO subject_assignment (subject_id, role_id, scope_id) VALUES (subject, role, scope);
    RETURN SYSIBM.IDENTITY_VAL_LOCAL();
    END

    and I still don't know how to run it.
    Last edited by Madalina; 07-03-13 at 04:32.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release/fixpack and platform OS are you using?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are some restrictions to use the "MODIFIES SQL DATA" option for a function.

    For example, on DB2 9.7 for LUW...
    Notes:
    ...
    3. Valid if RETURNS specifies a table (that is, TABLE column-list).
    Also valid if RETURNS specifies a scalar result and the SQL-function-body is a compound SQL (compiled) statement.
    In this case, the resulting function can only be used as the only element on the right hand side of an assignment statement that is within a compound SQL (compiled) statement.
    ...
    CREATE FUNCTION (SQL scalar, table, or row) - IBM DB2 9.7 for Linux, UNIX, and Windows

    Please see the manuals or Information Center corresponding to your DB2 version/release/fixpack and platform OS.

  8. #8
    Join Date
    Jan 2013
    Posts
    10
    DB2 V9.7.0.0 (I couldn't install the fixpack 8 yet), LINUXAMD6497.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see Information Center to use the "MODIFIES SQL DATA" option.
    CREATE FUNCTION (SQL scalar, table, or row) - IBM DB2 9.7 for Linux, UNIX, and Windows

    Example 4 might be usefull in particular.

    I thought that no major enhancement related to the "MODIFIES SQL DATA" option was included in DB2 9.7 Fix Packs,
    except in Fix Pack 6 which might be not related to your function.
    Fix pack summary - IBM DB2 9.7 for Linux, UNIX, and Windows
    So, I think you can try the "MODIFIES SQL DATA" option by referencing Example 4, without installing fixpack 8.
    Last edited by tonkuma; 07-03-13 at 06:05.

  10. #10
    Join Date
    Jan 2013
    Posts
    10
    The function is successfully created and yes, it is similar to the one in example 4.
    And it has the "MODIFIES SQL DATA" option.
    But I can't execute it.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But I can't execute it.
    Why?

    How did you executed the function?

    What error(s) did you got?

    The function is successfully created and yes, it is similar to the one in example 4.
    What exactly did you execued the CREATE FUNCTION statement?

  12. #12
    Join Date
    Jan 2013
    Posts
    10
    Yes, I executed the CREATE FUNCTION STATEMENT and the SQL command completed successfully:

    CREATE OR REPLACE FUNCTION TESTFUNCT(subject INTEGER, role INTEGER, scope INTEGER)
    RETURNS varchar(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    BEGIN
    INSERT INTO TEST (subject_id, role_id, scope_id) VALUES (subject, role, scope);
    RETURN SYSIBM.IDENTITY_VAL_LOCAL();
    END

    Next I need to execute the function to add a new row in TEST table and to find out the new id. So I tried the following statements:

    SELECT TESTFUNCT(9040, 1, 147)
    - got the error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "TFUNCT(9040, 1, 147)". Expected tokens may include: "<table_expr>

    SELECT TESTFUNCT(9040, 1, 147) FROM SYSIBM.SYSDUMMY1
    or
    VALUES TESTFUNCT(9040, 1, 147)
    - got the same error for both:
    SQL0740N Routine "TESTFUNCT" (specific name "SQL130704084156700") is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked.

    I understand from the restriction of using "MODIFIES SQL" that "the resulting function can only be used as the only element on the right hand side of an assignment statement that is within a compound SQL (compiled) statement." but I don't know how to do it, because I didn't find any example.

    I had the same function in MySQL and it worked like this:
    SELECT TESTFUNCT(9040, 1, 147)
    without any error or problem.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    Please see Information Center to use the "MODIFIES SQL DATA" option.
    CREATE FUNCTION (SQL scalar, table, or row) - IBM DB2 9.7 for Linux, UNIX, and Windows

    Example 4 might be usefull in particular.

    I thought that no major enhancement related to the "MODIFIES SQL DATA" option was included in DB2 9.7 Fix Packs,
    except in Fix Pack 6 which might be not related to your function.
    Fix pack summary - IBM DB2 9.7 for Linux, UNIX, and Windows
    So, I think you can try the "MODIFIES SQL DATA" option by referencing Example 4, without installing fixpack 8.
    Please see Example 4 which is an example of a table function.
    a table function can be called in FROM clause, like...
    ... FROM TABLE(<your table function>(...) )

    See from-clause and table-reference in subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

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