[SOLVED] Groovy Sql.execute() with sql statement as string variable not working

Groovy Sql.execute() with sql statement as string variable not working

I am trying to execute a sql statement in groovy by passing the statement as a string variable rather than a string literal. The reason I am using a string variable is because I am reading the sql statement from a file.

For example (assuming sql is a valid Instance of groovy.sql.Sql - which I have verified):

sql.execute("insert into table(id) values(1)")

Works just fine.

However, the following does not:

def str = "insert into table(id) values(1)"
sql.execute(str)

The last example just hangs when I run it. No SQL errors, it just stalls. I tried putting a println after the execute and it never reaches the println statement.

So, I tried the following variations:

sql.execute("$str")

and

sql.execute("${str}")

and even

sql.execute("?", [str])

for curiosity's sake, but all give the following error:

Mar 21, 2013 6:28:16 PM groovy.sql.Sql execute
WARNING: Failed to execute: ? because: Invalid SQL type: sqlKind = 0
Caught: java.sql.SQLException: Invalid SQL type: sqlKind = 0
java.sql.SQLException: Invalid SQL type: sqlKind = 0
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:77)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at runSqlFile.run(runSqlFile.groovy:40)

I am admittedly new to groovy, but I can't figure out why a string literal works but the variable will not. Any help here would be greatly appreciated. Let me know if more information is needed.

Also, the reason I am grabbing individual sql statements from a file is because I could not find a way to execute an entire .sql file using groovy. If there is a simple way to do that which I haven't found, that would also solve my problem. Thanks in advance.

Solution

Figured out the reason: I had SQL Developer open connected with the same credentials that I was using in my groovy script. I had executed a few commands in SQL Developer and neglected to commit them, which caused a conflict when running the groovy script. This was the cause of the stalling. As soon as I committed the changes made in SQL Developer, the script ran fine.