[groovy-user] Groovy 1.5.2, SQL statements and GStrings. Possibly another manifestation of GROOVY-2558?

[Erick Erickson]

First, I'm an utter novice at this, so this may be blazingly obvious.

When I run the code below, the first attempt to call dbMy.rows throws an exception while
the second succeeds. The only difference is that the variable that is used as a parameter
is just def'd in the first instance, and is declared as a String in the second.

As I read the documentation for SQL.rows, it should work with either a GString or a string.

Interestingly, the defs type is a GStringImpl, but I assume that "shouldn't make a difference".

This may be another flavor of http://jira.codehaus.org/browse/GROOVY-2558
and as such it may have already been fixed....

I'd be happy to test it with a trunk Groovy build if that would help.

Thanks
Erick

******************code*****************
import groovy.sql.Sql
def dbMy = Sql.newInstance(
        'jdbc:mysql://localhost/links',
        'linksadmin',
        'pw4linksadmin',
        'com.mysql.jdbc.Driver')

def tbl = "link_subjects"
def sd = "select count(*) from $tbl"

println "${sd.class.getName()}" // Prints GStringImpl

try {
   def rows = dbMy.rows(sd)
   println rows[0]                       //Never get here, throws exception
} catch (Exception e) {
    println "We get an exception here"
}

String ss = "select count(*) from $tbl"
try {
    def rows = dbMy.rows(ss)
    println rows[0]
} catch (Exception e) {
    println "No exception here"
}



*************output****************
org.codehaus.groovy.runtime.GStringImpl
We get an exception here
["count(*)":84572]

******************stack trace***************
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 ''link_subjects'' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
    at groovy.sql.Sql.rows(Sql.java:725)
    at groovy.sql.Sql.rows(Sql.java:666)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
    at org.codehaus.groovy.runtime.InvokerHelper.invokePojoMethod(InvokerHelper.java:765)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:753)
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:167)
    at selprob.run(selprob.groovy:19)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
    at org.codehaus.groovy.runtime.InvokerHelper.invokePogoMethod(InvokerHelper.java:777)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:757)
    at org.codehaus.groovy.runtime.InvokerHelper.runScript(InvokerHelper.java:402)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
    at groovy.lang.MetaClassImpl.invokeStaticMethod(MetaClassImpl.java:1094)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:748)
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:167)
    at selprob.main(selprob.groovy)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
    at groovy.lang.MetaClassImpl.invokeStaticMethod(MetaClassImpl.java:1094)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:748)
    at groovy.lang.GroovyShell.runMainOrTestOrRunnable(GroovyShell.java:244)
    at groovy.lang.GroovyShell.run(GroovyShell.java:218)
    at groovy.lang.GroovyShell.run(GroovyShell.java:147)
    at groovy.ui.GroovyMain.processOnce(GroovyMain.java:493)
    at groovy.ui.GroovyMain.run(GroovyMain.java:308)
    at groovy.ui.GroovyMain.process(GroovyMain.java:294)
    at groovy.ui.GroovyMain.processArgs(GroovyMain.java:111)
    at groovy.ui.GroovyMain.main(GroovyMain.java:92)
    at groovy.lang.GroovyShell.main(GroovyShell.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90

[Bloois, Rene de]

Erick,

 

I suspect that the problem is that Sql.rows(GString) converts the GString to a string with ?'s in it.

 

So the first case translates your SQL to "select count(*) from ?", which I suspect MySQL does not know how to handle correctly.

 

When you use a GString you should only use ${} for actual parameters to the query, like "select * from t1 where c1 = ${}".

 

What's your use case for this? maybe we can come up with better alternatives.

 

Greetz,

René

---

From: Erick Erickson [mailto:ericke...@gmail.com]
Sent: donderdag 21 februari 2008 04:30 PM
To: us...@groovy.codehaus.org
Subject: [groovy-user] Groovy 1.5.2, SQL statements and GStrings. Possibly another manifestation of GROOVY-2558?

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

[Erick Erickson]

Thanks for the reply. The use case is, perhaps, not very good as I'm using some
one-off programs to get more familiar with groovy.

But it occurred to me that I could write a very cheap domain class to
suck the data out of one database and put it on a local db. I need to
do this since we don't have a dedicated test DB, just a production
one and I need to get in there and *really* mess around with a few tables.
So it would be nice to have a database I could mess up.....

Yes, there are other ways to do this, but humor me <G>. I didn't want
to use Hibernate for instance...

So I wrote a method that takes in a class where I've carefully defined the fields
to be *exactly* the same spelling and case as a table in the prod DB. I can then
construct a SQL query of the form
'select (field1, field2, field3) from table' to get the source and then
'insert (field1, field2, field3) values(1, 2, 3) into table' to put them in
my test db via reflection.

once that's done, I can transfer selected tables like this:

move(new classt1, "tablename1")
move (new class2, "tablename2")
etc.

It's no problem to just use a String rather than a GString, or
use a StringBuilder and pass the string from that. I'm mostly trying to
refine my understanding of when how typing behaves. So I guess
I probably need to go back over GInA's explanation of GStrings to get a better
sense of what's going on. I'm still thinking in terms of fixed types I suspect.

When I see in the documentation the declaration db.rows(GString) I'm thinking
in terms of the parameter being of a GString type, and I suspect I really
need to crank my thinking around and think of a GString as an on-the-fly
definition of a quoted string with substitutions rather than a type I can hold
in my hand.

My program works, but I spent significant time figuring out why it was failing
(an inevitable consequence of learning something new) and I'd like to be able
to apply the lesson more broadly.....

Thanks
Erick

[Bloois, Rene de]

I can give you more explanation to get you up to speed:

 

What the groovy compiler does is translate the GString

 

"test${test}test"

 

to a new GString instance.

 

You can look at the source of GString and GStringImpl for the internals.

 

the GString contains 2 arrays, one containing the string fragments, the other containing the expressions.

 

So what Sql.rows() does is replace all the expressions with ?, then getting the string representation, giving it to a JDBC statement and then use the original expressions to set the parameters on the statement.

 

Hope this helps.

---

From: Erick Erickson [mailto:ericke...@gmail.com]

Sent: vrijdag 22 februari 2008 04:19 PM
To: us...@groovy.codehaus.org
Subject: Re: [groovy-user] Groovy 1.5.2, SQL statements and GStrings. Possibly another manifestation of GROOVY-2558?

[Erick Erickson]

Thanks for the explanation. I just got an unrelated hot issue dropped on my desk, so I'll take a look "sometime real soon now" <G>.

Best
Erick