Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 66143 invoked from network); 15 Jun 2006 17:52:31 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 15 Jun 2006 17:52:31 -0000 Received: (qmail 38718 invoked by uid 500); 15 Jun 2006 17:51:44 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 38697 invoked by uid 500); 15 Jun 2006 17:51:44 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 38685 invoked by uid 99); 15 Jun 2006 17:51:44 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 Jun 2006 10:51:44 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,RCVD_IN_BL_SPAMCOP_NET,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of jeffgbutler@gmail.com designates 66.249.92.174 as permitted sender) Received: from [66.249.92.174] (HELO ug-out-1314.google.com) (66.249.92.174) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 Jun 2006 10:51:43 -0700 Received: by ug-out-1314.google.com with SMTP id k3so1121562ugf for ; Thu, 15 Jun 2006 10:51:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=Pc0YodvtPd6VA6SE7VzBw+eH4DjgrBW5n1be57sNJqq896kRYHJ0GNoy96rZyrBGZFudm+xHYb2xXnHUyUAasgOnOCDRcAZHrbQWJ8ErNZ5+gilLmYfExhxHCyZVpj4S7Pv624/JRza4caxfUeEpWTqqE59GUp9kddS44DAx2jI= Received: by 10.67.96.14 with SMTP id y14mr1946097ugl; Thu, 15 Jun 2006 10:51:21 -0700 (PDT) Received: by 10.66.238.18 with HTTP; Thu, 15 Jun 2006 10:51:20 -0700 (PDT) Message-ID: Date: Thu, 15 Jun 2006 12:51:20 -0500 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: Rollback Issues In-Reply-To: <44919B55.2080803@aol.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3133_25863960.1150393880534" References: <3c8cea400606150809g2d0b1a64n4eb4c3f0a6fc1642@mail.gmail.com> <44917996.1020309@aol.com> <3c8cea400606150858y62be7a8r3f2220a715665c13@mail.gmail.com> <3c8cea400606150947ra416124qb0648680fd4eb81d@mail.gmail.com> <44919B55.2080803@aol.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_3133_25863960.1150393880534 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Tony - this is something iBATIS is doing to try to help you (Ha Ha). By default iBATIS will not commit after a queryForObject or queryForList - even if you explictely call the commitTransaction() method. The solution is to add the attribute commitRequired="true" to the transactionManager element in your SqlMapConfig.xml file. Jeff Butler On 6/15/06, Tony Qian wrote: > > Jeff, > > Since you mentioned stored procedure and transaction, I have encountered a > strange situation when using MySQL stored procedure. In my stored procedure, > I do some update to the records in a table then select all those rows get > updated. In my Dao, I call QueryForList to retrieve the records. Everything > works perfectly fine ( I got correct result set) until I went to database > and found that all updates were not committed. I have to commit the update > in my stored procedure. > > Is it true that all QueryForList or QueryForObject will NOT do commit? > That makes sense for 'regular query'. But, we can combine anything inside > stored procedure. This is not correct behavior. One way to solve this > problem is to use daoManager.startTransaction () etc. to force commit. I > haven't tried that yet. Don't know if it will work. > > Thanks, > Tony > > Jeff Butler wrote on 6/15/2006, 1:14 PM: > > The plot thickens... > > I seem to remember that there is something unusual with Oracle stored > procedures and transactions. Sounds to me like the stored procedure call is > causing a commit somehow. You could try some other combination of > statements without a SP call just to check. I'm not an Oracle person so > maybe someone else could jump in who's had some experience with stored > procedures and transactions in Oracle. > > Also, what I meant from question 2 was this - what is the actual exception > you are seeing at runtime that bypasses the commit? Maybe there is some > useful information in that exception? > > Jeff Butler > > > On 6/15/06, Debasish Dutta Roy wrote: > > > > OK. Here are the answers > > > > 1. First one is an insert & second one is a stored proc call. The first > > one populates the REQUEST table and the second one gets a number based on > > the request id. > > 2. Dao1 and DAO2 catches SQLException and throws DAOException (my > > exception, not iBATIS DAOException). > > This DAOException is caught in the catch block and thrown as a > > business exception. say RequestFailureException > > 3. Sampling from my dao.xml > > > > > > > > > > > > > > > > > > > implementation="com.mydao.sqlmap.SqlMapRequestDAOImpl"/> > > > implementation=" com.mydao.sqlmap.SqlMapGOSDAOImpl"/> > > ....................... > > > > > > Hope this will help. > > > > > > On 6/15/06, Jeff Butler wrote: > > > > > > I think we need a little more information. I assume that the code > > > you've prsented is a mocked up example - and everything looks right in your > > > mock up. > > > > > > Questions: > > > > > > 1. What is the method secondTask() doing? Hopefully no transaction > > > stuff (like another start transaction or a commit). > > > 2. What Exception is thrown from secondTask()? > > > 3. By any chance, are DAO1 and DAO2 in different elements in > > > your dao.xml? From your mocked up code it doesn't look like they are, > > > but also doesn't hurt to ask. > > > > > > Jeff Butler > > > > > > > > > > > > > > > On 6/15/06, Debasish Dutta Roy wrote: > > > > > > > > offcourse it will be helpful if you could share what you did > > > > correctly, and if you could find something wrong in my approach. I am using > > > > Oracle 9i. > > > > > > > > > > > > On 6/15/06, Tony Qian wrote: > > > > > > > > > > Yes. It worked for me (MySQL). > > > > > > > > > > Tony > > > > > > > > > > Debasish Dutta Roy wrote on 6/15/2006, 11:09 AM: > > > > > > > > > > Hi All > > > > > Has anyone successfully tested rollback with DAOManager > > > > > implementation. > > > > > > > > > > I am unable to get it working. > > > > > > > > > > > > > > > I have a business delegate who does like this: > > > > > > > > > > DAO1 dao1 = daoManager.getDAO(DAO1.class); > > > > > DAO2 dao2 = daoManager.getDAO(DAO2.class); > > > > > try { > > > > > daoManager.startTransaction (); > > > > > dao1.firstTask(); > > > > > dao2.secondTask(); > > > > > daoManager.commitTransaction(); > > > > > } catch (Exception e) { > > > > > throw myException; > > > > > } finally { > > > > > daoManager.endTransaction (); > > > > > } > > > > > > > > > > > > > > > my dao.xml is like this > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ........... > > > > > ........... > > > > > > > > > > > > > > > and the corresponding sql-map-config.xml > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------=_Part_3133_25863960.1150393880534 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Tony - this is something iBATIS is doing to try to help you (Ha Ha).  By default iBATIS will not commit after a queryForObject or queryForList - even if you explictely call the commitTransaction() method.  The solution is to add the attribute commitRequired="true" to the transactionManager element in your SqlMapConfig.xml file.
 
Jeff Butler

 
On 6/15/06, Tony Qian <daqiqian2@aol.com> wrote:
Jeff,

Since you mentioned stored procedure and transaction, I have encountered a strange situation when using MySQL stored procedure. In my stored procedure, I do some update to the records in a table then select all those rows get updated. In my Dao, I call QueryForList to retrieve the records. Everything works perfectly fine ( I got correct result set) until I went to database and found that all updates were not committed. I have to commit the update in my stored procedure.

Is it true that all QueryForList or QueryForObject will NOT do commit? That makes sense for 'regular query'. But, we can combine anything inside stored procedure. This is not correct behavior. One way to solve this problem is to use daoManager.startTransaction () etc. to force commit. I haven't tried that yet. Don't know if it will work.

Thanks,
Tony

Jeff Butler wrote on 6/15/2006, 1:14 PM:

The plot thickens...
 
I seem to remember that there is something unusual with Oracle stored procedures and transactions.  Sounds to me like the stored procedure call is causing a commit somehow.  You could try some other combination of statements without a SP call just to check.  I'm not an Oracle person so maybe someone else could jump in who's had some experience with stored procedures and transactions in Oracle.
 
Also, what I meant from question 2 was this - what is the actual exception you are seeing at runtime that bypasses the commit?  Maybe there is some useful information in that exception?
 
Jeff Butler

 
On 6/15/06, Debasish Dutta Roy < debasish.duttaroy@gmail.com> wrote:
OK. Here are the answers

1. First one is an insert & second one is a stored proc call. The first one populates the REQUEST table and the second one gets a number based on the request id.
2. Dao1 and DAO2 catches SQLException and throws DAOException (my exception, not iBATIS DAOException).
    This DAOException is caught in the catch block and thrown as a business exception. say RequestFailureException
3. Sampling from my dao.xml


    <context>
        <transactionManager type="SQLMAP">
            <property name="SqlMapConfigResource" value="xml/sql- map-config.xml"/>
        </transactionManager>

        <!-- DAO declarations -->
        <dao interface=" com.novartis.phoenix.db.dao.RequestDAO"
            implementation="com.mydao.sqlmap.SqlMapRequestDAOImpl "/>
        <dao interface=" com.novartis.phoenix.db.dao.GOSDAO"
            implementation=" com.mydao.sqlmap.SqlMapGOSDAOImpl"/>
     .......................
    </context>

Hope this will help.


On 6/15/06, Jeff Butler <jeffgbutler@gmail.com > wrote:
I think we need a little more information.  I assume that the code you've prsented is a mocked up example - and everything looks right in your mock up.
 
Questions:
 
1. What is the method secondTask() doing?  Hopefully no transaction stuff (like another start transaction or a commit).
2. What Exception is thrown from secondTask()?
3. By any chance, are DAO1 and DAO2 in different <context> elements in your dao.xml?  From your mocked up code it doesn't look like they are, but also doesn't hurt to ask.
 
Jeff Butler
 


 
On 6/15/06, Debasish Dutta Roy <debasish.duttaroy@gmail.com > wrote:
offcourse it will be helpful if you could share what you did correctly, and if you could find something wrong in my approach. I am using Oracle 9i.


On 6/15/06, Tony Qian <daqiqian2@aol.com> wrote:
Yes. It worked for me (MySQL).

Tony

Debasish Dutta Roy wrote on 6/15/2006, 11:09 AM:
Hi All
Has anyone successfully tested rollback with DAOManager implementation.

I am unable to get it working.


I have a business delegate who does like this:

DAO1 dao1 = daoManager.getDAO(DAO1.class);
DAO2 dao2 = daoManager.getDAO(DAO2.class);
try {
      daoManager.startTransaction ();
      dao1.firstTask();
      dao2.secondTask();
      daoManager.commitTransaction();
} catch (Exception e) {
   throw myException;
} finally {
   daoManager.endTransaction ();
}


my dao.xml is like this

    <context>
        <transactionManager type="SQLMAP">
            <property name="SqlMapConfigResource" value="xml/sql-map-config.xml "/>
        </transactionManager>
     ...........
     ...........
   </context>

and the corresponding sql-map-config.xml

<sqlMapConfig>

    <transactionManager type="JDBC" commitRequired="true">
        <dataSource type="JNDI">
            <property name="DataSource" value="java:comp/env/jdbc
/MyDataSource"/>
        </dataSource>
    </transactionManager>





------=_Part_3133_25863960.1150393880534--