Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2012
    Posts
    30

    Thumbs up Unanswered: DB2 cursor looping

    Hi All!

    I'm trying to update table rows through cursor.
    For each row in xml - need to insert (if id in xml != id in table) or update (if ix in xml == id in table)

    Here's code sample -
    Code:
    CREATE OR REPLACE PROCEDURE DLIB.Dictionary_UpdateByIdX(IN pId CHAR(16) FOR BIT DATA,
     IN pCode VARCHAR(50), IN pDescription VARGRAPHIC(500), IN pHierarchy INT, IN pUsercreate CHAR(16) FOR BIT DATA, IN pRecs XML)
    -- добавление/изменение справочника (поиск ведется по id)
    LANGUAGE SQL
    BEGIN
      -- cursor variables
      DECLARE id_ CHAR(16) FOR BIT DATA;
      DECLARE code_ VARGRAPHIC(50);
      DECLARE value_ VARGRAPHIC(500);
      DECLARE parentid_ CHAR(16) FOR BIT DATA;
      DECLARE eof SMALLINT DEFAULT 0;
    
      -- similar records
      DECLARE rec_cursor CURSOR FOR     
        SELECT VARCHAR_BIT_FORMAT(x.id, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'), x.code, x.value, VARCHAR_BIT_FORMAT(x.parentid, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx') FROM XMLTABLE('$r/records/dicrecord' PASSING pRecs AS "r" 
        COLUMNS id CHAR(36) PATH 'id', code VARGRAPHIC(50) PATH 'code', value VARGRAPHIC(500) PATH 'value', parentid CHAR(36) PATH 'parentid') AS x
        WHERE EXISTS (SELECT dr.id FROM DLIB.dl_dicrecords dr WHERE dr.id = VARCHAR_BIT_FORMAT(x.id, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'));
    
      DECLARE CONTINUE HANDLER FOR NOT FOUND  
        SET eof = 1; 	
      
      -- new  records  
      INSERT INTO DLIB.dl_dicrecords(id, code, value, dicid, parentid)
      SELECT VARCHAR_BIT_FORMAT(x.id, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'), x.code, x.value, pId, VARCHAR_BIT_FORMAT(x.parentid, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx') FROM XMLTABLE('$r/records/dicrecord' PASSING pRecs AS "r" 
      COLUMNS id CHAR(36) PATH 'id', code VARGRAPHIC(50) PATH 'code', value VARGRAPHIC(500) PATH 'value', parentid CHAR(36) PATH 'parentid') AS x
      WHERE NOT EXISTS (SELECT dr.id FROM DLIB.dl_dicrecords dr WHERE dr.id = VARCHAR_BIT_FORMAT(x.id, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'));
    
      -- updating
      OPEN rec_cursor;
      FETCH FROM rec_cursor INTO id_, code_, value_, parentid_;
      -- loop
      WHILE (eof = 0) DO    
    
        UPDATE DLIB.dl_dicrecords
        SET code = code_,
     	value = value_,
    	parentid = parentid_
        WHERE id = id_;
      
        FETCH FROM rec_cursor INTO id_, code_, value_, parentid_;	
      END WHILE;		
    
      CLOSE rec_cursor;
    END

    So,
    rec_cursor
    has only similar records in xml and in table.
    But loop circle never starts!!! (eof variable always = 1).

    How make proper looping through DB2 sql cursor ?

    Tried to pass
    SET eof = 1
    in BEGIN/END, but failed..

    Code:
     DECLARE CONTINUE HANDLER FOR NOT FOUND  
    BEGIN    
      SET eof = 1; 	
    END;

    DB2 9.5.7-64bit
    Win2008 Server x64

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it means the first fetch
    FETCH FROM rec_cursor INTO id_, code_, value_, parentid_;
    before the loop is not returning any row --> eof =1
    try to use debug and check all status/rc
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Why not use merge into ??

  4. #4
    Join Date
    Oct 2012
    Posts
    30
    Quote Originally Posted by fengsun2 View Post
    Why not use merge into ??
    Can you tell about it deeply?

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    why copying the doc.. have a look at
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    While SQLCODE <> +100

    doesn't work ?

  7. #7
    Join Date
    Oct 2012
    Posts
    30
    Quote Originally Posted by Lenny77 View Post
    While SQLCODE <> +100

    doesn't work ?
    DB2 returns error - SQL0206N. SQLSTATE=42703.

    fengsun2 answer with merging - the best answer.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by Black Moon View Post
    DB2 returns error - SQL0206N. SQLSTATE=42703.

    fengsun2 answer with merging - the best answer.

    sqlcode = -206 this error "undefined column name".

    Fix the error and change to

    Code:
    While (SQLCODE = 0)
    Lenny

Posting Permissions

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