Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: detecting end of CURSOR?

    I'm sort of new to DB2, using v8.1 on AIX...

    I'm trying to figure out the best way to determine when I'm at the end of a cursor. So far I've seen it done two ways in examples.
    1) run a count(*) with the same where clause used in the cursor and assign the result to a variable, then use that count for a fixed iteration loop.

    2) declare a CONTINUE HANDLER for SQLSTATE 02000 (not found) and iterate until the CONTINUE HANDLER fires

    Both approaches seem flawed. With option 1 you run the risk of the table changing between the select for the cursor and the select of the counter variable.
    With option 2 you can get a SQLSTATE 02000 in the body of the cursor for other operations not related to the cursor fetch.


    Here is an example using option 2:

    CREATE PROCEDURE CURSOR_TEST ()
    SPECIFIC CURSOR_TEST
    LANGUAGE SQL
    P1: BEGIN

    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE var1 varchar(20);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE c1 CURSOR FOR
    SELECT field1
    FROM someTable
    WHERE field2 = 'X'

    DECLARE CONTINUE HANDLER for not_found SET at_end = 1;
    OPEN c1;
    WHILE at_end = 0 DO
    FETCH c1 INTO var1;
    -- do something...
    END WHILE;

    CLOSE c1;

    END P1


    What is the best approach?

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    query

    1. if you want the result to be stable for the select you
    - lock the table in share (no update possible)
    - cursor : select .... for update : the lock level will be increased (no updates
    allowed))
    2. sqlstate 2000 can only be returned if no rows qualify anymore - all fetched or
    no rows qualify
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by craigmc
    2) declare a CONTINUE HANDLER for SQLSTATE 02000 (not found) and iterate until the CONTINUE HANDLER fires
    ...
    With option 2 you can get a SQLSTATE 02000 in the body of the cursor for other operations not related to the cursor fetch.
    Not if you separate the cursor loop and the handler into its own BEGIN...END block.

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Quote Originally Posted by n_i
    Not if you separate the cursor loop and the handler into its own BEGIN...END block.
    So from your suggestion NI, I now have the following, which seems to work. Thanks for the help.

    CREATE PROCEDURE CURSOR_TEST ()
    SPECIFIC CURSOR_TEST
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    -- declare these for DB2, it sets them implicitely (see Application Development Guide: Programming Server Applications)
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE var1 varchar(20);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE c1 CURSOR FOR
    SELECT field1
    FROM someTable
    WHERE field2 = 'X'

    OPEN c1;
    WHILE at_end = 0 DO
    P2: BEGIN
    DECLARE CONTINUE HANDLER for not_found SET at_end = 1;
    FETCH c1 INTO var1;
    END P2;

    -- do something...
    END WHILE;

    CLOSE c1;

    END P1

Posting Permissions

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