Groovy GString issues

Question

I'm want use $ macro in groovy GString. When i'm wrote this code

['cdata','tdata'].each { def sql = "select * from $it_1" }

i'm get error unknown property $it_

ok, i'm rewrite it

['cdata','tdata'].each { def sql = "select * from ${it}_1" }

then i'm get unwanted quotes in result string - "select * from 'cdata'_1"

Question is how i'm can use $-macro in GString to achive "select * from cdata_1" result string?

Chantilly

dont understand question? when you execute the code above in the groovy shell, there are not quotes in the result? – Unequivocal

Hmm... shortly - $it_1 = error, ${it}_1 = 'cdata'_1, need $[somewhat] = cdata_1. – Chantilly

Answer 1

You can use Groovy's Sql expand feature to help here. The following code will do the trick:

['cdata','tdata'].each {table -> def sql = "select * from ${Sql.expand table}_1" }

Using this method is particularly important if you have other parameters in your GString:

def name = 'Charlie Sheen'
def tables = ['normalPeople','crazyPeople']
tables.each { table -> 
    def sqlString = "select * from ${Sql.expand table} where name = ${name}"
    /* Execute SQL here */
}

In the example above a prepared statement will still be used, and the contents of the 'name' variable will still be handled as a parameter (thus helping to protect you against SQL injection attacks) but the table variable parameter will be expanded correctly.

Cresset

great. I'm solve it by assign GString variable to String. In any way this groovy sql "feature" (automatically replace GStrings with query params) very unclear at my point of view. – Chantilly

Answer 2

If the quotes are not from your IDE, or whatever you're evaluating your code in, you can do this:

['cdata','tdata'].each { def sql = "select * from ${it.replaceAll("'","")}_1" } 

Squelch

I'm using Eclipse with Groovy Plugin and run as Groovy Script. May be problem there... But i'm try your solution, not so elegant but do work. – Chantilly

Answer 3

groovy:000> ['cdata','tdata'].each { def sql = "select * from ${it}_1"; println sql }
select * from cdata_1
select * from tdata_1
===> [cdata, tdata]

I dont see any quotes... that is why I was asking for clarification

Unequivocal

Hm... really... very strange... I'm using Eclipse with Groovy Plugin and run as Groovy Script. May be problem there... I'm try in groovy console and you right, i'm don't get quotes. – Chantilly

Answer 4

Real answer was behind the question, so I'm sorry.
Groovy SQL makes parameterized query from GString, so after I had defined GString

def sql = "select * from ${it}_1";

I passed it to Groovy SQL and when I tried to execute the query, the actual query was

"select * from :?_1";

Of cource this drives MSSQL crazy.
Thanks to you all again, maybe someone will find this useful.

Chantilly