Re: Report lines can be rolled back?

>Informix v7.23 >> >>In a automatic order entry program, I was having a problem with report >>lines disappearing. I discovered that the problem only occured on >>report lines that happened within a BEGIN WORK...ROLLBACK WORK >>statement. Now, I know that report lines are stored in a temporary >>table, so I understand that a ROLLBACK WORK statement may cause the >>lines that have been put in that table to be un-inserted. But why would >>it only affect some of them? I decided to write a little test program >>to illustrate this, and was hoping someone else has ran into this >>feature (?) and had any idea what Informix is doing here. >> >>The odd thing is, you would expect the program to either show 100 lines >>for each line of text, or show no lines for the rolled back line of >>text. But it does neither. It actually shows 100 lines for the >>committed line of text, and 96 for the rolled back line. >> >>What am I missing here? >> >>As a temporary solution, I have just taken out the ROLLBACK work, but I >>do kinda need that in there to undo some database updates. > >A very curious problem. Are you sure your value of 96 wasn't on a different >report? I modified your code to include an integer parameter in with the >report, and then I got 96; up until then, I only got 51 lines. Equally >weird, >just slightly different report. > >Yes, I do have an explanation, but it certainly was anything but obvious. >I've attached the output from sqliprint run on the modified version of the >report (with 84 characters per PUT row instead of 80 as in the original). > >You can look through it but observe that the report temp table is created >WITH NO LOG; that means that changes made to it cannot be rolled back. >Note that in the attached data, two sets of 48 rows flushed implicitly >prior to the commit, plus one set of 4 rows flushed at the commit, >producing the 100 rows that you expected. Similarly, the second set of 100 >rows also has two sets of 48 rows flushed implicitly prior to the rollback, >and since the table is unlogged, these 96 rows are effectively committed. > >At the rollback statement, the last 4 rows in the insert cursor are deleted, >and the transaction is rolled back, but the unlogged temp table rows cannot >be rolled back. Then when the program fetches the data, there are 196 rows >in the temp table. > >I'm not sure whether I'd categorize this as a bug. Obviously, the code could >be modified to create the temp table with a log (omitting the WITH NO LOG >clause), >and then no lines are reported in the 'This line will be rolled back' section >because none of them are committed to the database. Ideally, I suppose, >you'd >be able to elect whether the temp table should be logged or unlogged. > >For the really alert, you should be able to spot that the first program >was run with I4GL-RDS; the modified version without a WITH NO LOG clause >had to be handled in C (well, it would have required a patch to the p-code >file to alter the p-code behaviour, and I couldn't be bothered). > >Yours, >Jonathan Leffler One solution - if your lines are sorted, use the "ORDER EXTERNAL BY linevar" (couldn't bring myself to use a variable called "line"). Using the external, the REPORT section does not need to buffer the lines until the "FINISH REPORT" statement, and you get your expected results. Sean Bradley