Groovy SQL problem with "in" parameters

[adrianp]

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

[David Sills]

Mind, I haven't actually got the vaguest, but it sounds like the process is using (internally) a PreparedStatement, in which case without some magic you cannot use this one-argument syntax - you must have a series of "... in (?,?,?,?,?...)" that matches your arguments, then assign them to the PS one at a time. This would be useful magic to implement in a Groovy object, and wouldn't be terribly hard, I think, but might not have been implemented yet. If I'm right, the result in the case of "... in (?)" might look in SQL like "... in ('[String1, String2, ...]')" (in other words, the string version of the array itself as a single parameter), which is most definitely not what you want and will almost undoubtedly give you zero rows (unless you have the oddest data...).

Try "... in (?, ?, ?, ?...)" (to the length of your array: undoubtedly there's some clever Groovy syntax for that, but I don't happen to know it off the top of my head) with your array (or single Strings?), and see if that works - it's at least worth a couple of minutes.

David Sills


-----Original Message-----
From: adrianp [mailto:adrian.a.powell@...]
Sent: Saturday, September 06, 2008 10:17 PM
To: user@...
Subject: [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



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

    http://xircles.codehaus.org/manage_email

[adrianp]

Glah.  I had been seduced by the allure of the ARRAY type and totally overlooked this simple and easy solution.  Wow, what an embarrassing oversight.  Thanks David.

-Adrian.

On Sat, Sep 6, 2008 at 9:18 PM, David Sills <DSills@...> wrote:

Mind, I haven't actually got the vaguest, but it sounds like the process is using (internally) a PreparedStatement, in which case without some magic you cannot use this one-argument syntax - you must have a series of "... in (?,?,?,?,?...)" that matches your arguments, then assign them to the PS one at a time. This would be useful magic to implement in a Groovy object, and wouldn't be terribly hard, I think, but might not have been implemented yet. If I'm right, the result in the case of "... in (?)" might look in SQL like "... in ('[String1, String2, ...]')" (in other words, the string version of the array itself as a single parameter), which is most definitely not what you want and will almost undoubtedly give you zero rows (unless you have the oddest data...).

Try "... in (?, ?, ?, ?...)" (to the length of your array: undoubtedly there's some clever Groovy syntax for that, but I don't happen to know it off the top of my head) with your array (or single Strings?), and see if that works - it's at least worth a couple of minutes.

David Sills

-----Original Message-----
From: adrianp [mailto:adrian.a.powell@...]
Sent: Saturday, September 06, 2008 10:17 PM
To: user@...
Subject: [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'¬í\0 ur\0 [Ljava.lang.String;­ÒVçé
{G \0\0xp\0\0\0 t\0 oranget\0 carrot'' 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



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

   http://xircles.codehaus.org/manage_email

[David Sills]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Adrian:

 

No embarrassment at all. I have done exactly the same thing (which is, of course, exactly why I thought I had a notion about it).

 

The suggestion of the ability of submit a single array parameter for the “in” operator and have Groovy sort out the number of “?”, however, seems an excellent one to me. Is this a worthwhile improvement, anyone? I wouldn’t think it’d be too difficult.

 

David Sills

 

---

From: Adrian Powell [mailto:adrian.a.powell@...]
Sent: Sunday, September 07, 2008 1:23 AM
To: user@...
Subject: Re: [groovy-user] Groovy SQL problem with "in" parameters

 

Glah.  I had been seduced by the allure of the ARRAY type and totally overlooked this simple and easy solution.  Wow, what an embarrassing oversight.  Thanks David.

-Adrian.

On Sat, Sep 6, 2008 at 9:18 PM, David Sills <DSills@...> wrote:

Mind, I haven't actually got the vaguest, but it sounds like the process is using (internally) a PreparedStatement, in which case without some magic you cannot use this one-argument syntax - you must have a series of "... in (?,?,?,?,?...)" that matches your arguments, then assign them to the PS one at a time. This would be useful magic to implement in a Groovy object, and wouldn't be terribly hard, I think, but might not have been implemented yet. If I'm right, the result in the case of "... in (?)" might look in SQL like "... in ('[String1, String2, ...]')" (in other words, the string version of the array itself as a single parameter), which is most definitely not what you want and will almost undoubtedly give you zero rows (unless you have the oddest data...).

Try "... in (?, ?, ?, ?...)" (to the length of your array: undoubtedly there's some clever Groovy syntax for that, but I don't happen to know it off the top of my head) with your array (or single Strings?), and see if that works - it's at least worth a couple of minutes.

David Sills

-----Original Message-----
From: adrianp [mailto:adrian.a.powell@...]
Sent: Saturday, September 06, 2008 10:17 PM
To: user@...
Subject: [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'¬í\0 ur\0 [Ljava.lang.String;­ÒVçé
{G \0\0xp\0\0\0 t\0 oranget\0 carrot'' 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



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

   http://xircles.codehaus.org/manage_email

 

[adrianp]

I know that GORM supports arrays as single parameters so that may be one place to start.  Here's a query from another part of my application which works and which started me down this rabbit hole in the first place:

        def trades = Trade.findAll("from Trade as tr where tr.id in (:allIds)",
                [allIds:ids], params)

where 'ids' is an ArrayList.

On Sun, Sep 7, 2008 at 5:10 AM, David Sills <DSills@...> wrote:

Adrian:

 

No embarrassment at all. I have done exactly the same thing (which is, of course, exactly why I thought I had a notion about it).

 

The suggestion of the ability of submit a single array parameter for the "in" operator and have Groovy sort out the number of "?", however, seems an excellent one to me. Is this a worthwhile improvement, anyone? I wouldn't think it'd be too difficult.

 

David Sills

 

---

From: Adrian Powell [mailto:adrian.a.powell@...]
Sent: Sunday, September 07, 2008 1:23 AM

To: user@...

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

 

Glah.  I had been seduced by the allure of the ARRAY type and totally overlooked this simple and easy solution.  Wow, what an embarrassing oversight.  Thanks David.

-Adrian.

On Sat, Sep 6, 2008 at 9:18 PM, David Sills <DSills@...> wrote:

Mind, I haven't actually got the vaguest, but it sounds like the process is using (internally) a PreparedStatement, in which case without some magic you cannot use this one-argument syntax - you must have a series of "... in (?,?,?,?,?...)" that matches your arguments, then assign them to the PS one at a time. This would be useful magic to implement in a Groovy object, and wouldn't be terribly hard, I think, but might not have been implemented yet. If I'm right, the result in the case of "... in (?)" might look in SQL like "... in ('[String1, String2, ...]')" (in other words, the string version of the array itself as a single parameter), which is most definitely not what you want and will almost undoubtedly give you zero rows (unless you have the oddest data...).

Try "... in (?, ?, ?, ?...)" (to the length of your array: undoubtedly there's some clever Groovy syntax for that, but I don't happen to know it off the top of my head) with your array (or single Strings?), and see if that works - it's at least worth a couple of minutes.

David Sills

-----Original Message-----
From: adrianp [mailto:adrian.a.powell@...]
Sent: Saturday, September 06, 2008 10:17 PM
To: user@...
Subject: [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'¬í\0 ur\0 [Ljava.lang.String;­ÒVçé
{G \0\0xp\0\0\0 t\0 oranget\0 carrot'' 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



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

   http://xircles.codehaus.org/manage_email

 

[simonz]

Hi adrianp,

I don't have much useful to say except:  make sure you are using up-to-date drivers for MySQL.

I recently ran into a similar issue and even ended up reporting a bug to the Groovy SQL folks, only to find out in the end that it was the old version of the MySQL JDBC drivers were buggy.

(See http://www.nabble.com/Strange-issue-with-Groovy-SQL-parameters-tp18587220p18587220.html for reference).

Cheers,

Simon.