Strange issue with Groovy SQL parameters

[simonz]

Hi,

I've been trying to write some simple code that queries a database and I found the following works fine:

  def channelNum = 2
  def channel = sql.firstRow("select * from channel where channum = "+channelNum)

However this returns strange invalid results:

  def channelNum = 2
  def channel = sql.firstRow("select * from channel where channum = ${channelNum}")

As does this:

  def channelNum = 2
  def channel = sql.firstRow("select * from channel where channum = ?",[channelNum])

When I say "invalid" results, I mean that a row is returned, but instead of correct values in the row, invalid values are in there, such as "4294968298" instead of "20", etc.   The type of the column concerned is an unsigned int(10), and the database is MySQL.

Can anyone tell me why I get different results for the different forms above?

Thanks!

Simon.

[Eric Anderson-21]

I am wondering if it has to do with GString versus String.  Example:

def channelNum = 2
def queryString = "select * from channel where channum ="+channelNum
println queryString
println queryString.class

def queryString2 = "select * from channel where channum =${channelNum}"
println queryString2
println queryString2.class

outputs:
-----------
select * from channel where channum =2
class java.lang.String
select * from channel where channum =2
class org.codehaus.groovy.runtime.GStringImpl


What happens if you put a toString() onto your failing queries such
that the argument to firstRow() is a String?

Eric

On Tue, Jul 22, 2008 at 6:42 AM, simonz <simonzmail@...> wrote:

>
> Hi,
>
> I've been trying to write some simple code that queries a database and I
> found the following works fine:
>
>  def channelNum = 2
>  def channel = sql.firstRow("select * from channel where channum =
> "+channelNum)
>
> However this returns strange invalid results:
>
>  def channelNum = 2
>  def channel = sql.firstRow("select * from channel where channum =
> ${channelNum}")
>
> As does this:
>
>  def channelNum = 2
>  def channel = sql.firstRow("select * from channel where channum =
> ?",[channelNum])
>
> When I say "invalid" results, I mean that a row is returned, but instead of
> correct values in the row, invalid values are in there, such as "4294968298"
> instead of "20", etc.   The type of the column concerned is an unsigned
> int(10), and the database is MySQL.
>
> Can anyone tell me why I get different results for the different forms
> above?
>
> Thanks!
>
> Simon.
> --
> View this message in context: http://www.nabble.com/Strange-issue-with-Groovy-SQL-parameters-tp18587220p18587220.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 Eric,

Thanks for the followup.  

Eric Anderson-21 wrote:

What happens if you put a toString() onto your failing queries such
that the argument to firstRow() is a String?

I tried adding toString() to each case - it helps when the variable is embedded in the GString (which makes sense because it logically must convert to the same as the working case), but it doesn't in the case where a parameter is used (which is the case I really care about).

In case anyone wants to try it, here's a complete stand alone reproduction code which should work for anyone if you fiddle with the database connection settings and ensure a mysql driver jar is available on the classpath:

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

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test_db","root","","com.mysql.jdbc.Driver")
sql.execute("create table test_groovy ( id int(10) unsigned  )");
sql.executeInsert("insert into test_groovy values (2)")

def selectId = 2;
def row = sql.firstRow("select * from test_groovy where id = ${selectId}")
println "First row has value ${row.id}"

row = sql.firstRow("select * from test_groovy where id = ${selectId}")
println "First row has value ${row.id}"

row = sql.firstRow("select * from test_groovy where id = ?",[ selectId ])
println "First row has value ${row.id}"

row = sql.firstRow("select * from test_groovy where id = " + selectId)
println "First row has value ${row.id}"

sql.execute("drop table test_groovy")

------

When run this produces the following output:

First row has value 4294967298
First row has value 4294967298
First row has value 4294967298
First row has value 2

------

This really seems like a bug to me since there really should be no difference between them all.   I might log this a bug in JIRA if there aren't any more responses or any other explanations for why it does this.

Thanks!

Simon.