Re: Informix equivalent of savepoint

JULIEJ wrote: > In Informix is there an equivalent command for the Oracle > savepoint command? > or > > sqlstmt1.. > sqlstmt2 > sqlstmt3 > ..... > ..... > ..... > ..... > sqlstmt10 > > After sqlstmt10, I want to rollback upto sqlstmt5. How would I do it? > > TIA, > Julie Joseph No, Informix does not exactly offer such a feature to the user community. Now why did I word my response in such a strange way? Because the engine uses a savepoint feature internally. Suppose I run transaction like this: BEGIN WORK SQLstmt1 SQLstmt2 execute procedure yadayada()SQLstmt3 COMMIT WORK The procedure may effect 770 updates. However, it encountered an error on update 766. All previous 765 updates in that procedure get rolled back by the engine but THE TRANSACTION IS STILL INTACT! The programmer may choose to continue and commit the transaction. The operations effected by SQLstmt1 and SQLstmt2 are still intact. This is also true for ANY mass update/delete in a transaction (eg. delete from emps where title != "boss" ) that encounters an error; allupdates effected by the statement are rolled back but changes effected by previous statemens in the transaction are still in effect. The transaction rolls on (unless you roll it back yourself). This means that while you are in a transaction, EVERY statement you issue from a program marks a savepoint. The engine will use that savepoint to roll back to in case of error. However, YOU, the dear user, cannot access this savepoint information directly. :-[ On the other hand, you should be able to use a stored procedure for any set of operations that require a savepoint within the transaction. Just don't issue a "rollback work" command within the procedure. -- -- Jake (In pursuit of undomesticated aquatic avians) +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+