Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 84611 invoked from network); 22 Mar 2007 03:29:42 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Mar 2007 03:29:42 -0000 Received: (qmail 82744 invoked by uid 500); 22 Mar 2007 03:29:48 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 82129 invoked by uid 500); 22 Mar 2007 03:29:46 -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 82118 invoked by uid 99); 22 Mar 2007 03:29:46 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Mar 2007 20:29:46 -0700 X-ASF-Spam-Status: No, hits=2.8 required=10.0 tests=HTML_MESSAGE,INFO_TLD,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of clinton.begin@gmail.com designates 66.249.92.168 as permitted sender) Received: from [66.249.92.168] (HELO ug-out-1314.google.com) (66.249.92.168) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Mar 2007 20:29:38 -0700 Received: by ug-out-1314.google.com with SMTP id m2so555688ugc for ; Wed, 21 Mar 2007 20:29:16 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=FjB+2fGJwwdv7+1lELy9YiWlG31j2oaW3O+WBDM9jNj1ioOoqW6V9SqWbPK4LbBv1i2z5qbj2pavDQjCRhCYcVx3JgfkQhN9yQvIpQXcjHrBofvSmYkCuKJxM00aVgT/KFLoZOnDBJAKeyBdtKFJshAShPjlrbSIVUsPEfrxlwM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=mnx761iGB8w/eD3N5LPnyMcYqxkhdvzY9/TFNfK8uPSVXlIXoh1UGTTrmtDF9wPwYOmDSO2Bl7fgtwSOJGd7ucvTwyzvBNsBG18vBAlBr2Tc8uozPiN+azeP3BnzcyVW7zVUrr1a7qqD1WqlyjKyHz0T5CxFSUXIzglGZTidsWo= Received: by 10.115.33.1 with SMTP id l1mr424679waj.1174534155174; Wed, 21 Mar 2007 20:29:15 -0700 (PDT) Received: by 10.114.135.7 with HTTP; Wed, 21 Mar 2007 20:29:15 -0700 (PDT) Message-ID: <16178eb10703212029u7fc8dc17l69f1b9b748a1a929@mail.gmail.com> Date: Wed, 21 Mar 2007 21:29:15 -0600 From: "Clinton Begin" To: user-java@ibatis.apache.org Subject: Re: Problem using iBatis and transaction management inside Stored procedure In-Reply-To: <9588734.post@talk.nabble.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_180810_13412224.1174534155110" References: <9588734.post@talk.nabble.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_180810_13412224.1174534155110 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline If I'm not mistaken, if you're using transaction management within the stored proc, you have to ensure that the DB connection is set to autocommit. iBATIS doesn't allow autocommit within its own transaction manager, so you'll need to manage your own connection using openSession(Connection)/closeSession(). Clinton On 3/21/07, shailesh.sutar wrote: > > > I am using stored procedure for DB related stuff using iBATIS. (SQL server > 2000).When i call procedure in which i do some DB updation stuff, it works > fine. > ie. > Code: > sqlMapClient.startTransaction(); > sqlMapClient.queryForObject("changeMerchantDetails", map); > sqlMapClient.commitTransaction(); > sqlMapClient.endTransaction(); > > But if i use transaction management inside stored procedure using BEGIN > TRAN > ---- END TRAN, there is problem. > ie. I am just calling > sqlMapClient.queryForObject("changeMerchantDetails ", map); > because there is BEGIN TRAN-----END TRAN inside stored procedure. > > Procedure get called but there is no updation in DB. > (I am checking OUTPUT param values after calling procedure to check if > procedure gets called or not) > > If i want to use transaction management inside stored procedure using > BEGIN > TRAN ---- END TRAN, how can i do it using iBATIS. > > My sqlmapconfig.xml file is > Code: > > > 2.0//EN" > "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> > > > > > > > value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/> > > value="jdbc:microsoft:sqlserver://192.168.2.22:2222;databaseName=dbname;SelectMethod=cursor"/> > > > > > > > > > > > > > > > > > Account.xml file is > Code: > > > "http://ibatis.apache.org/dtd/sql-map-2.dtd"> > > > > javaType="java.lang.String" mode="IN"/> > javaType="java.lang.String" mode="IN"/> > javaType="java.lang.String" mode="IN"/> > javaType="java.lang.String" mode="IN"/> > javaType="java.lang.String" mode="OUT"/> > > > > {call PG_upd_user_details(?,?,?,?,?) } > > > > > I want to know how to use iBatis for SQL server's BEGIN TRAN ---- END TRAN > inside stored procedure. > Is there any extra configuration required in sqlmapconfig file? > -- > View this message in context: > http://www.nabble.com/Problem-using-iBatis-and-transaction-management-inside-Stored-procedure-tf3438925.html#a9588734 > Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > > ------=_Part_180810_13412224.1174534155110 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline If I'm not mistaken, if you're using transaction management within the stored proc, you have to ensure that the DB connection is set to autocommit.  iBATIS doesn't allow autocommit within its own transaction manager, so you'll need to manage your own connection using openSession(Connection)/closeSession().

Clinton

On 3/21/07, shailesh.sutar <shailesh.sutar@avenues.info> wrote:

I am using stored procedure for DB related stuff using iBATIS. (SQL server
2000).When i call procedure in which i do some DB updation stuff, it works
fine.
ie.
Code:
sqlMapClient.startTransaction();
sqlMapClient.queryForObject("changeMerchantDetails", map);
sqlMapClient.commitTransaction();
sqlMapClient.endTransaction();

But if i use transaction management inside stored procedure using BEGIN TRAN
---- END TRAN, there is problem.
ie. I am just calling
sqlMapClient.queryForObject("changeMerchantDetails ", map);
because there is BEGIN TRAN-----END TRAN inside stored procedure.

Procedure get called but there is no updation in DB.
(I am checking OUTPUT param values after calling procedure to check if
procedure gets called or not)

If i want to use transaction management inside stored procedure using BEGIN
TRAN ---- END TRAN, how can i do it using iBATIS.

My sqlmapconfig.xml file is
Code:
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig   PUBLIC "-//ibatis.apache.org//DTD SQL Map Config
2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>


  <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
     <property name="JDBC.Driver"
value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:microsoft:sqlserver://192.168.2.22:2222;databaseName=dbname;SelectMethod=cursor"/>
        <property name="JDBC.Username" value="usr"/>
        <property name=" JDBC.Password" value="pwd"/>

        <property name="Pool.MaximumActiveConnections" value="10"/>
        <property name="Pool.MaximumIdleConnections" value="5"/>
        <property name="Pool.MaximumCheckoutTime" value="120000"/>
        <property name="Pool.TimeToWait" value="10000"/>
    </dataSource>
  </transactionManager>

  <!-- List the SQL Map XML files. They can be loaded from the
       classpath, as they are here (com.domain.data...) -->
  <sqlMap resource="Account.xml"/>


</sqlMapConfig>

Account.xml file is
Code:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    " http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Account">

<parameterMap id="merchantDetails" class="map" >
<parameter property="salutation" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property="first_name" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property="middle_name" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property="last_name" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property="errstatus" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT"/>
</parameterMap>

<procedure id="changeMerchantDetails" parameterMap="merchantDetails" >
{call PG_upd_user_details(?,?,?,?,?) }
</procedure>

</sqlMap>

I want to know how to use iBatis for SQL server's BEGIN TRAN ---- END TRAN
inside stored procedure.
Is there any extra configuration required in sqlmapconfig file?
--
View this message in context: http://www.nabble.com/Problem-using-iBatis-and-transaction-management-inside-Stored-procedure-tf3438925.html#a9588734
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


------=_Part_180810_13412224.1174534155110--