4GL/SQL behaviour not as expected

# cat $INFORMIXDIR/etc/*-crINFORMIX-4GL Interactive Debugger Version 7.20.UD8 INFORMIX-4GL Version 7.20.UD8 INFORMIX-4GL Rapid Development System Version 7.20.UD8 Informix Dynamic Server Version 7.30.UC7 INFORMIX-SQL Version 7.20.UD8 Hp-Ux 10.20 I have a 4GL program which does the following; LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple where clause using place holders}" PREPARE p_detail FROM l_sql DECLARE my_cursor WITH HOLD FOR p_detail OPEN my_cursor BEGIN WORK LOCK TABLE my_table IN EXCLUSIVE MODE WHILE l_result = 0 FETCH my_cursor INTO my_record Process the row and possibly update it and possibly insert a new row into my_table. This newly inserted row fulfils all of the necessary criteria to be included in the where clause for the cursor declared above! COMMIT WORK <---- BUG** END WHILE COMMIT WORK <--- BUG FIXED CLOSE my_cursor I expected the foreach loop to only process rows that existed when the cursor was opened. However rows being inserted within the loop are being processed, giving some interesting results. The table is locked in exclusive mode and the isolation level is cursor stability. Examination of the code shows the indicated bug where the transaction was being declared before the while loop but being committed/rolled back before the end while, but I don't understand how this could cause this effect. The database has buffered logging. no obvious errors are being displayed and the program is doing what is expected apart from the inclusion of these additional rows. I've fixed this and am re-testing but in the mean time if anyone has any ideas.... -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .

Tony Flaherty wrote: > # cat $INFORMIXDIR/etc/*-cr> INFORMIX-4GL Interactive Debugger Version 7.20.UD8 > INFORMIX-4GL Version 7.20.UD8 > INFORMIX-4GL Rapid Development System Version 7.20.UD8 > Informix Dynamic Server Version 7.30.UC7 > INFORMIX-SQL Version 7.20.UD8 > > Hp-Ux 10.20 > > I have a 4GL program which does the following; > > LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple > where clause using place holders}" > > PREPARE p_detail FROM l_sql > > DECLARE my_cursor WITH HOLD FOR p_detail > > OPEN my_cursor > > BEGIN WORK > LOCK TABLE my_table IN EXCLUSIVE MODE > > WHILE l_result = 0 > FETCH my_cursor INTO my_record > > Process the row and possibly update it and possibly insert a > new row into my_table. > This newly inserted row fulfils all of the necessary criteria to > be included in the where clause for the cursor declared above! > > COMMIT WORK <---- BUG** > END WHILE > > COMMIT WORK <--- BUG FIXED > CLOSE my_cursor > > I expected the foreach loop to only process rows that existed when the > cursor was opened. However rows being inserted within the loop are being > processed, giving some interesting results. > > The table is locked in exclusive mode and the isolation level is cursor > stability. > > Examination of the code shows the indicated bug where the transaction was > being declared before the while loop but being committed/rolled back before > the end while, but I don't understand how this could cause this effect. The > database has buffered logging. no obvious errors are being displayed and > the program is doing what is expected apart from the inclusion of these > additional rows. I've fixed this and am re-testing but in the mean time if > anyone has any ideas.... > > -- > --------------------------------------- > Tony Flaherty aef@mfs.misys.co.uk > Analyst Programmer > Misys Financial Systems > All statements and opinions are my own, > Misys don't pay me enough to have opinions > on their behalf > > . Why donot try this, this will serve better control LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple where clause using place holders}" PREPARE p_detail FROM l_sql DECLARE my_cursor WITH HOLD FOR p_detail OPEN my_cursor BEGIN WORK LOCK TABLE my_table IN EXCLUSIVE MODE FETCH my_cursor INTO my_record WHILE SQLCA.SQLCODE = 0 # --- <process whatever> FETCH my_cursor INTO my_record # --- NO SQL Code AFTER FETCH, FETCH should be the last SQL statements END WHILE # --- Above while will ensure program to get into loop only when atleast one row is satisfied with the criteria. COMMIT WORK <--- BUG FIXED CLOSE my_cursor Rgds

Yep, this is the conventional way of using a while loop in these circumstances. The control is O.k. as is, I tend to check the result of the fetch and EXIT WHILE if it failed, I prefer having just the one fetch for the cursor in the code where possible. I omitted all of the error checking code from my example for laziness :o/ Also I know my data well enough to know that there will always be some rows fetched by this particular cursor. Thanks for the reply :-) -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Chetan Anand wrote in message <3899E2C8.1E8B3BC2@systems.dhl.com>... Tony Flaherty wrote: # cat $INFORMIXDIR/etc/*-cr INFORMIX-4GL Interactive Debugger Version 7.20.UD8 INFORMIX-4GL Version 7.20.UD8 INFORMIX-4GL Rapid Development System Version 7.20.UD8 Informix Dynamic Server Version 7.30.UC7 INFORMIX-SQL Version 7.20.UD8 Hp-Ux 10.20 I have a 4GL program which does the following; LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple where clause using place holders}" PREPARE p_detail FROM l_sql DECLARE my_cursor WITH HOLD FOR p_detail OPEN my_cursor BEGIN WORK LOCK TABLE my_table IN EXCLUSIVE MODE WHILE l_result = 0 FETCH my_cursor INTO my_record Process the row and possibly update it and possibly insert a new row into my_table. This newly inserted row fulfils all of the necessary criteria to be included in the where clause for the cursor declared above! COMMIT WORK <---- BUG** END WHILE COMMIT WORK <--- BUG FIXED CLOSE my_cursor I expected the foreach loop to only process rows that existed when the cursor was opened. However rows being inserted within the loop are being processed, giving some interesting results. The table is locked in exclusive mode and the isolation level is cursor stability. Examination of the code shows the indicated bug where the transaction was being declared before the while loop but being committed/rolled back before the end while, but I don't understand how this could cause this effect. The database has buffered logging. no obvious errors are being displayed and the program is doing what is expected apart from the inclusion of these additional rows. I've fixed this and am re-testing but in the mean time if anyone has any ideas.... -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Why donot try this, this will serve better control LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple where clause using place holders}" PREPARE p_detail FROM l_sql DECLARE my_cursor WITH HOLD FOR p_detail OPEN my_cursor BEGIN WORK LOCK TABLE my_table IN EXCLUSIVE MODE FETCH my_cursor INTO my_record WHILE SQLCA.SQLCODE = 0 # --- <process whatever> FETCH my_cursor INTO my_record # --- NO SQL Code AFTER FETCH, FETCH should be the last SQL statements END WHILE # --- Above while will ensure program to get into loop only when atleast one row is satisfied with the criteria. COMMIT WORK <--- BUG FIXED CLOSE my_cursor Rgds

Tony Flaherty wrote: > > I have a 4GL program which does the following; > > LET l_sql = "SELECT ....... FROM my_table, another_table WHERE {simple > where clause using place holders}" > > PREPARE p_detail FROM l_sql > > DECLARE my_cursor WITH HOLD FOR p_detail > > OPEN my_cursor > > BEGIN WORK > LOCK TABLE my_table IN EXCLUSIVE MODE > > WHILE l_result = 0 > FETCH my_cursor INTO my_record > > Process the row and possibly update it and possibly insert a > new row into my_table. > This newly inserted row fulfils all of the necessary criteria to > be included in the where clause for the cursor declared above! > > COMMIT WORK <---- BUG** > END WHILE > > COMMIT WORK <--- BUG FIXED > CLOSE my_cursor > > I expected the foreach loop to only process rows that existed when the > cursor was opened. However rows being inserted within the loop are being > processed, giving some interesting results. Informix does not have an Isolation Level which keeps the state of the database at the time the cursor was opened. Cursor Stability only keeps a lock on the single row you are reading until you read the next row. Off the top of my head, I can't think of any way to do what you want except to read your rows into a temp table and create your cursor against that. > The table is locked in exclusive mode and the isolation level is cursor > stability. > > Examination of the code shows the indicated bug where the transaction was > being declared before the while loop but being committed/rolled back before > the end while, but I don't understand how this could cause this effect. The > database has buffered logging. no obvious errors are being displayed and > the program is doing what is expected apart from the inclusion of these > additional rows. I've fixed this and am re-testing but in the mean time if > anyone has any ideas.... June -- june_t@hotmail.com Living on Snickers bars in San Mateo

Thanks for the info. I've added a condition to the where clause which excludes these newly added details. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . June Tong wrote in message <38A2353E.DB8799B7@hotmail.com>... >Tony Flaherty wrote: [snip] [Me] >> I expected the foreach loop to only process rows that existed when the >> cursor was opened. However rows being inserted within the loop are being >> processed, giving some interesting results. > [June] >Informix does not have an Isolation Level which keeps the state of the >database at the time the cursor was opened. Cursor Stability only keeps >a lock on the single row you are reading until you read the next row. >Off the top of my head, I can't think of any way to do what you want >except to read your rows into a temp table and create your cursor >against that. > [snip]