Re: Extending groovy SQL to gain access to cur...

Author Login • Post Reply

Realizing there may be an incorrect assumption:

Situations where returning the cursor from the Sql method is part of an optimal solution are not that common. If the apparent problem is the memory needed to process the result, the groovy.sql.Sql#eachRow method gives the closure (as the only parameter - 'it') access to the ResultSet (with GroovyResultSet extension). The closure can, of course, write to a file or directly to the HTTP response. Since I'm more familiar with Rails than Grails, I can't say off-hand whether writing to the HTTP response or writing to a file will be better - Rails will hold the response in memory; that's why I think of writing to a file.

On Mon, Feb 8, 2010 at 14:33, John Bito <jwbito@gmail.com> wrote:

The patch I submitted is in Groovy 1.6.5 and 1.7.0 (I'm using 1.7.0, so I can't say how exactly things work with 1.6.5). A Couple of things you will have to deal with in a Grails app are threading and session life-cycle. One approach is to use a cursor to write a local file and allow the web server to stream the file for downloading.

If you want access to the cursor, you may subclass groovy.sql.Sql. The protected method groovy.sql.Sql#executePreparedQuery will give you access to the implementation of java.sql.ResultSet from JDBC. In order to make that groovy, you wrap it in a proxy:

new GroovyResultSetProxy(theResultSet).getImpl();

If there's work to do, I can be available on a consulting basis :)

John

On Mon, Feb 8, 2010 at 14:12, Kline, John <jkline@aclara.com> wrote:

John,

I saw this message thread archived on codehaus (http://archive.codehaus.org/lists/org.codehaus.groovy.user/msg/3ae83b000908031408l465f01abue93d1f4783e4fa01@mail.gmail.com ) where you discussed adding cursor support to Groovy Sql.

I was looking for something similar for my grails application where I may need to stream tens of thousands of rows as users “download” large record sets. A cursor seems much more efficient and eliminates the risk of OOM errors.

I was wondering how much luck you had with this? The Groovy Sql docs are pretty thin…

Thanks,

John Kline

[groovy-user] Re: Extending groovy SQL to gain access to cursors

John Bito

2010-02-09