Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Bug in a procedure when using IF, IN and SIGNAL together

    DB2 makes the wrong choice in an IF statement when I use IF, IN and SIGNAL together. It always choose the first IF.

    I think I found out a bug in DB2, but I'd like to know if I'm doing something wrong. I built a procedure with some IF-ELSEIF statements. If the last test in ELSEIF is an IN operator, DB2 always goes to the first condition, even if it is false.

    For example:

    CREATE PROCEDURE sp_dummy(
    IN p_x CHAR(1))
    LANGUAGE SQL
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    BEGIN ATOMIC

    IF p_x = 'A' THEN
    SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'px = a';
    ELSEIF p_x = 'B' THEN
    SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = 'px = b';
    ELSEIF p_x IN ('X','Y') THEN
    SIGNAL SQLSTATE '70003' SET MESSAGE_TEXT = 'px in (x,y)';
    END IF;
    END@

    And these returns:

    call sp_dummy('A') --> 'px = a'

    call sp_dummy('B') --> 'px = b'

    call sp_dummy('X') --> 'px = a' it's wrong, it should have returned 'px in (x,y)'

    call sp_dummy('Y') --> 'px = a' it's wrong, it should have returned 'px in (x,y)'

    My DB2 version is v8.1.0.64 FixPak 7 and the server is Linux (SUSE Enterprise Server 9)

    please help me,

    thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I tried it with this code:

    CREATE PROCEDURE ANDY.sp_dummy ( IN px CHARACTER(1) )
    SPECIFIC ANDY.SP_DUMMY
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- px
    ------------------------------------------------------------------------
    P1: BEGIN

    IF px = 'A' THEN
    SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'px = a';
    ELSEIF px = 'B' THEN
    SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = 'px = b';
    ELSEIF px IN ('X','Y') THEN
    SIGNAL SQLSTATE '70003' SET MESSAGE_TEXT = 'px in (x,y)';
    END IF;
    END P1

    with these results:

    DB:LCAD->call andy.sp_dummy('A')
    SQL0438N Application raised error with diagnostic text: "px = a".
    SQLSTATE=70001
    DB:LCAD->call andy.sp_dummy('B')
    SQL0438N Application raised error with diagnostic text: "px = b".
    SQLSTATE=70002
    DB:LCAD->call andy.sp_dummy('X')
    SQL0438N Application raised error with diagnostic text: "px in (x,y)".
    SQLSTATE=70003
    DB:LCAD->call andy.sp_dummy('Y')
    SQL0438N Application raised error with diagnostic text: "px in (x,y)".
    SQLSTATE=70003
    DB:LCAD->call andy.sp_dummy('D')

    Return Status = 0


    Seems to be working for me. I am using V8.1 FP6 on RH Linux.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I happen to also have a RH linux V8.1 FP 7 server and this is the results from the same code:

    DB:LCAD_GDV->call andy.sp_dummy('A')
    SQL0438N Application raised error with diagnostic text: "px = a".
    SQLSTATE=70001
    DB:LCAD_GDV->call andy.sp_dummy('B')
    SQL0438N Application raised error with diagnostic text: "px = b".
    SQLSTATE=70002
    DB:LCAD_GDV->call andy.sp_dummy('X')
    SQL0438N Application raised error with diagnostic text: "px = a".
    SQLSTATE=70001
    DB:LCAD_GDV->call andy.sp_dummy('Y')
    SQL0438N Application raised error with diagnostic text: "px = a".
    SQLSTATE=70001
    DB:LCAD_GDV->call andy.sp_dummy('D')

    Return Status = 0

    Obviously something changed from FP6 to FP7. I know that FP7 (aka v8.2) no longer requires a C compiler for SQL stored procedures. Either theres is a bug (which I suspect), in which case I suggest you contact IBM and open a PMR, or due to the change this is not supported properly.

    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
  •