[groovy-user] Groovy SQL problem with "in" parameters

I've been struggling with this problem and have tried a lot of different
things but I just can't seem to get it to work.  I can't find anyone asking
the problem on the intrawebs so I'm hoping I'm doing something so boneheaded
that I'm the first person to be confused by it.

I want to query a temporary MySQL table to get back some rows whose value is
in a given array.  I can get this working using GORM's Criteria Builder
using standard tables but I don't have any mappings defined for temp tables
so I'm doing this manually, using groovy.sql.Sql.

The query I want to run is: "select t.id, t.val1 from test1 t where t.val1
in ?" where the last argument is an array of Strings.  Running it just as
"... in (?)" gives me zero rows no matter what arguments I pass.  If I run
"... in ?" then I get:

Error with SQL: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You
have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'_binary'¬í\0ur\0[Ljava.lang.String;­ÒVçé
{G\0\0xp\0\0\0t\0oranget\0carrot'' at line 1

It looks like its doing something very strange with my parameters.  I would
convert them into a comma-delimited string and insert them directly into the
SQL but they're all user-entered parameters and I would rather not deal with
the security problems that raises.

I'd be grateful for any insight anyone has.  It's really got me foozled.

Here is a test application:

----------
import groovy.sql.Sql

class SqlTests {

	static void main(args) {

		try {
			def db =
Sql.newInstance("jdbc:mysql://localhost/test?enable-named-pipe&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory",
					"user", "password", "com.mysql.jdbc.Driver")
					
			db.execute("""
create temporary table test1 (
		id bigint(20) unique auto_increment,
		val1 varchar(15)
)""")
			def insertStmt = "insert into test1(val1) values (?)"
			def values = ["apple", "orange", "carrot", "lettuce", "beet", "turnip"]
			values.each{ db.executeInsert(insertStmt, [it]) }
			
			// validate
			db.eachRow("Select id, val1 from test1", {println "inserted ${it.id} -
${it.val1}"})
					
			String[] params = ["orange", "carrot"].toArray()
			
			
			def queryStmt = "select t.id, t.val1 from test1 t where t.val1 in (?)"
			def rowsReturned = 0
			def res = db.eachRow(queryStmt, [params], {
				println "Result: ${it}"
				rowsReturned++
			})
			println "Querying for ${params}, Rows returned: ${rowsReturned},
expecting 2"  
		} catch( Exception e ) {
			println "Error with SQL: ${e}"
			e.printStackTrace()
		}
		
	}
}
-----------
Here's the sample output:

inserted 1 - apple
inserted 2 - orange
inserted 3 - carrot
inserted 4 - lettuce
inserted 5 - beet
inserted 6 - turnip
Querying for {"orange", "carrot"}, Rows returned: 0, expecting 2

--

-- 
View this message in context: http://www.nabble.com/Groovy-SQL-problem-with-%22in%22-parameters-tp19353622p19353622.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email