Re: Report lines can be rolled back?

This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. ---559023410-1483920592-902796525=:11541 Content-Type: TEXT/PLAIN; charset=US-ASCII >Date: Thu, 06 Aug 1998 13:22:15 -0400 >From: jhatfiel <jhatfiel@bju.edu> >Subject: 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 (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.59 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn >--------- BEGIN PROGRAM LISTING >DATABASE db >MAIN > DEFINE i INTEGER > > START REPORT rbreport TO "rbreport.out" > > BEGIN WORK > FOR i=1 TO 100 > OUTPUT TO REPORT rbreport("This line will be committed") > END FOR > COMMIT WORK > > BEGIN WORK > FOR i=1 TO 100 > OUTPUT TO REPORT rbreport("This line will be rolled back") > END FOR > ROLLBACK WORK > > > FINISH REPORT rbreport > >END MAIN > > >REPORT rbreport(line) > DEFINE line CHAR(80), > counter1, counter2 INTEGER > > ORDER BY line > > FORMAT > FIRST PAGE HEADER > LET counter1 = 0 > LET counter2 = 0 > > ON EVERY ROW > IF line = "This line will be committed" THEN > LET counter1 = counter1 + 1 > ELSE > LET counter2 = counter2 + 1 > END IF > > AFTER GROUP OF line > IF line = "This line will be committed" THEN > PRINT GROUP COUNT(*) USING " ####", counter1 USING " ####", " ", > line CLIPPED > ELSE > PRINT GROUP COUNT(*) USING " ####", counter2 USING " ####", " ", > line CLIPPED > END IF >END REPORT ---559023410-1483920592-902796525=:11541 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="sqliprint.out" Content-Transfer-Encoding: BASE64 Content-ID: <Pine.GSO.3.96.980810174845.11541j@osiris.informix.com> Content-Description: SQLIPRINT output: about 50% of lines omitted because very boring SQLIDBG Version 1 S->C (4) Time: 1998-08-10 17:15:31.87154 SQ_VERSION SQ_EOT C->S (14) Time: 1998-08-10 17:15:31.91997 SQ_VERSION "7.24.UC1" [8] SQ_EOT C->S (370) Time: 1998-08-10 17:15:31.92702 SQ_INFO INFO_ENV Name Length = 6 Value Length = 282 "DBTEMP"="/tmp" "SHELL"="/usr/bin/ksh" "TZ"="US/Pacific" "DBPATH"="." "PATH"="/work4/informix/D4GL-2.00.UC1-4JS/bin:/work4/informix/D4GL-2.00.UC1-4JS/bin:/usr/informix/7.24.UC1/bin:.:/work/jleffler/bin:/u/jleffler/bin:/usr/informix/7.24.UC1/bin:/usr/atria/bin:/usr/perl/v5.004/bin:/usr/gnu/bin:/usr/bin:/usr/ccs/bin:/usr/dt/bin:/usr/openwin/bin:/usr/local/bin" INFO_DONE SQ_EOT S->C (2) Time: 1998-08-10 17:15:31.94287 SQ_EOT C->S (30) Time: 1998-08-10 17:15:31.94385 SQ_PREPARE # values: 0 CMD.....: "database "logged"" [17] SQ_NDESCRIBE SQ_WANTDONE SQ_EOT S->C (44) Time: 1998-08-10 17:15:32.29088 SQ_DESCRIBE Stmt Type...........: 1 Server Stmt Id......: 0 Estimated Cost......: 0 Size of output tuple: 0 # output fields.....: 0 Size of string table: 0 SQ_DONE Warning..: 0x10 # rows...: 0 rowid....: 0 serial id: 0 SQ_COST estimated #rows: 1 estimated I/O..: 1 SQ_EOT C->S (8) Time: 1998-08-10 17:15:32.29173 SQ_ID 0 SQ_EXECUTE SQ_EOT S->C (28) Time: 1998-08-10 17:15:33.15959 SQ_DONE Warning..: 0x15 # rows...: 0