execute multiple DB2 statements with MyBatis

Question

i have searched for some posts related to executing multiple SQL statements with Mybatis. but that didnt work. i need to execute multiple DB2 statements in MyBatis . consider i have to run 3 DELETE statements

i have referred below link . in this post they have given example for Mybatis with ORACLE.

MyBatis executing multiple sql statements in one go, is that possible?

Query syntax :

<delete id="clearTable" parameterType="test">
    DELETE FROM tableA WHERE key = #{key} 

    DELETE FROM tableb WHERE key = #{key} and param = #{param}

    DELETE FROM tablec WHERE key = #{key}  and param = #{param}
<delete>

error log :

Translating SQLException with SQL state '42601', error code '-199', message [[SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.]; SQL was [] for task [

Error updating database. Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

SQL: DELETE FROM tableA WHERE key = ? DELETE FROM tableb WHERE key = ? and param = ? DELETE FROM tablec WHERE key = ? and param = ?

Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

Thanks in Advance

Have you tried putting a semicolon (;) after each statement? — data_henrik
– data_henrik, Commented Jul 17, 2017 at 10:55

mao · Accepted Answer · 2017-07-17 16:45:49Z

1

MyBatis appears to expect a single line for its SQL statements. I don't use MyBatis, but I use DB2. Try either calling a stored procedure (and pass it the key-values of items to delete), or try an anonymous block.

begin atomic
   DELETE FROM tableA WHERE key = 1  ;

   DELETE FROM tableb WHERE key = 2 and param = 1 ;

   DELETE FROM tablec WHERE key = 3  and param = 1 ;
end@

The above syntax is valid for DB2 Linux/Unix/Windows current versions (v10.5 or v11.1 ) - you would replace the literal values with the MyBatis (or whatever) parameter-markers. One detail is that the syntax above has two delimiters, one is the statement delimiter (which in my example is @), the other is the intra-statement delimiter (which in my example is ;) and DB2 lets you configure both of these delimiters , so you might need to give at least one of those details to DB2 somehow when opening the connection from MyBatis.

answered Jul 17, 2017 at 16:45

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mustaccio Over a year ago

You probably don't need the final delimiter, precisely because MyBatis expects only one statement.

Vamsi Krishna Over a year ago

I have tried this but got some error Error when using Semicolon : DEBUG [SQLErrorCodesFactory] SQL error codes for 'DB2 UDB for AS/400' found DEBUG [SQLErrorCodeSQLExceptionTranslator] Translating SQLException with SQL state '42601', error code '-104', message [[SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: :.]; SQL was [] for task [

Vamsi Krishna Over a year ago

### Error updating database. Cause: java.sql.SQLException: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: :. ### SQL: begin atomic DELETE FROM tableA WHERE key = 1 ; DELETE FROM tableb WHERE key = 2 and param = 1 ; DELETE FROM tablec WHERE key = 3 and param = 1 ; end@ ### Cause: java.sql.SQLException: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: :.

Vamsi Krishna Over a year ago

with out semicolon :DEBUG [SQLErrorCodesFactory] SQL error codes for 'DB2 UDB for AS/400' found DEBUG [SQLErrorCodeSQLExceptionTranslator] Translating SQLException with SQL state '42601', error code '-199', message [[SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.]; SQL was [] for task [ ### Error updating database. Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

Vamsi Krishna Over a year ago

### SQL: begin atomic DELETE FROM tableA WHERE key = 1 DELETE FROM tableb WHERE key = 2 and param = 1 DELETE FROM tablec WHERE key = 3 and param = 1 end@ ### Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

|

Collectives™ on Stack Overflow

execute multiple DB2 statements with MyBatis

Error updating database. Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

SQL: DELETE FROM tableA WHERE key = ? DELETE FROM tableb WHERE key = ? and param = ? DELETE FROM tablec WHERE key = ? and param = ?

Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Error updating database. Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

SQL: DELETE FROM tableA WHERE key = ? DELETE FROM tableb WHERE key = ? and param = ? DELETE FROM tablec WHERE key = ? and param = ?

Cause: java.sql.SQLException: [SQL0199] Keyword DELETE not expected. Valid tokens: OR USE SKIP WAIT WITH.

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related