Discussions

EJB programming & troubleshooting: urgent: how to control the transaction for stored procedure

Bookmark and Share Get thread feed

  1. urgent: how to control the transaction for stored procedure (2 messages)

    • Posted by: siva kumar
    • Posted on: June 21 2002 08:01 EDT
    dear all

    subject
    how do i commit or rollback the records inserted by an
    stored procedure throug an stateless session bean.

    problem
    i have few jsp's calling stored procedure's in oracle through an stateless session bean.The procedure does some updation or insertion of records and the transaction inside the procedure are not setted for commit or rollback.The procedure just returns "-1" for faliure for which i(jsp/ejb) need to rollback and "0" for commiting.


    jsp code snippet
    -----------------
    DataCtlr remote=home.create();
    Hashtable h = remote.callProcedure("ProcedureName",Hashtable parameters);

    if(h.get("code").equals("-1"))
    txn.rollback();
    else
    txn.commit();

    DataCtlr which is an stateless ejb which meant for connecting database.

    There are two places i can do commit or rollback
    1.JSP
    2.DataCtlr callProcedure method itself.

    i prefer the second one would be better approach.

    i tried using UserTransaction API but i am getting expection.
    Its expecting ejb.properties and security.properties file
    which is not found in jboss.further i am using jboss app server.

    i need a soulution on this asap.

    with rgds
    shiva

    Reply to this

    Threaded Messages (2)

  2. found the answer to roll back a procedure[ Go to top ]

    hi

    i have found a solution for this.
    In the callProcedure i have a code like strRegisterOutParameter.equals("-1")).

    If the above condition satisfies i should rollback the records updated/inserted by the stored procedure.
    Now what i do ...

    if(strRegisterOutParameter.equals("-1"))
    throw new TransactionRolledBackException();

    the above code roll backs the records inserted by the procedure.

    Note:The callProcedure is a buisness method in an stateless session bean which serves to execute any stored procedure.
    The transaction attribute i have setted for this method is Requires which impiles Container Managed Transaction.

    Earlier i tried throwing new Exception() but it did't roll back instead it has commited the record. why this happens?
    Is the transaction layer is written in such a fashion which expects only TransactionRolledBackException class to thrown?

    expecting cheerfull replys

    with rgds
    shiva

     






    Reply to this Reply to original

  3. found the answer to roll back a procedure[ Go to top ]

    Hi,
    The container automatically rolls back transaction of a business method if the exception is an system exception and it also removes the instance of that bean. However in case of a business exception it is responsibility of the developer to catch the exception and mark the transaction for rollback otherwise the container commits the transaction. So you can also mark the transaction for rollback by calling setRollbackOnly method from the SessionContext.
    Reply to this Reply to original