on 2011 Jul 08 4:48 PM
What am I missing? What is "atomic" in the following code?
12.0.1.3298
SQLCODE = -267
SQLSTATE = 42W28
ERRORMSG() = COMMIT/ROLLBACK not allowed within atomic operation
CREATE PROCEDURE p() BEGIN NOT ATOMIC DECLARE @sqlcode INTEGER; DECLARE @sqlstate VARCHAR ( 5 ); DECLARE @errormsg VARCHAR ( 32767 ); BEGIN NOT ATOMIC -- handle possible exception COMMIT; EXCEPTION WHEN OTHERS THEN SELECT SQLCODE, SQLSTATE, ERRORMSG() INTO @sqlcode, @sqlstate, @errormsg; MESSAGE STRING ( 'EXCEPTION: @@VERSION = ', @@VERSION, ', SQLCODE = ', @sqlcode, ', SQLSTATE = ', @sqlstate, ', ERRORMSG() = ', @errormsg ) TO CLIENT; END; SELECT 'Hello, world' AS c; END; --------------------------------------------------------------------------- BEGIN NOT ATOMIC DECLARE @row_count INTEGER; DECLARE LOCAL TEMPORARY TABLE dsn_list ( dsn_entry LONG VARCHAR ) NOT TRANSACTIONAL; INSERT dsn_list SELECT LIST ( p.c ) FROM p(); END; EXCEPTION: @@VERSION = 12.0.1.3298, SQLCODE = -267, SQLSTATE = 42W28, ERRORMSG() = COMMIT/ROLLBACK not allowed within atomic operation
Request clarification before answering.
It is because the commit is being executed during the insert (not just insert select as I originally supposed) which is atomic.
The closest thing I found in the docs is:
COMMIT and ROLLBACK are not allowed within any atomic statement. Note that triggers are fired due to an INSERT, UPDATE, or DELETE which are atomic statements. COMMIT and ROLLBACK are not allowed in a trigger or in any procedures called by a trigger.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
oops, I did it again... accidentally deleted John Smirnios' comment. John had said it was the INSERT, not the SELECT, that made it an atomic operation.
Suggestion: Either allow undo of comment deletion, or do not allow the comment delete button to be pressed by anyone who has not yet had a second cup of coffee... whichever is easier to implement.
Of course I was sure I wanted to delete my own comment, so I could re-enter it in a different place in the hierarchy. So a cosmetic change to the dialog box text would not have helped.
It's a GUI thing... when there are a bunch of short comments the screen is full of tiny icons, and most comments have one delete icon right above it and another one right below it. Nothing wrong with that BUT mistakes will be made and an undo feature lets you leave the GUI alone while at the same time allowing the user to work efficiently AND safely.
If you don't realize you have clicked on the wrong delete icon, a confirmation box isn't going to stop you... unless, perhaps, that confirmation box contains the entire text of the comment you are about to delete. Or a big warning that "THIS NOT YOUR OWN COMMENT, THIS IS SOMEONE ELSE'S COMMENT, YOU BLOODY MORON!".
Or both.
Even then... there should still be an undo. ESPECIALLY for a delete operation. A lot of enterprise business applications don't even allow deletes, with good reason... disk space is cheap, lawsuits and FBI investigations not so much.
LOL... I have those in the Foxhound build process: "Before you recreate the Foxhound development database, have you looked at the runtime exception history table? Maybe there is something that needs fixing..."
...repeated five times. Sometimes, around the third or fourth "OK" click, I finally say "Aw, nuts, ok, let me look."
What it SHOULD say is, "There are N un-viewed rows in the exception table. You cannot do a build until you have looked at them all."
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.