AUTONOMOUS_TRANSACTION in SQL Anywhere?

[Former Member]

Hello

I wonder if there is something equivalent to oracle's AUTONOMOUS_TRANSACTION Pragma in SQL Anywhere? I found this work around: http://sqlanywhere-forum.sap.com/questions/12/what-is-the-best-way-to-simulate-oracle-autonomous-tra... but it was posted awhile ago so perhaps there is some new functionality I can use instead?

note: Oracle AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

[VolkerBarth]

I'm not aware of new functionality, so I guess the answers from the other FAQ are still valid.

FWIW: In case you may just need a subprogram to be able to rollback its own work (but nested in an outer program), you can use SAVEPOINTs to do so, i.e. a subprogram may save or rollback its own work but the outer program will still have to commit the whole transaction. Apparently, that's not the same as autonomous transactions.

[Former Member]

I need the opposite, I subprogram which can commit although the main transaction does rollback...

there is no other way (then described in the link above)?

[reimer_pods]

A possible solution might be to trigger an event to execute the subprogram. The event would be running in it's own connection and could commit independently. Potential drawback: execution order won't be predictable.

[VolkerBarth]

@Reimer: Yes, that's the suggestion from the cited FAQ - possibly with the WAITFOR AFTER MESSAGE BREAK / MESSAGE TO CLIENT FOR CONNECTION ... "inter-connection-communication" facilities to synchronize between the starting connection and the event.

@M G: I don' think there is another way.