Sybase Java question

Posted on 2006-04-25
Last Modified: 2008-02-20
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!!
Question by:hunky_sh
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    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.

    Author Comment

    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?
    LVL 24

    Accepted Solution

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now