Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    6

    Unanswered: Need to execute multiple stored procedures within single transaction

    I need to execute multiple stored procedure within a single transaction, so if any exception occurs it will rollback complete transaction. I will call this transaction from a java class.I am using Sybase database
    Please help.


    Thanks,
    Vikas Tayde

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Transactions should always be kept as short as possible. Having multiple sp within one transaction is likely to lead to locking and database slow downs. Are you sure you need to put all this code into one transaction?

  3. #3
    Join Date
    May 2010
    Posts
    6

    Need to execute multiple stored procedures within single transaction

    Yes, Ineed to execute multiple stored procedure whithin one transaction.
    Actually I have one method in Java class which calls another methods and these methods execute stored procedures.
    eg:
    Public void save(){
    //this method call another methods which executes stored procedures

    this.callUpdFixCompIdSql(Connection, params);
    this.callInsCustAccSql(Connection, params);
    this.callUpdFixCustomer(Connection, params, lastUpdate!=null);
    //above methods have SP
    ...
    ...


    }


    Now when any transaction fails it will not rollback the changes.
    Thats why I need to execute above save() method in a single transaction which has multiple SP.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I really just wanted a simple explanation of why you couldn't have smaller transactions. Seeing your method calls tells me very little. What does the whole transaction actually do? Can the individual sp have transactions within them? Can you prepare the work in the sp and then just commit the updates etc in one small transaction?

  5. #5
    Join Date
    May 2010
    Posts
    6
    Hi,

    Actually I have a java class which has a method which in turn calls another methods and these sub methods calls stored procedures. Main method calls around 8 sub methods which call stored procedure. These individual sp have transactions within them. On some event main method get called which executes all 8 sp. The requirement is like I need to make these execution of sp transactional means if any sp goes rong all transaction should be rollback.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Have a look at chained mode

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •