using dataset queries not supported in groovyConsole

Did you add the source directory to the classpath of your groovyConsole instance (under the Script menu)? Incidentally, you should get an improved error message once we release 1.8.7 or 2.0.

There is no source directory. It's just that snippet of code (I have only omitted the database connection line). Thank you!

add code tags

I'd normally expect to see something like the following in your script then:

class cc {
    Integer prg_ordine
    // other fields ...
}

So that the DataSet logic can map your query results to a POGO.

Also adding a POGO class results in the same failure as before (I have added the source to the groovyConsole cp). Always the problem is in calling the `each` closure on the dataset object returned from the `findAll`. Running the same code in a script works fine.

Yes, DataSets are lazily evaluated. Calling 'findAll' stores away the query conditions. Not until you try to use the result (e.g. using 'each' in your example) does the database actually get hit.

Can you try the following self-contained example:

@Grab('hsqldb:hsqldb:1.8.0.10')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
import org.codehaus.groovy.runtime.DefaultGroovyMethods as DGM

def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver']
def ignoreErrors = { Closure c ->
    try {
        c()
    } catch (java.sql.SQLException se) {
        System.err.println "Ignoring: " + se.message
    }
}
Sql.withInstance(db.url, db.user, db.password, db.driver) { sql ->
    ignoreErrors {
        sql.execute "drop table PROJECT"
    }
    sql.execute '''
    create table PROJECT (
        id integer not null,
        name varchar(50),
        url varchar(100)
    )
    '''

    def params = [10, 'Groovy', 'http://groovy.codehaus.org']
    sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params

    def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
    sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"

    def newUrl = 'http://grails.org'
    def project = 'Grails'
    sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"

    def projects = sql.dataSet('Project')
    projects.add(id: 30, name: 'Griffon', url: 'http://griffon.codehaus.org')
    projects.add(id: 40, name: 'Gradle', url: 'http://gradle.org')

    println 'Some GR8 projects:'
    sql.eachRow('select * from PROJECT') { row ->
        println "${row.name.padRight(10)} ($row.url) ${DGM.dump(row)}"
    }
}

class Project {
    Integer id
    String name
    String url
}

Just cut and paste the whole thing into a groovyConsole on a machine with Internet access.

Let us know how it goes. Alternatively, if you can provide some more of your example, we could try to debug it a little further.

I executed your code in groovyConsole, an exception is thrown:

Exception thrown May 08, 2012 9:14:10 AM org.codehaus.groovy.runtime.StackTraceUtils sanitize
WARNING: Sanitizing stacktrace: groovy.lang.MissingMethodException: No signature of method: static groovy.sql.Sql.withInstance() is applicable for argument types: (java.lang.String, java.lang.String, java.lang.String, java.lang.String, ConsoleScript0$_run_closure2) values: [jdbc:hsqldb:mem:testDB, sa, , org.hsqldb.jdbcDriver, ...]
... cut...

In order to run I slightly modified the code in:

def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
sql.execute "drop table PROJECT if exists"
sql.execute ''' create table PROJECT ( id integer not null, name varchar(50), url varchar(100) ) '''

and eventually that's what came out:

Some GR8 projects: Groovy (http://groovy.codehaus.org) <$Proxy10@150c73c h=groovy.sql.GroovyResultSetProxy@17e524b>
Grails (http://grails.org) <$Proxy10@150c73c h=groovy.sql.GroovyResultSetProxy@17e524b>
Griffon (http://griffon.codehaus.org) <$Proxy10@150c73c h=groovy.sql.GroovyResultSetProxy@17e524b>
Gradle (http://gradle.org) <$Proxy10@150c73c h=groovy.sql.GroovyResultSetProxy@17e524b>

Changing the code this way:

// println 'Some GR8 projects:'
// sql.eachRow('select * from PROJECT') { row ->
// println "$

($row.url) $

"
// }

println("Printing `each` on projects dataset")
projects.each

and running in the console here is the output:

Printing `each` on projects dataset
[ID:10, NAME:Groovy, URL:http://groovy.codehaus.org]
[ID:20, NAME:Grails, URL:http://grails.org]
[ID:30, NAME:Griffon, URL:http://griffon.codehaus.org]
[ID:40, NAME:Gradle, URL:http://gradle.org]
Printing `each` on results dataset returned from findAll
Exception thrown May 08, 2012 9:52:35 AM org.codehaus.groovy.runtime.StackTraceUtils sanitize WARNING: Sanitizing stacktrace: groovy.lang.GroovyRuntimeException: Could not find the ClassNode for MetaClass: org.codehaus.groovy.runtime.metaclass.ClosureMetaClass@ba2d07[class ConsoleScript8$_run_closure3] at groovy.sql.DataSet.visit(DataSet.java:278) at groovy.sql.DataSet.getSqlWhereVisitor(DataSet.java:261) at groovy.sql.DataSet.getSqlWhere(DataSet.java:208) at groovy.sql.DataSet.getSql(DataSet.java:235) at groovy.sql.DataSet.each(DataSet.java:198) at groovy.sql.DataSet$each.call(Unknown Source) at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at ConsoleScript8.run(ConsoleScript8:50) at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:266) at groovy.lang.GroovyShell.run(GroovyShell.java:517) at groovy.lang.GroovyShell.run(GroovyShell.java:172) at groovy.lang.GroovyShell$run.call(Unknown Source) at groovy.ui.Console$_runScriptImpl_closure16.doCall(Console.groovy:924) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:272) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:884) at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:141) at groovy.ui.Console$_runScriptImpl_closure16.doCall(Console.groovy) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:272) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:884) at groovy.lang.Closure.call(Closure.java:410) at groovy.lang.Closure.call(Closure.java:404) at groovy.lang.Closure.run(Closure.java:488) at java.lang.Thread.run(Thread.java:722) groovy.lang.GroovyRuntimeException: Could not find the ClassNode for MetaClass: org.codehaus.groovy.runtime.metaclass.ClosureMetaClass@ba2d07[class ConsoleScript8$_run_closure3] at ConsoleScript8.run(ConsoleScript8:50)

Sorry, "withInstance" is only available in the very latest versions of Groovy, not 1.8.4. But getting back to your main point, I think this is a limitation of the groovyConsole. I'll alter the issue description to include "groovyConsole" in it.