title: DB2 update in Loop does not execute
body: <p>I have the following code:</p>
  
  <pre><code>  BEGIN
  DECLARE cmd VARCHAR(1024); DECLARE attr CHAR(10); 
  DECLARE attr2 CHAR(10); 
  DECLARE at_end INTEGER DEFAULT 0;
  DECLARE not_found CONDITION FOR SQLSTATE '02000';
  DECLARE updstr1 VARCHAR(1024);
  DECLARE updstr2 VARCHAR(1024);
  
  
  DECLARE c1 CURSOR FOR SELECT cmd, attr, attr2 
                        FROM commandtbl2;
  
  DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
  
  SET updstr1 = 'update commandtbl3 t2 set t2.XXXX = attr where t2.cmd=cmd';
  SET updstr2 = 'update commandtbl3 t2 set t2.XXXX= attr2 where t2.cmd=cmd';
  OPEN c1;
  
  ins_loop: LOOP
  
    FETCH c1 INTO cmd, attr, attr2; 
  
    IF at_end = 1 THEN
      LEAVE ins_loop;
    ELSEIF cmd != '' THEN
      ITERATE ins_loop;
    END  IF;
  
    set updstr1= REPLACE(updstr1,'XXXX','attr');
    set updstr2= REPLACE(updstr2,'XXXX','attr2');
  
    EXECUTE IMMEDIATE updstr1;
    EXECUTE IMMEDIATE updstr2;
  
  
  END LOOP;
  
  CLOSE c1;
  END@
  </code></pre>
  
  <p>It all runs without error but when I check commandtbl3 nothing was updated. All values are NULL but should be replaced. There are 7 rows in both tables and 5 have matching cmd values so those 5 rows should update. </p>
  
  <p>I am not sure whether the replace is not working or the Execute Immediate.</p>
  
  <p>With debuging I found out, that the updstr1 and 2 variables are empty after the replace... but why?</p>
  
  <p>Can you help me?
  Thanks,
  TheVagabond</p>
  
  <p>As requested here are the definitions of commandtbl3 and 2 :</p>
  
  <pre><code>CREATE TABLE "TEST"."COMMANDTBL3" (
      "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 2 NO ORDER ), 
      "CMD" VARCHAR(1024 OCTETS) NOT NULL, 
      "ATTR" CHAR(10 OCTETS), 
      "ATTR2" CHAR(10 OCTETS), 
      CONSTRAINT "CC1455789123996" PRIMARY KEY
      ("ID")
  )
  
  CREATE TABLE "TEST"."COMMANDTBL2" (
      "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 2 NO ORDER ), 
      "CMD" VARCHAR(1024 OCTETS) NOT NULL, 
      "ATTR" CHAR(10 OCTETS), 
      "ATTR2" CHAR(10 OCTETS), 
      CONSTRAINT "CC1455789123996" PRIMARY KEY
      ("ID")
  )
  </code></pre>
  
  <p>COmmandtbl3 has CMD equal to commandtbl2 except for 2 rows (7 rows total) and attr and attr2 are NMULL in commandtbl3. I want the values of commandtbl2 attr and attr2 to be written to commandtbl3 via update with an replace to the command so I can have placeholders XXXX</p>
  
  <p>HERE IS THE WORKING CODE:</p>
  
  <p>If someone has the same prob here is the working code, even if changed from the starting one (while loop now, etc):</p>
  
  <pre><code>BEGIN
  DECLARE EOF INTEGER DEFAULT 0;
  DECLARE cmd VARCHAR(1024); DECLARE attr CHAR(10); 
  DECLARE attr2 CHAR(10); 
  DECLARE not_found CONDITION FOR SQLSTATE '02000';
  DECLARE updstr1 VARCHAR(1024);
  DECLARE updstr2 VARCHAR(1024);
  DECLARE stmt1 STATEMENT;
  DECLARE stmt2 STATEMENT;
  
  DECLARE c1 CURSOR FOR SELECT cmd, attr, attr2 
                    FROM commandtbl2 order by cmd;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;
  
  SET updstr1 = 'update commandtbl3 t2 set t2.XXXX = ? where t2.cmd= ?';
  SET updstr2 = 'update commandtbl3 t2 set t2.XXXX= ? where t2.cmd= ?';
  set updstr1= REPLACE(updstr1,'XXXX','attr');
  set updstr2= REPLACE(updstr2,'XXXX','attr2');
  insert into temptbl(text1,text2) values(updstr1,updstr2);
  
  prepare stmt1 from updstr1;
  prepare stmt2 from updstr2;
  
  OPEN c1;
  
  
  WHILE EOF = 0 DO
  
  FETCH from c1 INTO cmd, attr, attr2; 
  EXECUTE stmt1 using attr, cmd;
  EXECUTE stmt2 using attr2, cmd;
  
  END WHILE;
  
  CLOSE c1;
  END@
  </code></pre>