RE: commit work not working?

I'm not sure I agree that the documentation implies that it will automatically roll back. It will do what it's told, and if the client program (dbaccess) tells it to commit what it's done, then it does. I believe there is a new flag (-a, I think) in IDS 10 (possibly 9.4) for dbaccess that causes it to abort on error to avoid this sort of behavior. You could also use Jonathan Leffler's sqlcmd, which aborts by default (it's available on the IIUG software repository at www.iiug.org). Better, however, would be to use transactions directly inside the perl script. I assume that he's using the Perl DBI. You can work with transactions using this tool as well. For instance: $dbh = DBI->connect(...); $dbh->do("begin work"); ... Do some work ... $dbh->do("commit"); This way you can detect errors via the DBI mechanisms, and roll back when appropriate. It will roll back the entire transaction, including the inserts into tableA and the inserts into tableB. DC > -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of jda > Sent: Friday, July 21, 2006 8:23 AM > To: informix-list@iiug.org > Subject: Re: commit work not working? > > 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 > dbaccess> load 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 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list