commit work not working?

IDS 9.40.HC3 on HP-UX 11i (B.11.11) server We are having a strange situation with begin/commit work, the commit is taking place even though an error occurs between the begin & commit statements. We have a begin work then a load followed by an insert then the commit work. The load has a duplicate key error, but the second insert is being committed. So looks something like this in pseudo code Begin work; load from fname insert tableA; insert tableB; Commit work; The developer was expecting that when the insert into tableA failed because of the duplicate key the insert into tableB would be rolled back and not committed. So he came to me and the way I read the manual it should have rolled back the insert into tableB. Even when he copies the 4 lines of code into dbaccess or isql and runs it, the same thing happens; we get no tableA record but do get a tableB record. We expected neither to be there after the commit work. What are we mis-understanding? John

HI, John, I would say, You might not rollback the transaction and set isolation to dirty read. If you rollback the transaction or NOT set to dirty read, it should be OK. Frank jda wrote: >IDS 9.40.HC3 on HP-UX 11i (B.11.11) server > >We are having a strange situation with begin/commit work, the commit is >taking place even though an error occurs between the begin & commit >statements. We have a begin work then a load followed by an insert >then the commit work. The load has a duplicate key error, but the >second insert is being committed. > >So looks something like this in pseudo code > >Begin work; > load from fname insert tableA;> insert tableB; >Commit work; > >The developer was expecting that when the insert into tableA failed >because of the duplicate key the insert into tableB would be rolled >back and not committed. So he came to me and the way I read the manual >it should have rolled back the insert into tableB. > >Even when he copies the 4 lines of code into dbaccess or isql and runs >it, the same thing happens; we get no tableA record but do get a tableB >record. We expected neither to be there after the commit work. > >What are we mis-understanding? > >John > >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696

> Begin work; > load from fname insert tableA;> insert tableB; > Commit work; where is the error check??? i would expect: Begin work; load from fname insert tableA;if (error) rollback else insert tableB; if (error) rollback else Commit work; end if end if in dbaccess there is an env var (sorry dono this one at the top of my head check the manual it should be in 9.4 though ) which checks if there is an error in the sql file with this on set dbaccess yourdb <<! Begin work; load from fname insert tableA; insert tableB; Commit work; ! should give you want you want, if there is an error in load from... dbaccess bails out. Superboer.

Superboer wrote: >> Begin work; >> load from fname insert tableA;>> insert tableB; >> Commit work; > > where is the error check??? > > i would expect: > > > Begin work; > load from fname insert tableA;> if (error) > rollback > else > insert tableB; > if (error) > rollback > else > Commit work; > end if > end if Why not: BEGIN WORK; LOAD FROM fname INSERT INTO TableA;IF (no error) THEN INSERT INTO TableB;END IF IF (no error) THEN COMMIT; ELSE ROLLBACK; END IF; > in dbaccess there is an env var (sorry dono this one at the top of my > head check the manual it should be in 9.4 though ) > which checks if there is an error in the sql file export DBACCNOIGN=1 Or use SQLCMD instead, which gives you selective control over whether to ignore errors or not. > with this on set > > dbaccess yourdb <<! > Begin work; > load from fname insert tableA;> insert tableB; > Commit work; > ! > > should give you want you want, if there is an error in load from... > dbaccess bails out. Or if there's an error from the INSERT INTO TableB; :-) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Thanks everyone for your replies, I passed them on to the developer and here is his reply. It seems to me a difference of interpretation of what the manuals say on begin-commit work and/or what actually happens in IDS 9.4. Because he is using straight sql, I'm not sure how to check for errors, so asking the for help and passing on his email to me. John Thanks for all the replies but I still need more help. First here is a section of the "IBM Informix Guide to SQL: Syntax" for 9.4 page 2-84 -------------------------- begin snipit ------------------------ Example of BEGIN WORK The following code fragment shows how you might place statements within a transaction. The transaction is made up of the statements that occur between the BEGIN WORK and COMMIT WORK statements. The transaction locks the stock table (LOCK TABLE), updates rows in the stock table (UPDATE), deletes rows from the stock table (DELETE), and inserts a row into the manufact table (INSERT). BEGIN WORK; LOCK TABLE stock; UPDATE stock SET unit_price = unit_price * 1.10 WHERE manu_code = 'KAR'; DELETE FROM stock WHERE description = 'baseball bat'; INSERT INTO manufact (manu_code, manu_name, lead_time) VALUES ('LYM', 'LYMAN', 14); COMMIT WORK; The database server must perform this sequence of operations either completely or not at all. When you include all of these operations within a single transaction, the database server guarantees that all the statements are completely and perfectly committed to disk, or else the database is restored to the same state that it was in before the transaction began. -------------------------- end snipit ------------------------ The documentation implies that I don't need to decide if I should rollback or commit, it implies that the database server will do that for me (it actually guarantees it). So why doesn't the database server automatically rollback when an error occurs? Second since the documentation is obviously wrong I will need to find a work around. The code that I am running is passed to dbaccess from perl so dbaccess is not running interactively. It is very simple code (4 statements) and is standard SQL (not SPL) begin work; load from file insert tableA; insert tableB; commit work; Can anyone tell me how to do the error checking in standard SQL? I suppose from Perl I could break it apart into multiple SQLs passed to dbaccessload from file insert tableA;check for error if no error then insert tableB check for error My problem then becomes, if the insert on tableB fails how do I know what rows to remove from tableA? Do I have to parse the file attempting to pull out the key fields so that I can remove the correct rows from the table? There surely has to be an easier way to do this. Any suggestions? TIA Denis