Execute MySQL script in grails app

Question

I have an MySQL Script I want to execute in a controller when my Grails 3.0.9 application is running. I've tried it this way:

import groovy.sql.Sql
import grails.util.Holders

    def void clearDatabase() {
        String sqlFilePath = 'path/to/file/clear_database.sql'
        String sqlString = new File(sqlFilePath).text
        def sql = Sql.newInstance(Holders.config.dataSource.url, Holders.config.dataSource.username, Holders.config.dataSource.password, Holders.config.dataSource.driverClassName)
        sql.execute(sqlString)
    }

Thats how my clear_database.sql file looks like:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table_a;
TRUNCATE table_b;
TRUNCATE table_c;
SET FOREIGN_KEY_CHECKS = 1;

Thats the error message I get:

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Warning Code: 1064, SQLState: 42000
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 'TRUNCATE table_a;

Is my MySQL syntax wrong or am I missing something else?

edit:

When I run the script manually it works. So I think the script is correct but the way I execute it not.

Community · Accepted Answer · 2017-05-23 11:51:27Z

1

The problem was that sql.execute(sqlString) wants a GString and I've given it a normal String so it added quotes and the result was an incorrect MySQL syntax like described here.

That's how it works for me:

    def sql = Sql.newInstance(Holders.config.dataSource.url, Holders.config.dataSource.username, Holders.config.dataSource.password, Holders.config.dataSource.driverClassName)
    sql.execute "SET FOREIGN_KEY_CHECKS = 0;"
    sql.execute "truncate ${Sql.expand("table_a")}"
    sql.execute "truncate ${Sql.expand("table_b")}"
    sql.execute "truncate ${Sql.expand("table_c")}"
    sql.execute "SET FOREIGN_KEY_CHECKS = 1;"

There is no clear_database.sql file needed anymore.

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Mar 24, 2016 at 15:58

Peter

1,7692 gold badges31 silver badges62 bronze badges

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

Comments

Juan Diego Antezana · Accepted Answer · 2016-03-24 14:40:10Z

0

Not sure but could you try: as your clear_database.sql

    SET FOREIGN_KEY_CHECKS = 0;
    TRUNCATE TABLE table_a;
    TRUNCATE TABLE table_b;
    TRUNCATE TABLE table_c;
    SET FOREIGN_KEY_CHECKS = 1;

Good luck!!

answered Mar 24, 2016 at 14:40

Juan Diego Antezana

9124 silver badges16 bronze badges

Collectives™ on Stack Overflow

Execute MySQL script in grails app

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related