How do I prevent a commit from being executed on a connection?

[Breck_Carter]

At execution time, at some point in the SQL code, I want to issue some kind of directive telling SQL Anywhere to throw an exception if there is any attempt to issue a commit, either explicitly or as a side-effect of some other statement.

Then, at some later point in execution, I want to release the prohibition.

My immediate motivation is to use a large body of SQL code (procedures and what-not) in a transactional context, where the current code cares not one whit about transaction design. Hunting for the sassen-frassen-fricken-fracken commits is taking up too much of the my time, whereas an exception (or two, or ten) would make life easier.

My long-term motivation is to be able to exhort "No commits!" and know that the code (however badly maintained in the future) will either obey the exhortation or fail.

(I suppose I could move all the code into a CREATE TRIGGER... 🙂

Silent commits are deadly for data integrity.

So... can I do it now, or is it a product suggestion?

[Breck_Carter]

That's a JOKE, moving stuff into a CREATE TRIGGER on a dummy table so that a commit would violate the rule against commits in triggers 🙂

[Breck_Carter]

Haven't used the debugger for YEARS... the V16 edition works VERY well, as easy to use as the Application Profiler is difficult.

Except for the part about ... endlessly ... stepping ... through ... code ... looking ... for ... that ... steenking ... commit.

That's just the nature of the problem, of course.

The problem? Some DROP statements that did cleanup after a CREATE SERVER was no longer needed. The CREATE SERVER logic had long ago been removed, and the DROP statements were wrapped in BEGIN blocks with EXCEPTION handlers that said "never mind if the DROP fails" which it would, of course... but apparently even a failing DROP does a steenking commit.

[Breck_Carter]

The question is still OPEN... I still want to be able exhort "No commits!"

[VolkerBarth]

A few ideas - I'm not sure they fit your requirements:

• Add a few statements that cause a FK violation at the beginning of your code and set WAIT_FOR_COMMIT to ON - that should force any following commit to fail and could be handled by a general exception handler.
• Add checks within the code to ensure @@TRANCOUNT > 0 and otherwise, raise an exception.
• Have a monitoring connection that checks whether your worker connection's property "uncommitops" goes from > 0 to 0...

[Breck_Carter]

oooooo... I like the WAIT_FOR_COMMIT idea... that would have saved me an HOUR with the debugger!