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

Question

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.

sql.execute("$str") will not work, but sql.execute(str) or sql.execute("$str".toString()) should work. I just tried it. — kdabir, Mar 22 '13 at 04:55
@Kunal +1 That is strange. Just tried it again with sql.execute(str) , since you mentioned it should work and also tried sql.execute("$str".toString()). Both work now. Not sure why the former didn't work earlier. Thanks for the tip, though! — jwest, Mar 25 '13 at 19:12
Figured out the reason: I had SQL Developer open connected with the same credentials that I was using in my groovy script. I believe this was the cause of the stalling. As soon as I closed SQL Developer, the script ran fine. Thanks for the help, all. — jwest, Mar 25 '13 at 19:19

jwest · Accepted Answer · 2014-06-25T15:55:46.677

3

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.

edited Jun 25 '14 at 15:55

answered Mar 30 '13 at 22:15

jwest

100
1
1
9

2

Not caused by having a connection from SQL developer, but more likely because you had uncommitted transactions in SQL developer which were blocking the groovy script from committing. – DaveJohnston Jun 24 '14 at 13:05
Yes, thank you for clarifying - that was the case. Updated answer accordingly. – jwest Jun 25 '14 at 15:54

score 0 · Answer 2 · answered Mar 22 '13 at 10:11

Well, this works fine with mySql, so my guess is that it's an issue with the Oracle JDBC driver. I've no experience with it myself, but I understand it's not the most reliable piece of software you can find.

This is a bit of a shot in the dark, but:

sql.execute("$str")
sql.execute("${str}")

In both these calls, the value passed to sql.execute isn't actually a String but a GStringImpl, which might have something to do with your issue.

About your last example:

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

This just isn't valid. ? will be interpreted as a value, not an SQL statement. What you're doing is passing a string, which happens to contain valid sql. The result if it contained "foobar" would be the same.

The only example for which I can't come up with anything is:

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

Are you sure you're not doing anything to str before passing it to sql.execute? Can you make sure it doesn't somehow become wrapped in a specialised groovy class such as GStringImpl ?

Thanks for your reply. I forgot to mention that I also tried `sql.execute(str.toString())` (and `.toString()` with all the other attempts) to try to ensure that it didn't get caught up with the `GStringImpl` issue, but to no avail. And there are no other lines of code between `def str = "..."` and `sql.execute(str)` so that isn't the issue. — jwest, Mar 22 '13 at 16:46

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

2 Answers2