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????

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;

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

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