|
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
|
|
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. ooooo... gotta check that out.... I feel a blog posting coming on.... who'da thunk it? :)
(08 Jul '11, 16:48)
Breck Carter
Comment Text Removed
1
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.
(09 Jul '11, 05:07)
Breck Carter
Replies hidden
It does say "Are you sure?" so if you're not sure... Perhaps I can change the confirmation to say "Are you sure you want to delete this comment?" to be more specific.
(09 Jul '11, 17:03)
Graeme Perrow
maybe just an early morning/late night mode that says 'Are you really sure' just after the 'Are you sure' :)
(09 Jul '11, 17:05)
Daz Liquid
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.
(10 Jul '11, 05:47)
Breck Carter
...whatever you do, please don't change the GUI. It's fine. I have messed up twice now, it is possible that I have learned my lesson.
(10 Jul '11, 05:50)
Breck Carter
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."
(10 Jul '11, 05:55)
Breck Carter
|
