What`s the best way to simulate Oracle Autonomous Transactions on SQLA? I`m think publishing an webservice on database and create a stored funcion/procedure to consume it. That`s the better option? |
In this case I think that my solution (publish and consume webservices in database) is the best option. |
To expand on what Mark said... when an event is executed (triggered), it gets a separate connection, which is why it can commit separately. It also means the event runs asynchronously, unlike a procedure call which runs synchronously. In other words, events run in "fire and forget" mode. Like Stan Lee said, with great power there must also come — great responsibility! Events are wonderful things. Manually-executed events (as opposed to regularly scheduled ones) are even more wonderful. The great power is "commits separately". The great responsibility is solving the "bit more difficult" problem of which Mark speaks. I can personally testify that the effort is worth it... I have developed a commercial multi-threaded application which runs entirely inside SQL Anywhere, based on both scheduled and manually-executed events: the Foxhound database monitor. (that's not intended as a pitch for Foxhound, but for SQL Anywhere, without which I would have been floundering in C ten years from now) |
Just to add a v17 feature to that real old question: Based on Mark's excellent suggestion of a separate event connection, with v17 you could use a semaphore object to synchronize the current connection and the event connection, say something like the following in the current connection:
and within the event handler, you would finally call
That will then resume the waiting connection. (Note, in contrast to a synchronization via WAITFOR AFTER MESSAGE BREAK / MESSAGE ... TO CONNECTION, you cannot exchange state information here, but you could also use a global temp table shared by all or the like to do so.) FWIW, with v17 you could also use database-scope variables (CREATE DATABASE VARIABLE...) to share information between connections.
(12 Apr '19, 13:27)
Volker Barth
|