Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

COMMIT/ROLLBACK not allowed within atomic operation

3 posts in General Discussion Last posting was on 2005-11-10 07:59:24.0Z
Sowmini Ramana Posted on 2005-11-08 19:22:35.0Z
Sender: 24cc.43710525.846930886@sybase.com
From: Sowmini Ramana
Newsgroups: ianywhere.public.general
Subject: COMMIT/ROLLBACK not allowed within atomic operation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <437107a8.2554.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 8 Nov 2005 12:16:40 -0700, 10.22.241.42
Lines: 17
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 8 Nov 2005 12:16:47 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 8 Nov 2005 12:22:35 -0700
X-Trace: forums-1-dub 1131481355 10.22.108.75 (8 Nov 2005 12:22:35 -0700)
X-Original-Trace: 8 Nov 2005 12:22:35 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4917
Article PK: 31480

Hi,
I execute a procedure from JSP, the stored procedure has an
EXECUTE IMMEDIATE for an insert Statement. The procedure
runs as expected from Sybase central, but gives following
error message when called from JSP.

ASA Error -267: COMMIT/ROLLBACK not allowed within atomic
operation

Any idea what I can do to get rid of this error.

Version details below
Adaptive Server Anywhere 9.0.2
Sybase Central 4.3.0

Thanks,
Sowmini


Nick Elson Posted on 2005-11-09 15:29:15.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <437107a8.2554.1681692777@sybase.com>
Subject: Re: COMMIT/ROLLBACK not allowed within atomic operation
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <4372227a$1@forums-2-dub>
X-Original-Trace: 9 Nov 2005 08:23:22 -0700, nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 9 Nov 2005 08:23:23 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 9 Nov 2005 08:29:15 -0700
X-Trace: forums-1-dub 1131553755 10.22.108.75 (9 Nov 2005 08:29:15 -0700)
X-Original-Trace: 9 Nov 2005 08:29:15 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4931
Article PK: 8602

I usually only see this when someone codes a
COMMIT or ROLLBACK inside a trigger.
Any DDL inside that trigger can cause that error too.

But I have no idea why it only occurs when called
from a JSP and not via EXECUTE IMMEDIATE unless
the JSP is providing values that cause conditional
logic to fire.

I double the stored procedure is the major factor here.
If you can identify the specific conditions, you should
get the same behaviour from just the INSERT statement
directly.


[Trigger actions, and RI cascade actions can cause other
update/delete operations to fire in the background ... so you
may have to look for triggers on those tables too]

<Sowmini Ramana> wrote in message
news:437107a8.2554.1681692777@sybase.com...
> Hi,
> I execute a procedure from JSP, the stored procedure has an
> EXECUTE IMMEDIATE for an insert Statement. The procedure
> runs as expected from Sybase central, but gives following
> error message when called from JSP.
>
> ASA Error -267: COMMIT/ROLLBACK not allowed within atomic
> operation
>
> Any idea what I can do to get rid of this error.
>
> Version details below
> Adaptive Server Anywhere 9.0.2
> Sybase Central 4.3.0
>
> Thanks,
> Sowmini


Greg Fenton Posted on 2005-11-10 07:59:24.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: COMMIT/ROLLBACK not allowed within atomic operation
References: <437107a8.2554.1681692777@sybase.com> <4372227a$1@forums-2-dub>
In-Reply-To: <4372227a$1@forums-2-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.50.0.15
X-Original-NNTP-Posting-Host: 10.50.0.15
Message-ID: <43730bec@forums-1-dub>
Date: 10 Nov 2005 00:59:24 -0700
X-Trace: forums-1-dub 1131613164 10.50.0.15 (10 Nov 2005 00:59:24 -0700)
X-Original-Trace: 10 Nov 2005 00:59:24 -0700, 10.50.0.15
Lines: 29
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4934
Article PK: 8605


Nick Elson wrote:
>
> But I have no idea why it only occurs when called
> from a JSP and not via EXECUTE IMMEDIATE unless
> the JSP is providing values that cause conditional
> logic to fire.

This is just a guess, but maybe it has to do with the fact that the JSP
is using jConnect (i.e. TDS/OpenClient) rather than jODBC which Sybase
Central uses (I'm gathering this from Sowmini's previous thread).

Can you try using the iAnywhere JDBC driver to see if the behaviour
changes? Alternatively you might try overriding some of the jConnect
default connection behaviours to see if the behaviour comes inline with
a "native" SQLAnywhere connection type.

As I posted in the previous thread, you might try reading:

http://www.ianywhere.com/developer/technotes/sas_jdbc_driver.html

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/