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?

Do you have a trailing `;` in the statement(s) in your ddl? [See this answer](http://stackoverflow.com/a/10728434/6730571) — Hugues M., May 16 '17 at 15:33
@HuguesMoreau I understand that ; is the problem but if I don't use ; between 2 create statement then I get: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option — Angelo, May 17 '17 at 08:22
Yeah sorry obviously you'll have multiple statements separated with semicolons... Initially I meant just the one at the very end, but even without it, it would not work (not supported by JDBC I believe), so I made a different suggestion, as an answer because it does not fit in a comment. — Hugues M., May 17 '17 at 08:43

score 2 · Accepted Answer · edited May 23 '17 at 12:26

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()
    }
}

Edited `ScriptRunner.java` to add a `package ` statement, compiled, packed it in `.jar`, put it on Jenkins classpath, used `import ` and its working fine. — SMUsamaShah, May 22 '19 at 01:50

score 1 · Answer 2 · answered May 17 '17 at 09:10

1

If you can't get it done in JDBC (See Hugues' answer), consider executing sqlplus from your Groovy program.

["sqlplus", CREDENTIALS, "@"+scriptToExecute].execute()

answered May 17 '17 at 09:10

Erich Kitzmueller

36,381
5
80
102

Ah, yes, of course, using JDBC is not a requirement! Good idea – Hugues M. May 17 '17 at 09:24

Run ddl script from file in Groovy

2 Answers2