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-transactions-in-sql-anywhere 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.

asked 30 Jan '14, 03:16

M%20G's gravatar image

M G
629253044
accept rate: 42%


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.

permanent link

answered 30 Jan '14, 04:00

Volker%20Barth's gravatar image

Volker Barth
30.3k301452660
accept rate: 32%

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)?

(31 Jan '14, 01:33) M G

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.

(31 Jan '14, 06:22) Reimer Pods
Replies hidden

@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.

(31 Jan '14, 08:21) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×198

question asked: 30 Jan '14, 03:16

question was seen: 959 times

last updated: 31 Jan '14, 08:21