MySQL, MyBatis, Grammar error

Question

I have an application using MyBatis, and I'm trying to add a select SQL that has multiple statements.

<select id="getReleaseDetails" resultType="maingrid" statementType="STATEMENT">
    DROP TEMPORARY TABLE IF EXISTS vrTmp;
    DROP TEMPORARY TABLE IF EXISTS vrTmp2;

    CREATE TEMPORARY TABLE vrTmp AS (
        SELECT vr.*, v.Code

...etc.

However, I keep getting the error:

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TEMPORARY TABLE IF EXISTS vrTmp2;

    CREATE TEMPORARY TABLE vrTmp AS (
        S' at line 2
]
2014-03-11 12:48:20,069 [bio-8080-exec-3] DEBUG DataSourceUtils                - Returning JDBC Connection to DataSource
2014-03-11 12:48:20,085 [bio-8080-exec-3] ERROR VRMService                     - Error in getDetails : 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TEMPORARY TABLE IF EXISTS vrTmp2;

    CREATE TEMPORARY TABLE vrTmp AS (
        S' at line 2
The error may exist in mappings/mapping.xml

The SQL works fine in SQLYog, using the same database login.

What am I missing? It's almost like it does not like the ";" to separate the individual statements.

Thanks

Some more searching this morning yielded this question which states that it is possible to do multiple SQLs in MySQL: http://stackoverflow.com/questions/7174225/mybatis-executing-multiple-sql-statements-in-one-go-is-that-possible. I must have something else wrong. — Jason Nethercott, Mar 12 '14 at 12:37

score 2 · Answer 1 · answered Mar 12 '14 at 13:10

Figured it out: by default the MySQL connection does not allow execution of multiple SQLs. It has to be specified in the URL:

url="jdbc:mysql://ln-ct-dv-my1:3306/TestDB?allowMultiQueries=true"

... and I had to remove the "StatementType" from the mapping file.

score 1 · Answer 2 · answered Mar 11 '14 at 20:25

1

Here are some problems.

You can not use multiple statements in single tag in mybatis. Hence you can not use ";"
If at all if you want to use create table statement use insert tag.

The solution would be to use stored procedure to run multiple statements.

Here is an example for stored procedure and how to call it from mybatis.

CREATE OR REPLACE PROCEDURE KP_PRC AS 
BEGIN
        execute immediate 'DROP TEMPORARY TABLE IF EXISTS vrTmp';
        execute immediate 'DROP TEMPORARY TABLE IF EXISTS vrTmp2';
        execute immediate 'CREATE TEMPORARY TABLE vrTmp AS (SELECT vr.*, v.Code';
    commit;

EXCEPTION
    WHEN OTHERS THEN
    RAISE;

END KP_PRC;

Mybatis xml.

<select id="kpSpCall" statementType="CALLABLE">
        {CALL KP_PRC()}
</select>

answered Mar 11 '14 at 20:25

Karthik Prasad

9,662
10
64
112

I disagree with part of this response. You can do multiple statements in a Mybatis select; so long as the last one is the actual final select. We do it all the time with MSSQL applications. We just drop a "go" between each and it works like a charm. I'm trying to stay away from the stored procedure route. Once you go that route it means your application deployments must be synchronized with database updates. It can be done, it's just another layer of complexity I'm trying to avoid. – Jason Nethercott Mar 11 '14 at 22:07
I believe then you are using subquery or something for select..... not multiple statement – Karthik Prasad Mar 12 '14 at 05:11

MySQL, MyBatis, Grammar error

2 Answers2