use database in update statement always return 0

[janwen lou]

If execute use database xyz; in update statement,the mapper interface always return 0 update rows.

How to config to return the actually update rows?

[Kazuki Shimizu]

Could you provide more detail information and reproduce project?

[Guy Rouillier]

The semi-colon is a statement delimiter to the DBMS, so you are actually executing multiple statements.  I'm guessing that MyBatis is returning the value from the first executed statement, since you say you are using an <update> statement in your mapper.  The proper way to chain multiple statements is to use an anonymous block, if your DBMS supports it.  You don't mention what DBMS you are using.

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[janwen lou]

This is the statement xml:

<update id="updateMerchantInfo" parameterType="com.xiaoyeyun.lbp.bean.LBPMerchantInfo">

    USE ${dbName};

    

    update merchant_info

    set name = #{name,jdbcType=VARCHAR},

      merchant_code = #{merchantCode,jdbcType=VARCHAR},

      db_name = #{dbName,jdbcType=VARCHAR},

      merchant_type = #{merchantType,jdbcType=VARCHAR},

      contacts = #{contacts,jdbcType=VARCHAR},

      contact_phone = #{contactPhone,jdbcType=VARCHAR},

      logo = #{logo,jdbcType=VARCHAR},

      weixin_url = #{weixinUrl,jdbcType=VARCHAR},

      update_time = #{updateTime,jdbcType=TIMESTAMP}

    where merchant_id = #{merchantId,jdbcType=INTEGER};

  </update>

It 's very simple to understand,Before execute update statement,switch database,update statement always return 0 update rows,but actually update rows more than 0.

[janwen lou]

In our project,We use mybatis with MySQL.

This is the mapper xml in the update statement

<update id="updateMerchantInfo" parameterType="com.xiaoyeyun.lbp.bean.LBPMerchantInfo">

    USE ${dbName};

    

    update merchant_info

    set name = #{name,jdbcType=VARCHAR},

      merchant_code = #{merchantCode,jdbcType=VARCHAR},

      db_name = #{dbName,jdbcType=VARCHAR},

      merchant_type = #{merchantType,jdbcType=VARCHAR},

      contacts = #{contacts,jdbcType=VARCHAR},

      contact_phone = #{contactPhone,jdbcType=VARCHAR},

      logo = #{logo,jdbcType=VARCHAR},

      weixin_url = #{weixinUrl,jdbcType=VARCHAR},

      update_time = #{updateTime,jdbcType=TIMESTAMP}

    where merchant_id = #{merchantId,jdbcType=INTEGER};

  </update>

[Guy Rouillier]

Looks like MariaDB allows compound statements outside of a stored procedure, but MySQL does not.  Apparently, you can not use the "use database" statement in a stored procedure either: 

https://dba.stackexchange.com/questions/48326/use-database-command-inside-a-stored-procedure

Maybe you can employ the method suggested there of prefixing the table name in the SQL statement with the name of the database you want to use.  This would appear only to work if the database is on the same server you are connected to.  I'm not a MySQL expert, so please don't consider my comments as authoritative.

[janwen lou]

thanks,good advice.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/CCk8RGHf3M8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mybatis-user+unsubscribe@googlegroups.com.

[Tim]

you can also try allowMultiQueries=true https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

[janwen lou]

UPDATE SQL WITH database prefix,work well.thank return actual update rows.

<update id="updateOrderStatus">

     update paycenter.trx_order

     set trx_order_status=#{new_status},update_time=now()

     where trx_order_no=#{trx_order_no} and trx_order_status=#{current_status}

  </update>

On Thu, Nov 23, 2017 at 1:44 PM, Guy Rouillier <guy.ro...@gmail.com> wrote:

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/CCk8RGHf3M8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mybatis-user+unsubscribe@googlegroups.com.

[janwen lou]

Just tried it,but not work,did not return the actual update rows.

jdbc.url=jdbc:mysql://xxxx:3306/xx?characterEncoding=utf8&amp;connectTimeout=6000&amp;allowMultiQueries=true

Using the following update statement

 <update id="updateOrderStatus">

     use paycenter;

     update trx_order

     set trx_order_status=#{new_status},update_time=now()

     where trx_order_no=#{trx_order_no} and trx_order_status=#{current_status}

  </update>