SET CHAINED OFF problem

[Anand Hirekatur]

Hi,
I am getting the following error when I try to execute a stored procedure in JDBC code :

This is the relevant piece of code:

   cStmt = conn.prepareCall( "{ call getResults(  ? ) }" );
   cStmt.setInt(1, surID);
   rs = cStmt.executeQuery();

everything upto this point is ok i.e. I have the connection etc

The error is :
Stored procedure 'dbo.getResults' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

To overcome this I put the following piece of code before calling the above SP:

CallableStatement aStmt = conn.prepareCall( "{ call sp_procxmode getResults, anymode }" );
aStmt.execute();

This gives the error
The specified object 'getResults' is not found in database 'abc'.  Check sysobjects to make sure you own the object.

Obviously I do not own the object since I am just a user

So - how do I call 'set chained off' from within jdbc code?
Thanks,
Anand

[Anand Hirekatur]

Also I am using Connection Pooling. If I run this code without connection pooling i.e I load the driver etc this code runs fine.
Thanks,
Anand

[Nick Elson]

This is simple an ASE procedure attribute problem. All stored procedures are flagged with a Procedure Execute Mode

which is unchainced by default. So when you create you stored procedures they are flagged in such a way that

will throw this errorr if you are running in chained mode.  

 

A lot of our products managed transactions using chained mode and that can be a problem with some

sproc designs.

 

By running

        sp_procxmode <proc_name>, "chained"

or

        sp_procxmode <proc_name>, "anymode"

 

you can avoid this message.  I usually suggest people use "anymode"

(because "chained" can break other existing applicatons).  

 

CAVEAT: Any mode change may impact how the procedure needs to be coded.

 

 

"Anand Hirekatur" <anand.h...@ucop.edu> wrote in message news:3B2572AC...@ucop.edu...

[Nick Elson]

Obviously I didn't read through that whole thread.

 

You should probably just run that from OpenClient ISQL

once and not try to do it from within a middle tier design.

This execute mode is a permanent flag (unless you change

it later) and you should only need to set it once from the

ASE management tools (ISQL or Sybase Central).

 

If you really must do this from withing your Java code then

make sure you specify the database and owner on that

stored procedure; as in:

 

CallableStatement aStmt = conn.prepareCall( "{ call sp_procxmode abc.dbo.getResults, anymode }" );

It is just possible the account used in the connection pool is either

connecting to the wrong database (abc), or has been switched to

using a different databasse (on a different connection use) or

you cannot see dbo objects (for some unknown strange reasons).

 

Watch out for connection pools and playing with ASE things like

connection options, permissions, execute/transaction modes

set users, setting rols and switching databases. Then next

user of the connection can get a nasty surprise.  So if you

expect to be exposed to these variables, you may want to

enforce 'your' own defaults on every use. 

"Nick Elson" <nick....@sybase.com> wrote in message news:C76zvav...@forums.sybase.com...

[Anand Hirekatur]

Hi Nick,
Thanks for your help. I tried what you suggested i.e.CallableStatement aStmt = conn.prepareCall( "{ call sp_procxmode abc.dbo.getResults, anymode }" );
but got the following error:
Incorrect syntax near '.'.I know for sure that I am not connecting to the wrong db etc.
I cannot run the above statement in SQLAdvantage - I get the same incorrect syntax error.
So I removed abc.dbo and ran it and I get this error:

The specified object 'getResults' is not found in database 'abc'.  Check sysobjects to make sure you own the object.

Also when I run the actual SP by not using Connection Pooling but by using DriverManager and loading the driver class it executes just fine. We are using a J2EE based app server.

Thanks,
Anand

Nick Elson wrote:

Obviously I didn't read through that whole thread. You should probably just run that from OpenClient ISQLonce and not try to do it from within a middle tier design.This execute mode is a permanent flag (unless you changeit later) and you should only need to set it once from theASE management tools (ISQL or Sybase Central). If you really must do this from withing your Java code thenmake sure you specify the database and owner on thatstored procedure; as in: CallableStatement aStmt = conn.prepareCall( "{ call sp_procxmode abc.dbo.getResults, anymode }" );It is just possible the account used in the connection pool is eitherconnecting to the wrong database (abc), or has been switched tousing a different databasse (on a different connection use) oryou cannot see dbo objects (for some unknown strange reasons). Watch out for connection pools and playing with ASE things likeconnection options, permissions, execute/transaction modesset users, setting rols and switching databases. Then nextuser of the connection can get a nasty surprise.  So if youexpect to be exposed to these variables, you may want toenforce 'your' own defaults on every use.

"Nick Elson" <nick....@sybase.com> wrote in message news:C76zvav...@forums.sybase.com...This is simple an ASE procedure attribute problem. All stored procedures are flagged with a Procedure Execute Modewhich is unchainced by default. So when you create you stored procedures they are flagged in such a way thatwill throw this errorr if you are running in chained mode. A lot of our products managed transactions using chained mode and that can be a problem with somesproc designs. By running        sp_procxmode <proc_name>, "chained"or        sp_procxmode <proc_name>, "anymode" you can avoid this message.  I usually suggest people use "anymode"(because "chained" can break other existing applicatons). CAVEAT: Any mode change may impact how the procedure needs to be coded.  

"Anand Hirekatur" <anand.h...@ucop.edu> wrote in message news:3B2572AC...@ucop.edu...Also I am using Connection Pooling. If I run this code without connection pooling i.e I load the driver etc this code runs fine.

[Nick Elson]

That's actually the correct behaviour, sp_procxmode needs to be executed from the

database that contains the database so the

 

    database<dot>own<dot>

 

suggestion was incorrect. That was my oversight on that - a subquery in that

procxmode procedure looks for your stored procedure by name (which is going

to be stored in sysobjects undecorated). 

 

Try this instead (from isql or SQL Advantage)

        use abc

        go

        sp_procxmode getResults, anymode

        go 

 

That should work. You'll need to be the creator of that stored procedure

or the DBO account, or sa.  If not, get you DBA to do this for you.

 

The connection pool is probably connecting with another account than

what you are expecting.  It is setting the chain mode (and that alone

may be enough to break sp_procxmode).

 

A quick look into sp_procxmode also reveals itself does transaction

processing so it probably should be ran from a connection cache

(especially inside a EJB container managed transaction).

 

The problem may just be that your account only has execute permissions

on that stored procedure but just do not have sufficient permissions to be

it's owner or  equivalent to owner.  So again your DBA will probably need

to do this for you.

 

"Anand Hirekatur" <anand.h...@ucop.edu> wrote in message news:3B266514...@ucop.edu...

[lance.a...@sun.com]

The issue you are encountering is that your Connection Pooling
appears to be trying to coodordinate your Transaction processing
which would be common in a J2EE environment.

From a traditional JDBC app using jConnect toggling
Connection.setAutoCommit()
will result in the backend receiving set chained on/off.
from jConnect.

The sprocs will pick up their mode by the state of the session
that they were created in. As Nick points out, in many cases
you can simply have the dba issue sp_procxmode to change under
what scenarios the sproc can run.

Note that occasionally this might require logic changes to
the sproc to get the desired results.

Lance