Sybase Java question

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.

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?