Run ddl script from file in Groovy

Question

I working with Spock and Groovy in order to test my application. I should need to run a ddl script before to run every test. To execute the script from Groovy I am using the following code:

    def scriptToExecute = './src/test/groovy/com/sql/createTable.sql'
    def sqlScriptToExecuteString = new File(scriptToExecute).text
    sql.execute(sqlScriptToExecuteString)

The createTable.sql is a complex script that do several drop and create operation ( of course it is multiline ). When I try to execute it I got the following exception:

java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

To notice that the ddl is correct since that it has been checked running it on the same DB that I am connecting with groovy.

Any Idea how to resolve the problem?

Answer 1

I think JDBC does not support this, but there are tools/libraries that could help, see this answer for Java.

In Groovy, using this JDBC script runner would be something like:

Connection con = ....
def runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror])
def scriptFile = new File("createTable.ddl")
scriptFile.withReader { reader ->
    runner.runScript(reader)
}

Or, if your script is "simple enough" (ie no comments, no semicolons other than separating statements...), you can load the text, split it around ; and execute using sql.withBatch, something like that:

def scriptText = new File("createTable.ddl").text
sql.withBatch { stmt ->
    scriptText.split(';').each { order ->
        stmt.addBatch order.trim()
    }
}