Foreach and Begin Work

I have small code in stored procedure like this on exception set error_code rollback work; end exception foreach select .... begin work .... commit work; end foreach After the SP hits the 'commit work' command at the first row, it closes the cursor and never goes to the next row(showing from trace file). If I use following code, all rows will be effected on exception set error_code rollback work; end exception begin work foreach select .... .... end foreach commit work; Is it true that 'begin work can not used in 'foreach' loop. If possible, I like use the first sample code instead of second one. We use Informix SE 7.20 on HP Unix 10.2

When Informix commits work, it closes all open cursors. A cursor must be declared WITH HOLD in order to remain open. John Carlson Informix DBA WHSmith USA Randy Hao wrote: > > I have small code in stored procedure like this > > on exception > set error_code > rollback work; > end exception > > foreach select .... > begin work > .... > commit work; > end foreach > > After the SP hits the 'commit work' command at the first row, it closes the > cursor and never goes to the next row(showing from trace file). > > If I use following code, all rows will be effected > > on exception > set error_code > rollback work; > end exception > > begin work > foreach select .... > .... > end foreach > commit work; > > Is it true that 'begin work can not used in 'foreach' loop. If possible, I > like use the first sample code instead of second one. We use Informix SE > 7.20 on HP Unix 10.2