Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: Cursors in SQL Stored Procedures

    Is there a simple way of safely controlling a cursor loop in SQL procedures ? I know I can do something like:

    declare fetch_over integer default 0;
    declare cursor ....
    DECLARE fetch_over CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR FETCH_OVER set fetch_over =1;
    open cursor;
    while fetch_over = 0 loop
    fetch cursor ...
    end while;

    This works fine except that I want to call some sub-procedures inside my loop. These do some SQL selects as well and can easily return a not found condition. At that point, SQLSTATE gets set to 2000 and this blows out my main cursor loop as well.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    SQLSTATE, if declared in the SP is set automatically by each operation within the SP. You could have something like this:

    DECLARE SQLCODE char(5);
    DECLARE CURSOR...

    OPEN CURSOR
    FETCH CURSOR...
    WHILE SQLSTATE <> '02000' DO
    -- All you code goes here
    FETCH CURSOR...
    END WHILE;

    There is one caveat to this, and I do not know the solution. I posted a problem earlier:

    http://dbforums.com/t694595.html

    Where I have a cursor open and am fetching in a loop. I then call another SP to do some work, The next fetch is returning SQLSTATE '02000' even though there is more data that the cursor should return. (as evidenced if I comment out the SP call, the fetch actually reads the correct amount of rows). No one has posted anything that will help me with this issue yet.

    HTH

    Andy

  3. #3
    Join Date
    Feb 2003
    Posts
    7
    It sounds like I'd still have the same problem, then !

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know, you can try it and see. It maybe some configuration problem / bug on my end. I was hoping that I could get some help here without going to IBM support but that has not been the case yet. Go ahead and write the SP the way you want it and see if it works properly. If so, let me know and then I will know that it is probably a config problem. If not, I will be contacting IBM soon about the issue.
    Let me know either way, and I will let you know what I learn from IBM (probably later this week).

    Andy


    Originally posted by Dagon
    It sounds like I'd still have the same problem, then !

  5. #5
    Join Date
    Feb 2003
    Posts
    7
    I didn't try exactly that, but I did try something similar:

    declare fetch_over integer default 0;
    declare cursor ....
    DECLARE fetch_over CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR FETCH_OVER set fetch_over =1;
    open cursor;
    while fetch_over = 0 loop
    fetch_over = 0; <------
    fetch cursor ...
    end while;

    i.e. I reset my fetch_over variable immediately before the fetch statement on the assumption that the fetch would reset it only if it got a SQLSTATE 2000. It didn't work so I assume that DB2 didn't reset the state after it had had that condition from an earlier statement.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The way you coded it, ANY sqlstate '02000' will cause your fetch_over
    to be set to 1. It will not be reset since the condition in the while clause will now be false. THis means that any statement after your fetch that raises the sqlstate to '02000' will cause the handler to execute. I do not think this is what you desire.

    Handlers are nice but they need to be used correctly to get the desired results. I tend NOT to use them for expected events (e.g. SQLSTATE = '02000'), since any SQL command can cause the handler to execute, not just the one where you want the sqlstate checked and expect it to handle program flow.

    Andy

    Originally posted by Dagon
    I didn't try exactly that, but I did try something similar:

    declare fetch_over integer default 0;
    declare cursor ....
    DECLARE fetch_over CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR FETCH_OVER set fetch_over =1;
    open cursor;
    while fetch_over = 0 loop
    fetch_over = 0; <------
    fetch cursor ...
    end while;

    i.e. I reset my fetch_over variable immediately before the fetch statement on the assumption that the fetch would reset it only if it got a SQLSTATE 2000. It didn't work so I assume that DB2 didn't reset the state after it had had that condition from an earlier statement.

  7. #7
    Join Date
    Feb 2003
    Posts
    7
    So what is the best way to handle an "end of data" condition in a cursor ? My present program runs the cursor query as a "count(*)" first to find out how many rows it will return. It then sets up a counter and loops round until the counter equals the number of rows.

    This seems pretty poor since it means we're running the query twice. Surely there is a better way.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure of a better way until IBM fixes my problem.
    What I have done in the interim to get around the problem
    of subsequent SP calls messing up a cursor, is to open the cursor
    fetch to the row I need, then close the cursor, then call the next SP.
    It works, but it reads the data multiple times.

    remember this is only because the SP is calling another SP within the
    fetch loop. If you are not doing a SP call in the fetch loop, my original post contained code on controlling the fetch loop properly.

    Andy

    Originally posted by Dagon
    So what is the best way to handle an "end of data" condition in a cursor ? My present program runs the cursor query as a "count(*)" first to find out how many rows it will return. It then sets up a counter and loops round until the counter equals the number of rows.

    This seems pretty poor since it means we're running the query twice. Surely there is a better way.

  9. #9
    Join Date
    Jan 2003
    Posts
    2
    May be you can try GET DIAGNOSTICS to check return value of SP. This will prevent to leave the loop even if a SP is called

    DECLARE RETVAL INTEGER DEFAULT 0;
    ...
    CALL TRYIT; ---- TRYIT is a SP

    GET DIAGNOSTICS RETVAL = RETURN_STATUS;
    IF RETVAL <> 0 THEN
    ...
    LEAVE A1;


    *********************************
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE c1 CURSOR FOR
    SELECT deptno, deptname, admrdept
    FROM department
    ORDER BY deptno;

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;
    OPEN c1;
    ins_loop:
    LOOP
    FETCH c1 INTO v_dept, v_deptname, v_admdept;
    IF at_end = 1 THEN
    LEAVE ins_loop;
    ELSEIF v_dept = 'D11' THEN
    ITERATE ins_loop;
    END IF;
    INSERT INTO department (deptno, deptname, admrdept)
    VALUES ('NEW', v_deptname, v_admdept);
    END LOOP;

    CLOSE c1;

    May be you can use FOR statement, it will prevent you to go in continue handler.

    best regards,

  10. #10
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    Is there an SQL WHENEVER clause in db2 v8?
    I'm not sure if the outer cursor would still work or not, but worth a try.

    On the mainframe (db2 v6) I used SQL WHENEVER in a Cobol SP.

    EXEC SQL
    WHENEVER SQLERROR GO TO 999-DISPLAY-ERROR
    END-EXEC.

    ...... Cobol code

    EXEC SQL
    WHENEVER SQLERROR CONTINUE
    END-EXEC

    ...Cobol code and other SQL statements - Handle errors via SQLCODE

    EXEC SQL
    WHENEVER SQLERROR GO TO 999-DISPLAY-ERROR
    END-EXEC.

Posting Permissions

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