Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: Immposible foreach loop

    Hello everybody

    I have been writing SPL for a number of years on a large but antique Informix 7.?? Db.

    Yesterday a rather large script took a turn that I did not expect.
    The SQL hit a foreach loop that retrieved a single row (There are instances when either none or > 1 can be returned and the loop is coded to make a few choices along the way)

    Within the loop the row was changed the expectation was for the sql then to exit the foreach and move on through the proc.(As there was only one row going in).But it did a second itteration
    retrieving the row that had just been updated???

    Now my understanding of a foreach statement is that the cursor list is built at the entry point of the foreach and the list is then cursored through.

    A watered down example of the statement is....


    --CASE2
    TRACE "Case 2";

    LET var_loopcount = 0;

    FOREACH act_end FOR
    SELECT
    dat_tim_fr,
    dat_tim_to
    INTO
    var_act_dat_tim_fr,
    var_act_dat_tim_to
    FROM
    tablex
    WHERE
    blah=var_blah
    AND
    NVL(dat_tim_to,CURRENT) >= var_req_dat_tim_fr
    AND
    dat_tim_fr <= NVL(var_req_dat_tim_to,CURRENT)
    ORDER BY
    dat_tim_fr
    ASC

    TRACE " Got.. dat_tim_fr '||var_act_dat_tim_fr;
    TRACE " dat_tim_to '||var_act_dat_tim_to;

    IF var_loopcount = 0 THEN

    UPDATE
    tablex
    SET
    dat_fr = var_req_dat_tim_fr,
    dat_tim_fr = var_req_dat_tim_fr,
    dat_to = var_req_dat_tim_to,
    dat_tim_to = var_req_dat_tim_to
    WHERE
    blah=var_blah
    AND
    NVL(dat_tim_to,CURRENT) = NVL(var_act_dat_tim_to,CURRENT)
    AND
    dat_tim_fr = var_act_dat_tim_fr;

    TRACE "UPDATED! "||DBINFO('sqlca.sqlerrd2')||" tablex row(s)";

    ELSE --Loopcount > 0

    DELETE FROM
    tablex
    WHERE
    blah = var_blah
    AND
    NVL(dat_tim_to,CURRENT) = NVL(var_act_dat_tim_to,CURRENT)
    AND
    dat_tim_fr = var_act_dat_tim_fr;


    TRACE "DELETED! "||DBINFO('sqlca.sqlerrd2')||" tablex row(s)";

    END IF;

    LET var_loopcount = var_loopcount + 1;

    END FOREACH;

    Sorry I cant post the actual SQL its over 10K lines and contains proprietry data...

    But when the sql runs the secon itteration shows dat_tim_fr that was not there as the sql goes into the loop..

    The primary key on tablex is a composite key using the colums blah,dat_tim_fr and dat_tim_to

    Any ideas or have i gone mad......Or can informix indead populate the cursor from within the execution of the loop????
    Last edited by jobby; 07-04-07 at 09:34.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    you sure that the select on foreach return only 1 row??

    try get a snapshot for this foreach to sure that select return only 1 row.
    My suggestion is before the foreach execute a statment like below and check the temporary table after execution :

    SELECT
    dat_tim_fr,
    dat_tim_to
    FROM
    tablex
    WHERE
    blah=var_blah
    AND
    NVL(dat_tim_to,CURRENT) >= var_req_dat_tim_fr
    AND
    dat_tim_fr <= NVL(var_req_dat_tim_to,CURRENT)
    INTO TEMP TMP1_TABLEX WITH NO LOG;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    Ohh yes I am absolutely certain that there is only 1 row...
    The update is to correct some rather complex data. unfortunately the entry corrects the data and the second itteration delete it....

    There are a few identifying marks to the deleted record that shows very clearly that it is the updated record that is being deleted.. ;-(

    I am now starting to question the exact process being used by Informix when creating a Foreach loop.

    Regards John

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    well.. if you can not upgrade your IDS to version 10...

    try use named cursor...something like this:

    CREATE PROCEDURE foreach_ex()
    DEFINE i, j INT;
    FOREACH SELECT c1 INTO i FROM tab ORDER BY 1
    INSERT INTO tab2 VALUES (i);
    END FOREACH
    FOREACH cur1 FOR SELECT c2, c3 INTO i, j FROM tab
    IF j > 100 THEN
    DELETE FROM tab WHERE CURRENT OF cur1;
    CONTINUE FOREACH;
    END IF
    UPDATE tab SET c2 = c2 + 10 WHERE CURRENT OF cur1;
    END FOREACH
    FOREACH EXECUTE PROCEDURE bar(10,20) INTO i
    INSERT INTO tab2 VALUES (i);
    END FOREACH
    END PROCEDURE; -- foreach_ex

    http://publib.boulder.ibm.com/infoce...oc/sqls932.htm


    Sorry my bad english...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Jul 2007
    Posts
    3
    Quote Originally Posted by ceinma
    well.. if you can not upgrade your IDS to version 10...

    try use named cursor...something like this:

    CREATE PROCEDURE foreach_ex()
    DEFINE i, j INT;
    FOREACH SELECT c1 INTO i FROM tab ORDER BY 1
    INSERT INTO tab2 VALUES (i);
    END FOREACH
    FOREACH cur1 FOR SELECT c2, c3 INTO i, j FROM tab
    IF j > 100 THEN
    DELETE FROM tab WHERE CURRENT OF cur1;
    CONTINUE FOREACH;
    END IF
    UPDATE tab SET c2 = c2 + 10 WHERE CURRENT OF cur1;
    END FOREACH
    FOREACH EXECUTE PROCEDURE bar(10,20) INTO i
    INSERT INTO tab2 VALUES (i);
    END FOREACH
    END PROCEDURE; -- foreach_ex

    http://publib.boulder.ibm.com/infoce...oc/sqls932.htm


    Sorry my bad english...
    Thanks for the reply..

    Unfortunately IDS 7.3 dosent support "WHERE CURRENT"...

    I have modified the delete clause to ensure the updated record is not deleted.
    I think this may just be another 7.3 bug (Yes there are a number of them!)

    Thanks again for your help

Posting Permissions

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