Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: atomic compound statements

    Hi,
    Im a DB2 newbie. I'm trying to declare a cursor inside a compound statement in a UDF, but the compiler always fails on the "declare cursor" line with err msg: "SQL0104N An unexpected token "cursor for..."

    create function xxx.xxx()
    returns integer
    begin atomic
    declare x cursor for select * from xxx.table1
    end

    I've read that only a subset of SQL commands is permitted within an atomic compound statement, but which ones??? The UDF will not accept a non-atomic BEGIN-END

    I'm using DB2 v8.1 on Windows XP.

    Thanks in advance...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the V 8.1 SQL Reference manual Volume 2 (in my manual it is on page 123) - Section "Compound SQL (Dynamic)". It tells you everything you need to know.

    Andy

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    Originally posted by ARWinner
    Look in the V 8.1 SQL Reference manual Volume 2 (in my manual it is on page 123) - Section "Compound SQL (Dynamic)". It tells you everything you need to know.

    Andy
    Thanks Andy,
    unfortunately, I don't have this book. Is it available online, or could you post the relevant bit?

    regards,
    Deji

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deji,
    Online documentation is located here:
    http://publib.boulder.ibm.com/infoce...help/index.jsp

    To find what you are looking for: under Contents:
    REFERENCE -> SQL -> SQL Statements -> Compound SQL (Dynamic)

    You can download the manuals at:
    http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main

    Andy


    Originally posted by deji_akadiri
    Thanks Andy,
    unfortunately, I don't have this book. Is it available online, or could you post the relevant bit?

    regards,
    Deji

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    Originally posted by ARWinner
    Deji,
    Online documentation is located here:
    http://publib.boulder.ibm.com/infoce...help/index.jsp

    To find what you are looking for: under Contents:
    REFERENCE -> SQL -> SQL Statements -> Compound SQL (Dynamic)

    You can download the manuals at:
    http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main

    Andy
    Thanks again Andy. It appears I can't declare a cursor within a UDF. Is this consistent with your experience?, and if so, how would you run a dynamic SQL select within a UDF?

    Regards,
    Deji

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deji,
    No, you cannot use a cursor in a UDF. Can you post what you are trying to do, and maybe I can help you?

    Andy

  7. #7
    Join Date
    Mar 2004
    Posts
    8
    Originally posted by ARWinner
    Deji,
    No, you cannot use a cursor in a UDF. Can you post what you are trying to do, and maybe I can help you?

    Andy
    Andy, I wanted to write a function which would accept a lookup-value, then use this value to lookup against a column in another table, and return a column in the 2nd table. (eg lookup id 45 in the staff table, and return "SMITH"). I wrote this succesfully as a SP, but I would now like to convert it to a UDF. The SP code is as follows:

    CREATE procedure ARENA_SCHEMA.LookupByInteger(in p_intLookupValue integer, in p_strLookupTable varchar(50),
    in p_strLookupColumn varchar(50), in p_strReturnColumn varchar(50),
    out p_strReturnValue varchar(255))
    reads sql data
    result sets 0
    /* looks up on value <p_intLookupValue> into table <p_strLookupTable>.<p_strLookupColumn>
    and returns value of <p_strLookupTable>.<p_strReturnValue> in <p_strReturnValue> */
    /* returns 0 if the lookup succeeds, -1 if the lookups fails, -2 if duplicate matches found */
    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    BEGIN

    declare intReturnValue integer;
    declare strSQL varchar(250);
    declare intRowNotFound int default 0;
    declare intRowCount integer;
    declare strReturnValue varchar(250);
    declare objStatement statement;

    declare objCursor cursor for objStatement;
    -- select p_strReturnColumn from p_strLookupTable where
    -- p_strLookupColumn = p_intLookupValue;

    declare CONTINUE handler for NOT FOUND
    set intRowNotFound = 1;

    set p_strLookupTable = ltrim(rtrim(p_strLookupTable));
    set p_strLookupColumn = ltrim(rtrim(p_strLookupColumn));
    set p_strReturnColumn = ltrim(rtrim(p_strReturnColumn));

    set strSQL = 'select ' || p_strReturnColumn ||
    ' from ' || p_strLookupTable ||
    ' where ' || p_strLookupColumn || ' = ' || char(p_intLookupValue);

    if (p_strLookupTable = '' or p_strLookupColumn = '' or p_strReturnColumn = '') then
    /* all parameters must be supplied */
    set intReturnValue = -1;
    else

    prepare objStatement from strSQL;
    open objCursor;

    set intRowCount = 0;
    set intRowNotFound = 0;
    while (intRowCount < 2 and intRowNotFound = 0) do
    fetch objCursor into strReturnValue;
    if (intRowNotFound = 1) then
    if (intRowCount = 0) then
    set intReturnValue = -1;
    end if;
    else
    set intReturnValue = 1;
    set intRowCount = intRowCount+1;
    if (intRowCount > 1) then
    set intReturnValue = -2;
    end if;
    end if;
    end while;

    close objCursor;

    if (intReturnValue >= 0) then
    set p_strReturnValue = strReturnValue;
    else
    set p_strReturnValue = null;
    end if;

    end if;

    return intReturnValue;
    end


    Thanks,
    Deji

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deji,
    Your problem in the UDF is that you cannot build the select statement. You could use the FOR statement if the SELECT statement is known. If you have a finite set of values for lookuptable.lookupcolumn then you could write it as a series of IF-THEN-ELSE. Otherwise you will need to stay with the SP.

    Andy

  9. #9
    Join Date
    Mar 2004
    Posts
    8
    Originally posted by ARWinner
    Deji,
    Your problem in the UDF is that you cannot build the select statement. You could use the FOR statement if the SELECT statement is known. If you have a finite set of values for lookuptable.lookupcolumn then you could write it as a series of IF-THEN-ELSE. Otherwise you will need to stay with the SP.

    Andy
    Thanks for taking the time to look at this Andy. You've saved me from further fruitless searching. Coming from a Microsoft (SQL Server/VB) background, the biggest problem I'm finding is that information on DB2 or J2EE is not as available as with MS products, even if the technology is better...

    By the way, if I wrote the UDF in C, could that get around the dynamic SQL problem?

    Regards,
    Deji

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deji,
    C or Java should allow you to do what you want.
    Andy

Posting Permissions

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