Re: commit work not working?

jda wrote: > 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. Your programmer needs to get and use the Perl DBS/DBI module for Informix and have Perl talk directly to the database server rather than piping a script to dbaccess. Then he/she will have program control of error detection and can make decisions about commit or rollback. Perl DBD/DBI Informix is available on CPAN. Art S. Kagel > 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 >