• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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!!

  • 2
1 Solution
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]
sp_procxmode [procedure], anymode

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.
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..
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


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?
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!

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now