Re: Stored Procedure Exasperation

> Under "Stored Procedure Exasperation" I posted my code which returns > unexpected values in a select statement within a "foreach . . . " loop. > > Per Graeme's suggestion via Email I have beefed up my procedure to handle > NULL rows better. But it's still not working. For a column named > rate.counter_id my select statement returns these values: > > where should actually actual ROWID > counter_id = return returns returned > > 1 NULL NULL NULL OK > 2 2 2 517 OK > 3 NULL 2 517 WRONG! > > > Instead of NULLs, I'm getting the previous row. I don't see anything > wrong with my code and am inclined to think this is an Informix problem. > Any ideas out there? > > Ken Miles Ken, This sounds exactly like a problem that we have recently come across. I have reported the matter to Informix and they are investigating the behaviour from my example stored procedures. My investigations indicate that while within a loop, be it for, while or foreach select statements do not produce correct results when there is no data to be returned. Basically if the select statement returns no rows the variables which are intended to recieve the results will be set to the values they held from the last successfull select iteration of the loop. This occurs even if the variables are deliberately set to some value, such as null, just before the select statement is run. I consider this to be a bug but in some respects it does go along with the philospohy of the SP language. By this I mean that the only statement that specifically is built and documented to handle multiple row selects is the foreach. As the case of a select that returns either one or zero rows is a special case of the multiple row case it could be said that you should use a foreach for all selects that may not return a row. This is the workaround for this problem. I have found that if all selects of this kind are coded as foreach selects the problem goes away. This is, I suspect, because foreach is the only statement capable of recognising the NOTFOUND condition. Personally I would expect the system to leave the values of the variables unchanged in the event of a select failing to return a row. This seems to be the case with a simple select but appears not to work within a loop where values appear to being pulled from some kind of buffer. As I hear more from Informix I will keep you informed. Cheers - Jim