[groovy-user] When to Use Sql.expand?
202 views
Skip to first unread message
Daniel Price
Nov 6, 2013, 12:20:10 PM11/6/13
to us...@groovy.codehaus.org
Good morning. More XML questions, of course. I'm using the following method to perform Oracle DB queries and assign query results to a variable name. The variable name, DB attribute, DB table, and an amplifying "where" string are contained in a list that the method takes as an argument. Most of the queries are simple, but 2 require use of the "where" string to restrict results, and I'm getting DB errors when using the "where". Maybe this is a syntax issue? I'm not sure when to use Sql.expand and when to just use $variable when feeding variables to a method that constructs a SQL query... I've learned to always use Sql.expand for tables and attributes, but for variable values, I get errors either way. The following code gives me "ORA-00920: invalid relational operator" errors, and using Sql.expand for the survey and where variables results in "ORA-00904: invalid identifier". Thanks!
Method:
def query(list)
//input: sql_queries list [[variable_name, table, attribute, "where" string], etc.]
//output: none.
//function: conducts sql query, saves result in variable to update dynamic xml values
{
list.each{
variable = it[0]
table = it[1]
attribute = it[2]
if ( it.size == 4 )
{
where = it[3]
try{
db.eachRow("select count(${Sql.expand attribute}) from hydro.${Sql.expand table} where survey = $survey and $where")
{
if (it[0] == 0 )
{
binding."$variable" = "Unknown"
}else{
db.eachRow("select ${Sql.expand attribute} from hydro.${Sql.expand table} where survey = $survey and $where")
{
binding."$variable" = it[0] ?: "Unknown"
}
}
}
}catch(e){
println "--SQL query failed: select $attribute from hydro.$table where survey = $survey and $where"
log.append("SQL query failed: select $attribute from hydro.$table where survey = $survey and $where\n")
log.append("$e\n")
err_bit = 1
}
}else{
try{
db.eachRow("select count(${Sql.expand attribute}) from hydro.${Sql.expand table} where survey = $survey")
{
if (it[0] == 0 )
{
binding."$variable" = "Unknown"
}else{
db.eachRow("select ${Sql.expand attribute} from hydro.${Sql.expand table} where survey = $survey")
{
binding."$variable" = it[0] ?: "Unknown"
}
}
}
}catch(e){
println "--SQL query failed: select $attribute from hydro.$table where survey = $survey"
log.append("SQL query failed: select $attribute from hydro.$table where survey = $survey\n")
log.append("$e\n")
err_bit = 1
}
}
}
}
Tim Yates
Nov 6, 2013, 12:37:05 PM11/6/13
to us...@groovy.codehaus.org
Assuming `where` is a String, I think you'll need:
db.eachRow( "select count(${Sql.expand attribute}) from hydro.${Sql.expand table} where survey=$survey and ${Sql.expand where}" )
If you don't use Sql.expand, groovy will try to enclose the where string in quotes ' ' and that wouldn't be valid
Daniel Price
Nov 6, 2013, 12:53:58 PM11/6/13
to us...@groovy.codehaus.org
Tim,
Thanks much--it worked.
Best,
Dan
Dinko Srkoč
Nov 6, 2013, 1:06:34 PM11/6/13
to us...@groovy.codehaus.org
The rule is actually simple: `${param}` should be used in the very same positions as '?' is used in JDBC prepared statements. For every other case use `${Sql.expand(...)}`.
Sql treats GString values as parameters, unless you use expand(). In that case it leaves them as they are.
Cheers,
Dinko
Reply all
Reply to author
Forward
0 new messages