Is it possible to detect each error while executing a multi-line query (DML) in the MS-SQL MyBatis environment? It seems that ONLY the error in the "FIRST QUERY statement" is detected.


       BEGIN
        UPDATE SY_CD_COM_CODE_D SET    CODE_NAME = #{CODE_NAME} .... ; 

        UPDATE  NOT_EXIST_TABLE SET  ... ;  
       END;

MyBatis version

3.5.13

Database vendor and version

Microsoft MS-SQL 2019

Actual result

In the above case, no error is detected. And first statement was committed.

Comment From: harawata

Hello @slowtyping-git ,

If you just want it to throw an exception, you can call SET NOCOUNT ON at the beginning. i.e.

BEGIN
  SET NOCOUNT ON;
  UPDATE SY_CD_COM_CODE_D SET ... ;
  ...

The details are driver dependent, but putting multiple SQL statements into a single MyBatis statement is not a good idea in general. Because...

  • It is not possible to know which statement failed.
  • It is not possible to get the update count of each statement.

Defining one MyBatis statement for each UPDATE and executing them in a transaction should give you more control.

Comment From: slowtyping-git

Hello @slowtyping-git ,

If you just want it to throw an exception, you can call SET NOCOUNT ON at the beginning. i.e.

sql BEGIN SET NOCOUNT ON; UPDATE SY_CD_COM_CODE_D SET ... ; ...

The details are driver dependent, but putting multiple SQL statements into a single MyBatis statement is not a good idea in general. Because...

  • It is not possible to know which statement failed.
  • It is not possible to get the update count of each statement.

Defining one MyBatis statement for each UPDATE and executing them in a transaction should give you more control.

OK! Thank you. It works properly. And I'll consider your advice.

Comment From: harawata

Glad to know it worked!