Sybase Java question

Hi all,

would appreciate your help..

I have a Sybase stored procedure which is being called from JDBC.

Having some issues..

I have posted the same question in Java forum as well..

please if you can help!!

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21826668.html
hunky_shAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
The error "set chained command not found for multi statement transaction" says to me that this is a problem with the stored procedure "transaction mode".

ANSI defines two different transaction modes. In Sybase world they're called "chained" and "unchained".

Unchained is the Sybase default. Here each statement is considered to be in its own transaction. You get no explicit transaction management unless you wrap statements in BEGIN TRAN and COMMIT TRAN. Every BEGIN must be matched by a COMMIT and vice versa.

However the ANSI standard is what Sybase calls chained mode. Here you are always in a transaction. On connection to the dataserver, you implicitly issue a BEGIN TRAN. You don't get any commits until you issue and explicit COMMIT TRAN (which won't have a matching BEGIN since the BEGIN was implicit). You automatically issue a new BEGIN TRAN after any COMMIT or ROLLBACK.

Stored procedures in Sybase are stamped with the transaction mode of the session they were created in. Since Sybase uses unchained mode by default, the proc will also be marked as "I execute only in unchained mode".

However since chained mode is the ANSI standard, I am guessing that Java and JDBC connect in chained mode by default. Sybase *will* allow you to execute across modes, but it raises a warning, and perhaps that warning is being interpreted as an error by the JDBC layer.

There's an easy way to check this. Try running in Sybase:

use [database]
go
sp_procxmode [procedure], anymode
go

Note that you'll now have to give some thought to transaction management - your JDBC might be expecting one mode while Sybase is working with another. This could leave you with (effectively) BEGIN TRANs without matching COMMIT TRANs. Be careful.
0
hunky_shAuthor Commented:
I have understood some of it, but not all..
okay, few questions:

1). ANSI?? What does it stands for? and what does it mean.. i am assuming this is a entity who decides on some standards.. but standards on what ? sybase? or java? or everything?

2). You said, SYBASE default is unchained..

so looks like this procedure is wrong first of all..
because
it executes stored procedure insertCharge/updateCharge or run the sql to logically delete the Charge depending on value of operation..
and then runs the select to return chargeId..

running a last select and running the stored proc(or sql depending on value of operation) should be a separate transaction.. and since procedure in unchained.. this select should bring wrong results.. but this is returning the correct results... why?

3). So basically this stored proc should be changed to have

BEGIN TRAN
AND
COMMIT TRAN

Correct? where should I add this?

4). I am confused between JDBC and Sybase..
there is a way to manage transactions in Java as well as in Sybase..

Now where should we manage the transactions? Do we have to manage at both places?

Managing transactions at both places is a pain..

any good ideas on this?

5). I see by running sp_procxmode chargeMaintanance that this is unchained transaction..

i am running this from a one JDBC call..

so basically, all i need is -- just change the transaction mode to chained .. or anymode.. and it should be fine ??

6). what is a anymode?
chained mode -- basically means that transaction is chained correct? just changing the mode basically will convert the stored proc to chained.. why do we still need BEGIN TRAN and COMMIT TRAN?

unchained mode - is stored proc is marked as unchained, but we still BEGIN TRAN and COMMIT TRAN -- what happens?

anymode -- what does this mean?
0
Joe WoodhousePrincipal ConsultantCommented:
1) ANSI sets the SQL standard.

2) No, unchained mode means treat each statement as its own transaction. So the insert/update/delete and the select are being run in separate transactions, as you want.

3) It isn't as simple as wrapping the proc in BEGIN and COMMIT TRAN. Firstly, you said above that you want them in separate transactions! Secondly, this would assume you never did any transaction management anywhere else. Nested transactions get complicated, but briefly - nothing commits until the outermost COMMIT, but any ROLLBACK rolls back all levels of nested transactions.

4) Correct, you can handle transactions in the calling environment, or in the stored procedure. It gets complicated when you try to do it in both places. Pick one.

5) Yep.

6) "Anymode" means "be compatible with both chained & unchained modes, and assume the developer is correctly taking care of transactions".

Good luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.