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?

asked 18 May '13, 13:59

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

edited 21 May '13, 03:36

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654

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

(18 May '13, 14:22) 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.

(18 May '13, 14:28) Breck Carter

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

(18 May '13, 14:28) Breck Carter

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...
permanent link

answered 19 May '13, 17:33

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

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

(19 May '13, 18:04) Breck Carter
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:

×101
×47

question asked: 18 May '13, 13:59

question was seen: 649 times

last updated: 21 May '13, 03:36