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, Jul 17 '17 at 10:55

score 1 · Answer 1 · answered Jul 17 '17 at 16:45

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 '17 at 16:45

mao

11,321
2
13
29

You probably don't need the final delimiter, precisely because MyBatis expects only one statement. – mustaccio Jul 17 '17 at 19:03
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 was not valid. Valid tokens: :.]; SQL was [] for task [ – Vamsi Krishna Jul 18 '17 at 06:40
### Error updating database. Cause: java.sql.SQLException: [SQL0104] Token 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 was not valid. Valid tokens: :. – Vamsi Krishna Jul 18 '17 at 06:42
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 Jul 18 '17 at 06:43
### 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. – Vamsi Krishna Jul 18 '17 at 06:43
My example was for DB2 for Linux/Unix/Windows. You need to find the equivalent syntax for DB2 for i. You may also need to give additional options on the connection string to handle intra-statement delimiters. – mao Jul 18 '17 at 07:32
Can you please share any example . i am new to this and i dont have any idea – Vamsi Krishna Jul 21 '17 at 14:02

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 Answers1