Grails 3.3 execute H2 script command

Question

I'm running a small, trivial Grails 3.3.0 application using a H2 file based database. For simple backup reasons I would like to dump the current database state to a file using the H2 specific SCRIPT command:

SCRIPT TO /path/to/backup/dir/tempoDb.sql;

Currently I am trying to execute the native SQL command like this.

User.withSession { session ->

   NativeSQLQuerySpecification nativeSQLQuerySpecification = new NativeSQLQuerySpecification("SCRIPT TO /path/to/backup/dir/tempoDb.sql;", null, null)

   session.executeNativeUpdate(nativeSQLQuerySpecification, new QueryParameters())
}

but this does not work.

Answer 1

You can autowire the dataSource and try to run your sql query using the connection obtained from datasource Without going through Hibernate. The dataSource bean is registered in the Grails Spring context and it is an instance of javax.sql.DataSource.

Here is an example of a Grails service that backup the current H2 database to the file system.

@ReadOnly
class BackupService {

    DataSource dataSource

    def backup() {

        def sql = "SCRIPT DROP TO '${System.properties['java.io.tmpdir']}/backup.sql'"

        Statement statement = dataSource.connection.createStatement()
        boolean result = statement.execute(sql)
    }
}